Freigeben über

Returning spatial data in GeoJSON format - Part 2

  • Artikel

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 https://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.

Comments

  • Anonymous
    October 12, 2016
    Great find, doesn't work for me on Mulitpolygon data though.
  • Anonymous
    October 12, 2016
    The comment has been removed
  • Anonymous
    January 10, 2017
    I used the function and I didn't get right results....1) the polygon coordinates weren't follow the right hand rule2) it couldn't serialize correct polygons with holes.3) I believe that it serialized instead of lon, lat .... lat lon
  • Anonymous
    March 29, 2017
    How do we determine an intersection between two polygons using GeoJson and SqlServer 2016