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!

GeoJson, GIS, SQL Server

How to list feature property information for GeoJSON data using SQL Server 2017 and OPENJSON

I’ve been importing GeoJSON files into tables in SQL Server 2017 – I’ve just had the files themselves without any metadata, so I’ve had to have a look into the file contents to work out what the properties are of the features listed so I can design the table schema.

After a while it got painful looking at the GeoJSON text and trying to parse it visually, and I wondered if I could write a SQL command to list the feature properties for me.

The SQL below allows me to import a GeoJSON file and quickly peek into the first feature in the collection of features, and list all the name-value pairs in the ‘property’ node.

Declare @geoJson nvarchar(max)
 
SELECT @geoJson = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\jeremy.lindsay\Desktop\my_gis_data.json', SINGLE_CLOB) as importedGeoJson
 
SELECT *
FROM OPENJSON(@geoJson, '$.features[0].properties')

Now I can read the properties of the first feature much more easily.

I also get the values and the type SQL Server infers about the property data – that’s interesting but I don’t trust the type information too much, as that could be different in every feature in the collection. (Of course the properties could be different in every feature in the collection as well!)


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!

GeoJson, GIS, SQL Server

‘Invalid object name ‘OPENJSON’.’ – SQL Server doesn’t recognise OPENJSON when bulk importing files

Recently I’ve been working with the GIS extensions in SQL Server, and Ineeded to import a GeoJSON file into a SQL Server 2017 table.

There’s a very straightforward way to import a valid JSON file into SQL Server with the OPENROWSET command, as shown below.

Declare @geoJson nvarchar(max)
 
SELECT @geoJson = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\jeremy.lindsay\Desktop\my_gis_data.json', SINGLE_CLOB) as importedGeoJson
 
SELECT left(@geoJson, 100)

The above SQL reads the GeoJSON file from my desktop, loads it into a variable, and allows me to view the leftmost 100 characters just as a quick visual check that the code GeoJSON data loaded into the variable correctly.

So far so good – the next step was to load this GeoJSON into a table. For this, I planned to use the OPENJSON command – but I noticed that my SQL Server Management Studio instance highlighted this command as unrecognised, as shown below.openjsonerror

I didn’t think too much of this – it happens sometimes – but of course when I ran the command, I got an error.

 'Invalid object name 'OPENJSON'.'

This made no sense to me – I was sure I had used the syntax correctly, I’ve used this on other machines and, I’d recently upgraded to SQL Server 2017 on my development machine. After a bit of googling, I found some other people had this issue on earlier versions of SQL Server, and the compatability level needs to be 130 or higher. To identify my database compatibility level, I ran the command below:

SELECT compatibility_level  
FROM sys.databases WHERE name = 'SampleGis';

I was greatly surprised to see it was set to 120!

I decided to double check what version of SQL Server I was using with the command:

SELECT @@VERSION

And was again greatly surprised when the answer came back telling me I was running SQL Server 2014.

As it turned out, I had an old version of SQL Server 2014 still running on my development machine. After uninstalling this, and trying again, the version correctly came back as SQL Server 2017, and the compatibility_level of my database was 140 – this time, OPENJSON worked correctly and I was able to view and query the GeoJSON file.


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!