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.
Updated Mar 23, 2019
Version 2.0JovanPop
Microsoft
Joined March 07, 2019
SQL Server Blog
Follow this blog board to get notified when there's new activity