Load Garmin POI data to SQL Server

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:

1. Download

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):

image

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!

image

About these ads
This entry was posted in Spatial, SQL Server and tagged , . Bookmark the permalink.

2 Responses to Load Garmin POI data to SQL Server

  1. Pingback: Self-Service BI Mapping with Microsoft Research’s Layerscape–Part 1 « Chris Webb's BI Blog

  2. Pingback: Live Streaming Κάλυψη Συνεδρείων

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s