GeoJson, GIS, SQL Server

Use SQL Server to import GeoJSON files, and convert geo-data back into GeoJSON

This post is about how to use SQL Server to load GeoJSON spatial data in a SQL Server table, and how to export geo-data from a SQL Server table back into valid GeoJSON.

So here’s a problem…

You’re working with some geodata which is stored in files formatted as JSON – specifically GeoJSON. And you need to query this data, and make a few modifications too. But working with this textual data by hand is kind of tedious – it’d be much nicer to be able query and manipulate this data using software with dedicated JSON querying functions.

SQL Server’s JSON capabilities can help solve this problem

I’ve mentioned in previous couple of blogs that I’ve been working with geodata recently, specifically in the GeoJSON format. GeoJSON is just JSON, but it adheres to a particular standard to describe a geographical feature.

You can read more about GeoJSON at http://geojson.org/.

An example of a GeoJSON geographical feature is shown below:

{
  "type": "Feature",
  "properties": {
    "BuildingReference": "BR-123: City Hall",
    "Address": "Donegall Square",
    "City": "Belfast",
    "Postcode": "BT1 5GS",
    "CurrentStatus": "In Use"
  },
  "geometry": {
    "type": "Point",
    "coordinates": [
      -5.9301,
      54.5967
    ]
  }
}

The really interesting parts of this JSON object are the ‘properties‘ and ‘geometry‘, which tells us information about the feature, and where it is. The example above shows a geographical point with latitude and longitude, but it could also be a shape or a line.

Below is an example of a GeoJSON FeatureCollection, which contains a couple of different features.

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "BuildingReference": "BR-123: City Hall",
        "Address": "Donegall Square",
        "City": "Belfast",
        "Postcode": "BT1 5GS",
        "CurrentStatus": "In Use"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          -5.9301,
          54.5967
        ]
      }
    },
    {
      "type": "Feature",
      "properties": {
        "BuildingReference": "BR-456: Queen's University",
        "Address": "University Road",
        "City": "Belfast",
        "Postcode": "BT7 1NN",
        "CurrentStatus": "In Use"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          -5.934,
          54.5844
        ]
      }
    }
  ]
}

If you’d like to see your GeoJSON feature collection represented on a map, check out http://geojson.io/.

So I’m working with files that have tens of thousands of features, and I need to run a few reports. For example, I need to know how many features per city are represented in the data. This would be a really easy query in T-SQL – but how can I get this data into SQL Server?

Fortunately SQL Server handles JSON really well. I can use the OPENROWSET keyword to bulk load my GeoJSON files into a variable, and it also provides the useful OPENJSON keyword, which allows me to parse a JSON string into its different components.

So if my GeoJSON is stored in a file named buildings.geojson, I can access the file using the code below, and represent it in SQL Server’s tabular format.

DECLARE @JSON nvarchar(max)
 
-- load the geojson into the variable
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\jeremy.lindsay\buildings.geojson', SINGLE_CLOB) as JSON
 
-- use OPENJSON to split the different JSON nodes into separate columns
SELECT
	*
FROM
OPENJSON(@JSON, '$.features')
	WITH (
		BuildingReference nvarchar(300) '$.properties.BuildingReference',
		Address nvarchar(300) '$.properties.Address',
		City nvarchar(300) '$.properties.City',
		Postcode nvarchar(300) '$.properties.Postcode',
		CurrentStatus nvarchar(300) '$.properties.CurrentStatus',
		Longitude nvarchar(300) '$.geometry.coordinates[0]',
		Latitude nvarchar(300) '$.geometry.coordinates[1]'
	)

Or if I wanted, I can just as easily load this data into a dedicated table, and represent the feature’s location as the SQL Server geography spatial type:

DROP TABLE IF EXISTS dbo.Buildings
 
CREATE TABLE dbo.Buildings
(
	Id int IDENTITY PRIMARY KEY,
	BuildingReference nvarchar(300),
	Address nvarchar(300),
	City nvarchar(300),
	Postcode nvarchar(300),
	CurrentStatus nvarchar(300),
	Coordinates GEOGRAPHY,
	Longitude nvarchar(100),
	Latitude nvarchar(100)
)
 
 
DECLARE @JSON nvarchar(max)
 
-- load the geojson into the variable
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\jeremy.lindsay\buildings.geojson', SINGLE_CLOB) as JSON
 
Insert Into dbo.Buildings (BuildingReference, Address, City, Postcode, CurrentStatus, Longitude, Latitude, Coordinates)
SELECT
	BuildingReference, 
	Address, 
	City,
	Postcode,
	CurrentStatus, 
	Longitude, 
	Latitude,
	geography::Point(Latitude, Longitude, 4326) AS Geography
FROM
OPENJSON(@JSON, '$.features')
	WITH (
		BuildingReference nvarchar(300) '$.properties.BuildingReference',
		Address nvarchar(300) '$.properties.Address',
		City nvarchar(300) '$.properties.City',
		Postcode nvarchar(300) '$.properties.Postcode',
		CurrentStatus nvarchar(300) '$.properties.CurrentStatus',
		Longitude nvarchar(300) '$.geometry.coordinates[0]',
		Latitude nvarchar(300) '$.geometry.coordinates[1]'
	)

How about exporting from SQL Server back to GeoJSON?

So querying data in the table is really easy for me now – but how about the scenario where I have data in SQL Server, and I want to export the results of a SELECT query to GeoJSON format?

Fortunately we can use the JSON querying capabilities of SQL Server – I can suffix my query with ‘FOR JSON PATH’ to convert the results of a SELECT query from a tabular format to a JSON format, as shown below:

DECLARE @featureList nvarchar(max) =
(
	SELECT
		'Feature'                                           as 'type',
		BuildingReference                                   as 'properties.BuildingReference',
		Address                                             as 'properties.Address',
		City                                                as 'properties.City',
		Postcode                                            as 'properties.Postcode',
		CurrentStatus                                       as 'properties.CurrentStatus',
		Coordinates.STGeometryType()                        as 'geometry.type',
		JSON_QUERY('[' + Longitude + ', ' + Latitude + ']') as 'geometry.coordinates'
	FROM Buildings
		FOR JSON PATH
)

But this doesn’t get me a result that’s quite right – it’s just a JSON formatted list of GeoJSON features. To make this a properly formatted GeoJSON featurecollection, I need to give this list a name – ‘features’, and specify the type as a ‘FeatureCollection’. Again this is reasonably straightforward with the built in JSON querying features of SQL Server.

DECLARE @featureList nvarchar(max) =
(
	SELECT
		'Feature'                                           as 'type',
		BuildingReference                                   as 'properties.BuildingReference',
		Address                                             as 'properties.Address',
		City                                                as 'properties.City',
		Postcode                                            as 'properties.Postcode',
		CurrentStatus                                       as 'properties.CurrentStatus',
		Coordinates.STGeometryType()                        as 'geometry.type',
		JSON_QUERY('[' + Longitude + ', ' + Latitude + ']') as 'geometry.coordinates'
	FROM Buildings
		FOR JSON PATH
)
 
DECLARE @featureCollection nvarchar(max) = (
	SELECT 'FeatureCollection' as 'type',
	JSON_QUERY(@featureList)   as 'Features'
	FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
 
SELECT @featureCollection

If you want to validate your GeoJSON, you can use a site like GeoJSONLint.com.

Wrapping up

SQL Server has great JSON querying capabilities, and I’ve found this really useful when I’ve combined this also with its support for geospatial querying. Hopefully this post is helpful to anyone working with spatial data in GeoJSON format.


About me: I regularly post about Microsoft technologies and .NET – if you’re interested, please follow me on Twitter, or have a look at my previous posts here. Thanks!

One thought on “Use SQL Server to import GeoJSON files, and convert geo-data back into GeoJSON

Comments are closed.