Using SQL Spatial to find waypoints close to a route

I’m going away on another cycling adventure soon, riding north through France and Germany before a short stint in both The Netherlands and Belgium. No opportunity for a long trip this time so the ride is just over 500 miles which should be achievable in two weeks.

I already have the route we will follow as a GPX and of course there is the amazing Archies Camping, a collection of all campsites in the countries we are riding through. We never plan where to stop each day so Archies is invaluable in finding a place to stay.

Problem is that France and Germany in particular have a lot of campsites and most are nowhere near the areas we will be visiting. Putting the full list onto my GPS or iPhone would slow it down too much – instead it would be much better to filter the list of Archies campsites to show only those close enough to the route to be useful.

This is all easily achievable in just three lines of T-SQL thanks to the spatial types in SQL Server (with some PowerShell to load the data into the database).

Full script is available here, I will go through key points below.

1. The Campsite table

First create a table to store the campsites including the Lat/Lon (as numerics) and the Location as a geography type. Geography is the right type to use as the data being stored are coordinates on the globe.

Next, load the CSV data dump from the Archies Europe zip file and insert a row into the table for each campsite using the geography::point function to convert from latitude and longitude.

This takes a few minutes for the 30000 or so European campsites.

2. The Route table

I exported the route from Garmin Basecamp as a 12000-point GPX *track* (this is important – convert any routes to tracks beforehand) which is much too detailed for my requirements, so it’s simplified to 500 points using GPSBabel:

Next, build up the INSERT statement to run against the SQL Server instance. The geography type for the route is a LINESTRING. They can be created from a list of coordinates using the STLineFromText function and a textual representation of the line in the format LINESTRING('Lon Lat, Lon Lat, Lon Lat, ...').

In this case the LINESTRING is built by reading the simplified GPX file (which is just XML) and looping over all trackpoints:

The T-SQL below inserts it into the DB as a valid linestring using SRID 4326 (so that the distance calculation will be in metres):

You can query TheRoute table to see the route looks correct:

3. Finding campsites within 30km of the route

SQL Server does all of the heavy lifting here. Use the STDistance method to get the distance from each campsite to the route and filter to include those less than 30Kms away.

Save each result in GPX format so it can be easily loaded into other applications or onto your GPS device.

4. Checking it all out

Import the new GPX into BaseCamp, view it alongside your route and check everything looks OK.

5. Happy riding!

This entry was posted in PowerShell, Scripting, Spatial, SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *