Creating your own spatial data with Windows Phone

In the other parts of the Spatial Data Series, I showed you how you can import, query and use spatial data. But how do you generate your own spatial data?

 
Apps
The last few months I tried to generate my own spatial data in a number of ways. Some trials were more successful than others. But when I found the right app, it was a great step in the right direction. At this moment, I use the Run The Map app. That app allowed me to record routes and, most important, was able to export the routes that were recorded. So how can you do that?

 
Start by running the app (in this case, from the start screen of my Windows Phone):

 
When the app is started, press the big start button to start recording your route:

 
When you start the recording, the start button will change into a stop button. If you’re done recording, just press the stop button.

Now that you’ve recorded your route, you want to export it. To export the recording, open the menu at the bottom of the screen, and press History:

 
In the history screen, press the synchronize button in the bottom menu:

 
This will allow you to export the “XML” file to your OneDrive. You’ll see a login window, and once you’ve entered your information, you’re good to go. The sync might take a while, depending on the size of your recorded route, and the speed of your wireless connection. The files will be placed in a folder called “RunTheMap”, that can be found in the root of your OneDrive.

 
Processing the XML
So now that we have the export file, we need to retrieve the data from that file. If you export the data to OneDrive, you’ll get 3 types of files:

– .DAT file, which you don’t need to use
– .KML file, which you can use to import the data into SQL Server
– .GPX file, which we will use to import the data into SQL Server

 
Let’s assume your .GPX file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.1">
 <metadata>
  <name>2014-06-04</name>
 </metadata>
 <trk>
  <name>S1</name>
  <desc></desc>
		<trkseg>
			<trkpt lat="51.916667" lon="4.500000">
				<ele>-7</ele>
				<time>2014-09-01T08:00:00Z</time>
				<hdop>0.0</hdop>
				<vdop>0.0</vdop>
				<pdop>0.0</pdop>
			</trkpt>
			<trkpt lat="50.851368" lon="5.690973">
				<ele>-7</ele>
				<time>2014-09-01T16:00:00Z</time>
				<hdop>0.0</hdop>
				<vdop>0.0</vdop>
				<pdop>0.0</pdop>
			</trkpt>
		</trkseg>
 </trk>
</gpx>

 
This sample file basically consist of 2 points: the center of Rotterdam and the center of Maastricht. Both cities are in the Netherlands, and are just used to illustrate the output. The content of the file can be copy-pasted into the script below (just replace the sample string with your own XML content from the .GPX file):

DECLARE @XML TABLE
  (XML_COLUMN XML)


INSERT INTO @XML
SELECT
'<?xml version="1.0" encoding="utf-8"?>
<gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.1">
 <metadata>
  <name>2014-06-04</name>
 </metadata>
 <trk>
  <name>S1</name>
  <desc></desc>
		<trkseg>
			<trkpt lat="51.916667" lon="4.500000">
				<ele>-7</ele>
				<time>2014-09-01T08:00:00Z</time>
				<hdop>0.0</hdop>
				<vdop>0.0</vdop>
				<pdop>0.0</pdop>
			</trkpt>
			<trkpt lat="50.851368" lon="5.690973">
				<ele>-7</ele>
				<time>2014-09-01T16:00:00Z</time>
				<hdop>0.0</hdop>
				<vdop>0.0</vdop>
				<pdop>0.0</pdop>
			</trkpt>
		</trkseg>
 </trk>
</gpx>'


;WITH X_CTE AS
(
  SELECT
    T1.Name.query('.') AS Name,
    T2.X_Content.query('.') AS X_Content
  FROM   @XML AS X
  CROSS APPLY XML_Column.nodes('/gpx/trk') AS T1(Name)
  CROSS APPLY XML_Column.nodes('/gpx/trk/trkseg/trkpt') AS T2(X_Content)
),
XML_Data AS
(
SELECT
  Name.value('(/trk/name)[1]', 'VARCHAR(20)') AS NAME,
  X_Content.value('(/trkpt/@lat)[1]', 'VARCHAR(20)') AS LAT,
  X_Content.value('(/trkpt/@lon)[1]', 'VARCHAR(20)') AS LON,
  X_Content.value('(/trkpt/ele)[1]', 'VARCHAR(20)') AS ELE,
  X_Content.value('(/trkpt/time)[1]', 'VARCHAR(20)') AS TIME,
  X_Content.value('(/trkpt/hdop)[1]', 'VARCHAR(20)') AS HDOP,
  X_Content.value('(/trkpt/vdop)[1]', 'VARCHAR(20)') AS VDOP,
  X_Content.value('(/trkpt/pdop)[1]', 'VARCHAR(20)') AS PDOP
FROM X_CTE
)


SELECT
	NAME,
	LAT,
	LON,
	ELE,
	LEFT(TIME, CHARINDEX('T', TIME, 0) - 1) AS DATE,
	LEFT(RIGHT(TIME, 9), LEN(RIGHT(TIME, 9)) - 1) AS TIME,
	HDOP,
	VDOP,
	PDOP
--INTO SpatialData.dbo.RunTheMapData
FROM XML_Data
WHERE 1 = 1

 
This big query will process the data, and will output a clean representation of the file contents:

 
With this data (for example, if you insert it into a table by uncommenting the INSERT line in the example script), you can create a line layover on a map (a manual about downloading and importing this data can be found here:

 
For clarification, I’ve drawn a circle around the start- and endpoint of Rotterdam and Maastricht. I’ve let SQL Server draw a line between the 2 points, that represents the route that was recorded. But obviously the route that you recorded will be a lot more detailed.

 
Creating a route
To map your full route on a map, you need to combine all the coordinates in the table into one line object. To achieve this, I’ve written a small cursor script (yes, I know, and I’m really sorry! But this was the quickest way at the time to do this…), that combines all the coordinates into 1 line. You can download it here. If you don’t want to use that, check out this blog post from Rob Farley (Blog | @rob_farley), who shows you how it can be done in a clean and neat way.

 
The opportunities…
If that’s done, you can use it as an overlay for any map you’ll like. For example, you could map your traveling history with this. Or you could check out which route you drive when you drive to work in the morning, or the route home in the evening. Parcel companies and truck drivers could track the routes the drivers take with this. The opportunities are as great as your own imagination.

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

%d bloggers like this: