First published on MSDN on Jan 05, 2016
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.
