Returning spatial data in GeoJSON format - Part 2

[JovanPop]

First published on MSDN on Jan 13, 2016

In the previous post Returning spatial data in GeoJSON format - Part 1 , I have described how you can export Sql Server spatial data to GeoJSON . GeoJSON is popular JSON format used to represent spatial data that looks like:

{
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [125.6, 10.1]
},
"properties": {
"name": "Dinagat Islands"
}
}

However, in part 1, I have exported simple points. In this post I will show you how to export other spatial types such as lines or polygons, into equivalent GeoJSON formats.

Spatial data in SQL server can be transformed to well-known text (WKT) format that looks like MULTIPOINT ((100 100), (200 200)) . To transform spatial data to GeoJSON such as { "type": "MultiPoint", "coordinates": [ [100, 100], [200, 200] ] } , I will transform WKT to JSON.

Data preparation

First, I need to prepare spatial data that will be exported. I will use the following script:

DROP TABLE IF EXISTS dbo.SpatialTable;
GO

CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POINT (100 100)', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('MULTIPOINT ((100 100), (200 200))', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('MULTIPOLYGON (((5 5, 10 5, 10 10, 5 5)), ((10 10, 100 10, 200 200, 30 30, 10 10)))', 0));

In this example, I have populated one table with five different spatial objects that will be exported.

Exporting Spatial data as GeoJSON

Now, I will use SELECT query with FOR JSON PATH to select spatial data from table and format it as JSON (or GeoJSON in this case):

SELECT
'Feature' as [type],
JSON_QUERY( dbo.geometry2json( GeomCol1 ) ) as [geometry]

id as [properties.id],
GeomCol1.STGeometryType() as 'properties.sqlgeotype',
GeomCol1.ToString() as 'properties.wkt'
FROM SpatialTable
FOR JSON PATH

First I have selected literal 'Feature' as property [type] - this is required value in GeoJSON format. Then, I'm using user defined function to convert spatial type to JSON (cod eof the function is below). Note that I need to pass generated JSON to JSON_QUERY function. Without this call, FOR JSON will output GeoJSON objects as strings instead of sub-objects.

Finally, I'm exporting some additional data such as id of the row, sql geo type, and WKT value in the properties object. These are custom additional information that I might generate if needed.

In order to produce properly formatted GeoJSON, you will just need to wrap results of this query with the following wrapper:

{ "type": "FeatureCollection",
"features":
<< query results >>
}

Utility function that is used formatting spatial cells as GeoJSON is shiwn below.

DROP FUNCTION IF EXISTS dbo.geometry2json
GO
CREATE FUNCTION dbo.geometry2json( @geo geometry)
RETURNS nvarchar(MAX) AS
BEGIN
RETURN (
'{' +
(CASE @geo.STGeometryType()
WHEN 'POINT' THEN
'"type": "Point","coordinates":' +
REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POINT ',''),'(','['),')',']'),' ',',')
WHEN 'POLYGON' THEN
'"type": "Polygon","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'POLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
WHEN 'MULTIPOLYGON' THEN
'"type": "MultiPolygon","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'MULTIPOLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
WHEN 'MULTIPOINT' THEN
'"type": "MultiPoint","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'MULTIPOINT ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
WHEN 'LINESTRING' THEN
'"type": "LineString","coordinates":' +
'[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@geo.ToString(),'LINESTRING ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']'
ELSE NULL
END)
+'}')
END

In this case I'm using type and well-known text representation of spatial data and converting WKT to GeoJSON. I need few replace calls to replace parentheses and spaces in WKT to brackets and commas in JSON. Depending on type of spatial data, I would need three or four REPLACE calls. Code is modified from this http://stackoverflow.com/questions/6506720/reformat-sqlgeography-polygons-to-json stack overflow question.

Conclusion

FOR JSON clause enables you to export any data as JSON text, so it can be used to format spatial data as GeoJSON. With a couple of replaces you can transform WKT to GeoJSON and export any type of spatial data that is stored in SQL Server.