Friday, 17 August 2012

MS SQL Server Spatial to Google Maps KML

I don't usually post code tips on my blog but this one might help some other poor soul.

You have some map data stored in SQL Server as geometry (or geography) and you want to extract it in a format that you can use in your google maps mashups.

I wanted a Q and D solution that would be confined entirely to SQL Server functions. I can certainly write C type code (maybe a million lines in my life) but i wanted this totally within SQL Server and easily used.

Note that my maps are relatively simple - there are no interior rings though there are all sorts of combinations of MULTIPOLYGONs, GEOMETRYCOLLECTIONs and simple POLYGONs. You should check your own maps for complexity and if necessary add support for interior rings (which i don't need).

Here's the 3 main pieces of code i wrote to satisfy my needs.


This first function returns the MultiGeometry part of my Placemark (sorry, everything above you will have to sort out yourself):


CREATE FUNCTION [dbo].[kmlGeometryElementsExtract]
(
@p_Spatial geometry
)
RETURNS xml
AS
BEGIN

DECLARE @v_spatialXML xml

SET @v_spatialXML = dbo.kmlGeometryElementsExtractAux(@p_Spatial.ToString())

DECLARE @v_collection xml
DECLARE @v_multi xml
DECLARE @v_poly xml

SELECT
@v_multi = nref.query('.')
from @v_spatialXML.nodes('/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'MULTI'

IF @v_multi IS NOT NULL
BEGIN

RETURN (

SELECT
(
SELECT
dbo.kmlGeometryCoordsConvert(nref.value('.','VARCHAR(MAX)')) as 'outerBoundaryIs/LinearRing/coordinates'
from @v_multi.nodes('//bracket') as R(nref)
where
nref.value('./bracket[1]','VARCHAR(MAX)') IS NULL -- no backet below
FOR XML PATH('Polygon'), TYPE
)
FOR XML PATH('MultiGeometry'), TYPE
)

END

SELECT
@v_collection = nref.query('.')
from @v_spatialXML.nodes('/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'COLLECTION'

IF @v_collection IS NOT NULL
BEGIN
RETURN (

SELECT
(
SELECT
dbo.kmlGeometryCoordsConvert(nref.value('.','VARCHAR(MAX)')) as 'outerBoundaryIs/LinearRing/coordinates'
from @v_collection.nodes('*/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'POLYGON'
FOR XML PATH('Polygon'), TYPE
)
FOR XML PATH('MultiGeometry'), TYPE

)
END

SELECT
@v_poly = nref.query('.')
from @v_spatialXML.nodes('/bracket') as R(nref)
where
nref.value('.[1]/@tag','varchar(100)') = 'POLYGON'

IF @v_poly IS NOT NULL
BEGIN
RETURN (
SELECT
(
SELECT
dbo.kmlGeometryCoordsConvert(nref.value('.','VARCHAR(MAX)')) as 'outerBoundaryIs/LinearRing/coordinates'
from @v_poly.nodes('*/bracket') as R(nref)
where
nref.value('.','VARCHAR(MAX)') IS NOT NULL
FOR XML PATH('Polygon'), TYPE
)
FOR XML PATH('MultiGeometry'), TYPE
)
END

RETURN NULL
        
END



The kmlGeometryElementsExtract function above calls this one - which looks like a hack but it turns out to be the fastest way to skin this cat - i had written a FSM to do the job but SQL Server string handling is way too slow if you are concatenating one char at a time to a large string.

 CREATE FUNCTION [dbo].[kmlGeometryElementsExtractAux]
(
@p_SpatialString VARCHAR(MAX)
)
RETURNS varchar(max) -- in xml format
AS
BEGIN

DECLARE @v_result VARCHAR(MAX)

SET @v_result = REPLACE(@p_SpatialString,'GEOMETRYCOLLECTION (','<bracket tag="COLLECTION">')
SET @v_result = REPLACE(@v_result,'MULTIPOLYGON (','<bracket tag="MULTI">')

SET @v_result = REPLACE(@v_result,', POLYGON','POLYGON')
SET @v_result = REPLACE(@v_result,'POLYGON (','<bracket tag="POLYGON">')
SET @v_result = REPLACE(@v_result,', LINESTRING','LINESTRING')
SET @v_result = REPLACE(@v_result,'LINESTRING (','<bracket tag="LINESTRING">')

SET @v_result = REPLACE(@v_result,'), (',')(')

SET @v_result = REPLACE(@v_result,'(','<bracket>')
SET @v_result = REPLACE(@v_result,')','</bracket>')

RETURN @v_result

        
END


The kmlGeometryElementsExtract function above also calls this one - which simply formats the coordinates element contents to suit my needs - YMMV:


CREATE FUNCTION [dbo].[kmlGeometryCoordsConvert]
(
@p_CoordsString VARCHAR(MAX)
)
RETURNS varchar(max)
AS
BEGIN

/* this line replaced with line from post comment below   
RETURN CHAR(10) + REPLACE(REPLACE(@p_CoordsString,', ',',0' + CHAR(10)),' ',',') + ',0' + CHAR(10)

here's the new line:
*/

 RETURN CHAR(10) + REPLACE(REPLACE(REPLACE(@p_CoordsString,', ',',0' + CHAR(10)),' ',',') + ',0' + CHAR(10), CHAR(10), CHAR(10) + ' ')

END




Hope you find that useful

pop


ps see DB rankings here: DB-ENGINES

3 comments:

  1. I take it this will extract all the geometries of a table. How would I specify a specific set of geometries say withing a certain intersection of another geometry?

    ReplyDelete
  2. Thank you for this very usefull post. It helped me a lot!
    I would recommend including a space behind your 0 altitude { like this: RETURN CHAR(10) + REPLACE(REPLACE(REPLACE(@p_CoordsString,', ',',0' + CHAR(10)),' ',',') + ',0' + CHAR(10), CHAR(10), CHAR(10) + ' ') } in order to be closer at kml "best practise". I did encounter problems becauseof that missing space...
    “To conform more closely with the OGC KML 2.2 standard, we've made the parsing of the tag more strict in version 6.1. Spaces must only be used to separate the (longitude,latitude,altitude) triplets which specify the coordinates of a single point. It is invalid to add spaces between individual coordinates. We understand that previous versions of the client were more lenient, so if your KML files contain spaces between coordinates in coordinate triplets, an easy workaround is to remove all whitespace located immediately before or after commas inside a tag.”

    ReplyDelete

It is better to read than write - try http://www.historyisaweapon.com/zinnapeopleshistory.html

thanks

pop