I’m always looking out for interesting new sources of spatial data to provide examples for use in my books and presentations. Microsoft’s own MSDN documentation demonstrates SQL Server spatial methods only using abstract geometries, such as
SET @h = geometry::STGeomFromText('POINT(10 10)', 0);
However, people often tell me that they really appreciate real-world examples instead – not only are they more interesting, but it’s easier to understand a practical use for the method, and they’re also more memorable afterwards. Some examples I’ve used in the past include:
- Use STBuffer() to calculate the free delivery area around a Pizza restaurant
- Use STDisjoint() to ensure that a planned road development does not intersect a protected area of natural beauty
- Use STIntersection() to identify the section of the Via Appia Roman road that passed through the malaria-infested Pontine marshes.
Anyway, I thought I’d share a new source of data I came across recently – it’s a free dataset of (UK) points of interest designed for importing into Garmin sat-nav systems. As such, it contains practical POIs, categorised into things like museums, mountains, petrol stations, and restaurants. However, since it’s distributed in a simple CSV format, it’s super easy to import the data into SQL Server or other systems as well. Here’s how:
Download the dataset of POIs from the garminpoi.co.uk website at http://garminpoi.co.uk/?page=download
When unzipped, you’ll find it contains a number of categorised CSV files (each with an accompanying BMP icon, which you could use if you want to display this data using pushpins on an SSRS report or Bing Maps, for example):
2. Create a Format File
All of the CSV files follow exactly the same structure – with columns for Longitude, Latitude, a Name, and Description. This can be described by the following bulk format file, which you should save as garminpoi.xml:
<?xml version="1.0"?> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="0" xsi:type="CharTerm" TERMINATOR=","/> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=","/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=","/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/> </RECORD> <ROW> <COLUMN SOURCE="0" NAME="Longitude" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="1" NAME="Latitude" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="3" NAME="Details" xsi:type="SQLVARYCHAR"/> </ROW> </BCPFORMAT>
3. Open the CSV file in SQL Server
Now, you can access the data from the CSV file directly in SQL Server using OPENROWSET, supplying the name of the CSV file you want to load following the BULK keyword, and the formatfile created above following the FORMATFILE keyword. The CSV files contain column headers, which you can skip by specifying FIRSTROW=2. What’s more, you can pass the Latitude and Longitude coordinate values from the CSV straight to the Point() method of the geography datatype to create point instance at each location on-the-fly.
For example, try executing the following code listing:
SELECT *, geography::Point(Latitude,Longitude,4326) AS TescoStore FROM OPENROWSET( BULK 'C:\Users\Alastair\Downloads\GarminPOI\Tesco.csv', FORMATFILE='C:\temp\garminpoi.xml', FIRSTROW=2 ) AS GarminPOI;
Result? An instant map of all Tesco branches in the UK!