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