Loading GeoJSON data into SQL Server

GeoJSON is popular format for spatial data representation. If you receive text formatted as GeoJSON from other systems, you can load it into SQL Server and convert it into spatial types.

New OPENJSON function in SQL Server 2016 enables you to parse and load GeoJSON text into SQL Server spatial types.

In this example, I will load GeoJSON text that contains a set of bike share locations in Washington DC. GeoJSON sample is provided ESRI and it can be found in https://github.com/Esri/geojson-layer-js/blob/master/data/dc-bike-share.json

I will load this GeoJSON text using following query:

drop table if exists BikeShare
create table BikeShare(
id int identity primary key,
position Geography,
ObjectId int,
Address nvarchar(200),
Bikes int,
Docks int )
declare @bikeShares nvarchar(max) = 
'{"type":"FeatureCollection",
"features":[{"type":"Feature",
"id":"56679924",
"geometry":{"type":"Point",
"coordinates":[-77.0592213018017,38.90222845310455]},
"properties":{"OBJECTID":56679924,"ID":72,
"ADDRESS":"Georgetown Harbor / 30th St NW",
"TERMINAL_NUMBER":"31215",
"LATITUDE":38.902221,"LONGITUDE":-77.059219,
"INSTALLED":"YES","LOCKED":"NO",
"INSTALL_DATE":"2010-10-05T13:43:00.000Z",
"REMOVAL_DATE":null,
"TEMPORARY_INSTALL":"NO",
"NUMBER_OF_BIKES":15,
"NUMBER_OF_EMPTY_DOCKS":4,
"X":394863.27537199,"Y":137153.4794371,
"SE_ANNO_CAD_DATA":null}
},
......'
-- NOTE: This GeoJSON is truncated.
-- Copy full example from https://github.com/Esri/geojson-layer-js/blob/master/data/dc-bike-share.json

INSERT INTO BikeShare(position, ObjectId, Address, Bikes, Docks)
SELECT geography::STGeomFromText('POINT ('+long + ' ' + lat + ')', 4326),
ObjectId, Address, Bikes, Docks
from OPENJSON(@bikeShares, '$.features')
WITH (
long varchar(100) '$.geometry.coordinates[0]',
lat varchar(100) '$.geometry.coordinates[1]',
ObjectId int '$.properties.OBJECTID',
Address nvarchar(200) '$.properties.ADDRESS',
Bikes int '$.properties.NUMBER_OF_BIKES',
Docks int '$.properties.NUMBER_OF_EMPTY_DOCKS' )

 

I have created a table BikeShare that will contain spatial data and I have defined local text variable @bikeShares that contains GeoJSON taken from https://github.com/Esri/geojson-layer-js/blob/master/data/dc-bike-share.json.

Then I will open GeoJSON rowset from @bikeShares variable using OPENJSON function. OPENJSON will return the one row for each object in GeoJSON array with the schema defined in WITH clause:

  • long and lat that represent longitude and latitude values on $.geometry.coordinates[0] and $.geometry.coordinates[1] paths in GeoJSON objects
  • objectId, address, number of bikes, and number of empty docks in $.properties object in GeoJSON.

Values in long and lat are used to initialize spatial type using geography::STGeomFromText method, and other values are inserted in table columns.

When I execute this script, I can query spatial data loaded from GeoJSON text:

select position.STAsText(), ObjectId, Address, Bikes, Docks 
FROM BikeShare

If you try to execute the same script you will see that all objects from GeoJSON variable are not in the table.

OPENJSON function enables you to parse any JSON text. You can use this function to convert GeoJSON format into SQL Server spatial types.