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!

Azure, GIS, Leaflet, Security, Web Development

Getting started with Azure Maps, using Leaflet to display roads and satellite images, and comparing different browser behaviours

In this post I’m going to describe how to use the new(ish) Azure Maps service from Microsoft with the Leaflet JavaScript library. Azure Maps provides its own API for Geoservices, but I have an existing application that uses Leaflet, and I wanted to try out using the Azure Maps tiling services.

Rather than just replicating the example that already exists on the excellent Azure Maps Code Samples site, I’ll go a bit further:

  • I’ll show how to display both the tiles with roads and those with aerial images
  • I’ll show how to switch between the layers using a UI component on the map
  • I’ll show how Leaflet can identify your present location
  • And I’ll talk about my experiences of location accuracy in Chrome, Firefox and Edge browsers on my desktop machine.

As usual, I’ve made my code open source and posted it to GitHub here.

First, use your Azure account to get your map API Key

I won’t go into lots of detail about this part – Microsoft have documented the process very well here. In summary:

  • If you don’t have an Azure account, there are instructions here on how to create one.
  • Create a Maps account within the Azure Portal and get your API Key (instructions here).

Once you have set up a resource group in Azure to manage your mapping services, you’ll be able to track usage and errors through the portal – I’ve pasted graphs of my usage and recorded errors below.

graphs

You’ll use this API Key to identify yourself to the Azure Maps tiling service. Azure Maps is not a free service – pricing information is here – although presently on the S0 tier there is an included free quantity of tiles and services.

API Key security is one key area of Azure Maps that I would like to be enhanced – the API Key has to be rendered on the client somewhere in plain text and then passed back to the maps API. Even with HTTPS, the API Key could be easily intercepted by someone viewing the page source, or using a tool to read outgoing requests.

Many other tiling services use CORS to restrict which domains can make requests, but:

  • Azure Maps doesn’t do this at the time of writing and
  • This isn’t real security because the Origin header can be easily modified (I know it’s a forbidden header name for a browser but tools like cUrl can spoof the Origin). More discussion here and here.

So this isn’t a solved problem yet – I’d recommend you consider how you use your API Key very carefully and bear in mind that if you expose it on the internet you’re leaving your account open to abuse. There’s an open issue about this raised on GitHub and hopefully there will be an announcement soon.

Next, set up your web page to use the Leaflet JS library

There’s a very helpful ‘getting started‘ tutorial on the Leaflet website – I added the stylesheet and javascript to my webpage’s head using the code below.

<link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.1/dist/leaflet.css"
      integrity="sha512-Rksm5RenBEKSKFjgI3a41vrjkw4EVPlJ3+OiI65vTjIdo9brlAacEuKOiQ5OFh7cOI1bkDwLqdLw3Zg0cRJAAQ=="
      crossorigin="" />
 
<script src="https://unpkg.com/leaflet@1.3.1/dist/leaflet.js"
        integrity="sha512-/Nsx9X4HebavoBvEBuyp3I7od5tA0UzAxs+j83KgC8PU0kgB4XiK4Lfe4y4cgBtaRJQEIFCW+oC506aPT2L1zw=="
        crossorigin=""></script>

Now add the URLs to your JavaScript to access the tiling services

I’ve included some very simple JavaScript code below for accessing two Azure Maps services – the tiles which display roadmaps and also those which have satellite images.

function satelliteImageryUrl() {
    return "https://atlas.microsoft.com/map/imagery/png?api-version=1&style=satellite&tileSize=512&zoom={z}&x={x}&y={y}&subscription-key={subscriptionKey}";
}
 
function roadMapTilesUrl() {
    return "https://atlas.microsoft.com/map/tile/png?api-version=1&layer=basic&style=main&TileFormat=pbf&tileSize=512&zoom={z}&x={x}&y={y}&subscription-key={subscriptionKey}";
}

If you’re interested in reading more about these two tiling services, there’s more about the road map service here and more about the satellite image service here.

Now add the tiling layers to Leaflet and create the map

I’ve written a JavaScript function below which registers the two tiling layers (satellite and roads) with Leaflet. It also instantiates the map object, and attempts to identify the user’s location from the browser. Finally it registers a control which will appear on the map and list the available tiling services, allowing me to toggle between them on the fly.

var map;
 
function GetMap() {
    var subscriptionKey = '[[[**YOUR API KEY HERE**]]]';
 
    var satellite = L.tileLayer(satelliteImageryUrl(), {
        attribution: '© ' + new Date().getFullYear() + ' Microsoft, © 1992 - ' + new Date().getFullYear() + ' TomTom',
        maxZoom: 18,
        tileSize: 512,
        zoomOffset: -1,
        id: 'azureSatelliteMaps',
        crossOrigin: true,
        subscriptionKey: subscriptionKey
    });
 
    var roads = L.tileLayer(roadMapTilesUrl(), {
        attribution: '© ' + new Date().getFullYear() + ' Microsoft, © 1992 - ' + new Date().getFullYear() + ' TomTom',
        maxZoom: 18,
        tileSize: 512,
        zoomOffset: -1,
        id: 'azureRoadMaps',
        crossOrigin: true,
        subscriptionKey: subscriptionKey
    });
 
    // instantiate the map object and display the 'roads' layer
    map = L.map('myMap', { layers: [roads] });
 
    // attempt to identify the user's location from the browser
    map.locate({ setView: true, enableHighAccuracy: true });
    map.on('locationfound', onLocationFound);
 
    // create an array of the tiling base layers and their 'friendly' names
    var baseMaps = {
        "Azure Satellite Imagery": satellite,
        "Azure Roads": roads
    };
 
    // add a control to map (top-right by default) allowing the user to toggle the layer
    L.control.layers(baseMaps, null, { collapsed: false }).addTo(map);
}

Finally, I’ve added a div to my page which specifies the size of the map, gives it the Id “mymap” (which I’ve used in the JavaScript above when instantiating the map object), and I call the GetMap() method when the page loads.

<body onload="GetMap()">
    <div id="myMap" style="position:relative;width:900px;height:600px;"></div>
</body>

If the browser GeoServices have identified my location, I’ll also be given an accuracy in meters – the JavaScript below allows me to draw a circle on my map to indicate where the browser believes my location to be.

map.on('locationfound', onLocationFound);
 
function onLocationFound(e) {
    var radius = e.accuracy / 2;
 
    L.marker(e.latlng)
        .addTo(map)
        .bindPopup("You are within " + radius + " meters from this point")
        .openPopup();
 
    L.circle(e.latlng, radius).addTo(map);
}

And I’ve taken some screenshots of the results below – first of all the results in the MS Edge browser showing roads and landmarks near my location…

roads

…and swapping to the satellite imagery using the control at the top right of the map.

satellite

Results in Firefox and Chrome

When I ran this in Firefox and Chrome, I found that my location was identified with much less accuracy. I know both of these browsers use the Google GeoLocation API and MS Edge uses the Windows Location API so this might account for the difference on my machine (Windows 10), but I’d need to do more experimentation to better understand. Obviously my laptop machine doesn’t have GPS hardware, so testing on a mobile phone probably would give very different results.

roads2

Wrapping up

We’ve seen how to use the Azure Maps tiling services with the Leaflet JS library, and create a very basic web application which uses the Azure Maps tiling services to display both road and landmark data, and also satellite aerial imagery. It seems to me that MS Edge is able to identify my location much more accurately on a desktop machine than Firefox or Chrome on my Windows 10 machine (within a 75m radius on Edge, and over 3.114km radius on Firefox and Chrome) – however, your mileage may vary.

Finally, as I emphasised above, I’ve concerns about the security of a production application using an API Key in plain text inside my JavaScript, and hopefully Microsoft will deploy a solution with improved security soon.


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!