How to create GeoJson in SQL Server 2016

    

    GeoJSON is a geospatial data interchange format based on JavaScript Object Notation (JSON). It defines several types of JSON objects and the manner in which they are combined to represent data about geographic features, their properties, and their spatial extents.

     GeoJson supports Point, LineString, Polygon, MultiPoint, MultiLineString and MultiPolygon geometry primitives. That means a geojson file can represent a point, country, state, county or a map of a building in JSON format. Also you can attach custom information about the object by using JSON nodes. Like what? For example you can attach custom name, address, some kind of information you like to display on a map when user hovers or clicks on this object or a color code if you are trying to display heat map.

      How are you going to create these files dynamically? SQL Server has been supporting spatial data since 2008 and It started to support JSON objects in the version 2016. That means we should be able to read spatial data and create custom json files in GeoJson format.

      First you need spatial data which is going to represent countries, states, counties. Usually spatial data comes in shape file format.The best place to get free spatial data is the Census Bureau website  SQL Server has no idea what shape file is, so we need a way to convert/export these files to SQL Server. I wrote about how to import shape files to SQL Server before. Here is the link.

      Now, I am going to create the following table and export the states shape file/s in it. Geometry is one of the spatial data types SQL Server has and it represents 2D objects.  I am going to use the following table.

CREATE TABLE US_States (
 StateId int identity(1,1) not null,
 StateName varchar(50) not null,
 StateCode varchar(5) not null,
 StateMap geometry,
 StateGeoJson nvarchar(max)
)

 After exporting spatial data in this file, run a basic SELECT *  statement for this table. Since the data has spatial data you should see a new tab named Spatial results in SSMS.

    

    We have the spatial data. Now we need to convert geometry data type to JSON object. Sql Server stores spatial data types in binary format so we need to use one of the spatial functions to convert spatial data to WKT. If you are not familiar, I bet you are saying what the heck is WKT?

    WKT (Well-Known Text) is a text markup language for representing vector geometry objects. All you need to do is to call ToString() function.

      Now, let's look at a simple geoJson file. As you can see in the following example. We have a parent node named "type" which contains an array named "features". Each feature has type, geometry and properties nodes. We should be able to create features array by using our states spatial data.

{ "type": "FeatureCollection",
    "features": [
      { "type": "Feature",
        "geometry": {
          "type": "Point",
          "coordinates": [102.0, 0.5]
          },
          "properties": {
            "prop0": "value0"
          }
        },
      { "type": "Feature",
        "geometry": {
          "type": "LineString",
          "coordinates": [
            [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]
            ]
          },
        "properties": {
          "prop0": "value0",
          "prop1": 0.0
          }
        },
      { "type": "Feature",
         "geometry": {
           "type": "Polygon",
           "coordinates": [
             [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],
               [100.0, 1.0], [100.0, 0.0] ]
             ]
         },
         "properties": {
           "prop0": "value0",
           "prop1": {"this": "that"}
           }
         }
       ]
     }

      Let's talk about creating JSON now. GeoJson supports Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon spatial objects. WKT format looks very similar to these objects.We have two options when it comes to creating and storing GeoJson in the database. We can create a function which will take geometry value and create json from it or we can create computed column which can create the JSON as soon as a new column is created in this table.

     I am going to use the first option in this post.  There are couple of things we need to do in this function, first we need a case statement so we can change the type node in geojson. I am going to use another useful spatial function named STGeometryType() It returns the type of spatial type.

    Second, GeoJson uses square brackets for coordinates, WKT uses parentheses.I need to replace all parantheses with square brackets.  I might need tons of REPLACE functions to handle this in SQL Server 2016. If you have SQL Server 2017, you can do this cleaner way by using TRANSLATE function rather than REPLACE function. If you are interested, check out my older post about TRANSLATE function. SQL Server 2016 does not have this function so I am going to use old friend REPLACE function here.

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

    I know it looks complex but really it is a simple function. All those REPLACE functions make it look complex and ugly. All it does is creating a string that looks like GeoJson feature array. Now we need to convert this string to JSON format by using FOR JSON PATH synthax.

SELECT
'Feature' as [type],
JSON_QUERY( dbo.geometry2json( Stategeom ) ) as [geometry],
 [StateName] ,[StateCode] ,
stated,
 Stategeom.STGeometryType() as 'properties.sqlgeotype',
 Stategeom.ToString() as 'properties.wkt'
  FROM [dbo].[US_States]
  where statename ='dc'
  FOR JSON PATH

      I passed the output of the function to JSON_QUERY function because coordinates needs to be array without double quotes. Let's see what's this going to return.

       It looks like it satisfies the GeoJson specifications for 'features' nodes. Now I need to wrap the features nodes with FeatureCollection node and that should be it.

declare @inner nvarchar(max) =
(
SELECT
'Feature' as [type],
JSON_QUERY( dbo.geometry2json( Stategeom ) ) as [geometry],
 [StateName] ,[StateCode] ,
stateid,
 Stategeom.STGeometryType() as 'properties.sqlgeotype',
 Stategeom.ToString() as 'properties.wkt'
  FROM [dbo].[US_States]
  where statename ='dc'
  FOR JSON PATH
 )

declare @outer nvarchar(max) = (
 SELECT 'FeatureCollection' as [type], 
 JSON_QUERY(@inner) as 'features'
 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
 select @outer

It returns the following Json object which satisfies the GeoJson file specifications.

      That's it! you have GEOJson file from a spatial object. Now web application can serve this to any map application and map will display the spatial object. You can add custom properties to GEOJson too. Depending on your data you might want to change the color of a state to red or green, you can control that by attaching your data to GeoJson properties nodes. I have created a column in our table named StateGeoJson, you can store it there so you don't need to re-create it later.