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!