Create a Google StreetView layer

In this exercise we are going to use OS Open Roads dataset as the basis of our layer. This dataset is available from the Ordnance Survey directly or for ADS customers available through Astun. In this scenario we have the OS Open Roads roadnode dataset loaded in a local Postgresql database as 'osdata.ords_roadnode'.

After migrating the roadnode dataset to Postgresql you will probably want to delete nodes outside of the area of interest.

DELETE FROM osdata.ords_roadnode WHERE osdata.ords_roadnode.ogc_fid IN (SELECT a.ogc_fid FROM osdata.ords_roadnode a, (SELECT * from area_of_interest) b WHERE ST_Disjoint(a.wkb_geometry, b.wkb_geometry));

If your area of interest is defined by a Boundaryline object then alter the SQL accordingly. Avoid using a LIKE where clause if using Boundaryline as this may slow down the query considerably. We'd suggest make a new area of interest table from the boundaryline dataset instead.

Now we need to add a column and populate it with the necessary html to create a link to Google StreetView.

ALTER TABLE osdata.ords_roadnode ADD COLUMN html text;
UPDATE osdata.ords_roadnode SET html = '<a href="https://www.google.com/maps?cbll=' || round(ST_Y(ST_Transform(wkb_geometry, 4326))::numeric,5)::text || ',' || round(ST_X(ST_Transform(wkb_geometry, 4326))::numeric,5)::text || '&cbp=12,0,0,0,5&layer=c" target=\"_new\">Google StreetView</a>';

Now we just need to define our layer. In this case we are using a classic mapserver layer definition as we may wish to use it My House.

LAYER
	NAME google_streetview
	INCLUDE "isharedata.inc"
	DATA "wkb_geometry from (select *, html as html_raw from osdata.ords_roadnode) as foo using srid=27700 using unique ogc_fid"
	METADATA
		"qstring_validation_pattern" "."
	END
	STATUS OFF
	TYPE POINT
	UNITS METERS
	CLASS
		NAME ""
		STYLE
			SYMBOL "circle"
			COLOR 251 251 251
			SIZE 10
		END
		STYLE
			SYMBOL "circle"
			COLOR 192 192 192
			SIZE 9
		END
	END
END

Expose the 'html_raw' field as the visible field, turn off the display field name option and save.