Using GeoJSON Data

Jovan Popovic shows how to use data in GeoJSON format.

First, building data in GeoJSON format from a spatial type:

In geometry object are placed type of the spatial data and coordinates. In “property” object can be placed various custom properties such as address line, town, postcode and other information that describe object. SQL Server stores spatial information as geometry or geography types, and also stores additional properties in standard table columns.

Since GeoJSON is JSON, it can be formatted using new FOR JSON clause in SQL Server.

In this example, we are going to format content of Person.Address table that has spatial column SpatialLocation in GeoJSON format using FOR JSON clause.

Then, converting GeoJSON to Geography 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

Check them out.

Related Posts

JSON Parsing Performance

Kevin Feasel

2017-11-14

JSON

Jovan Popovic has a couple posts showing JSON parsing performance in SQL Server 2017.  First up, he makes the statement that JSON parsing can be 10x faster than XML: I’m running 10000 iterations and getting the average spent time. Average time to parse JSON and get the value using JSON_VALUE() function is around 3 microseconds while the […]

Read More

Multi-Structured Data In U-SQL

Kevin Feasel

2017-09-06

JSON, U-SQL

Melissa Coates shows us how to use U-SQL to normalize JSON files in which different rows may have differing structures: Handling the varying formats in U-SQL involves a few steps if it’s the first time you’ve done this: Upload custom JSON assemblies  [one time setup] Create a database   [one time setup] Register custom JSON assemblies […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031