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.

Visualizing Spatial Data – Toggling map layers

Last time I showed you how to add a PushPin to your map control. But what if you want to toggle that (or any other) layer in your map control?

The reason I wanted to do this, is that I want to allow the users of my report to toggle different layers in the map control. That way, you can pre-build all the different layers you think your users need into a single report, and just let them toggle the layers themselves. That saves you a lot of time building a report for every single combination of layer-types.

Multi-layer map control
In order to keep it simple, I used the report from my previous post. Specifically, the version with the standard PushPins. And the only layer I want them to be able to toggle, is the PushPin layer on top of the basic map.

Creating a parameter
Because your want to present an easy solution to your users, I want to create a Boolean parameter that they can use to toggle the PushPin layer. To create this parameter, right-click on Parameters in Report Builder, and choose “Add Parameter”:

In the general options window, make sure you fill in a Parameter Name, a question for your users (so they understand what they need to do), and a Data Type (in this case a Boolean, that’s either True or False):

Because you don’t want to keep your users waiting if they open your report, you might want to give the Boolean a default value. You can do this by adding a default value to your Boolean:

By doing this, the Boolean is True by default. So the user doesn’t have to pick a value if he runs the report for the first time. This might come in handy if you need this as an extra feature in your report, and not a feature you need to configure before the report shows your data.

Expression
Now you need to configure your map layer to use the parameter you created to toggle it. Double-click on your map control, and once you see the Map Layers menu on the right, you can do this by right-clicking on “Layer Data”:

Now click on Visibility, and under Layer visibility, click the last option, and press the Fx button:

In the Expression window, fill in the following expression:

Now if you run the report, you’ll see the Boolean appear in the top bar of your report. By default, it’ll show the PushPins:

And if you change the Boolean to False and press “View Report”, you’ll see the may without PushPins:

With a little help from my friends…
This post isn’t just written by me, but also with the help of Valentino Vranken (Blog | @ValentinoV42. He helped me out on Twitter, because my expression didn’t work as expected. So Valentino: I really appreciated your help! Thank you! ;)

Visualizing Spatial Data – Show locations with a PushPin

After my last two posts (Creating a spatial report, Create an interactive report), I came across a feature I never saw before. Apparently if you add a point layer to your report, you get different layer-options in the list.

Starting with a single layer
To start off easy, you could use the same steps I used to create my first report. This is a single layer report with a full map of (in this case) the Netherlands.

The next step is adding a few pushpins. How about visualizing the three biggest cities in the Netherlands: Amsterdam (790.000 people), Rotterdam (616.000) and Den Haag (The Hague, 502.000).

Creating a Point layer
Before creating a point layer, you need to determine what data you want to present to your users. In my case, I chose the center of each city. This might not be the actual center, but I tried to get as close to the center as I could.

Now that you have your data, you can create the Point layer in your report. Start by double-clicking on your map control, and in the “Map Layers” window, click on “New layer wizard:

Now choose the bottom option “SQL Server spatial query”, and click next.

Choose the bottom option to add a new dataset to your report, and click next.

Click next to choose the datasource that’s already in your report, and click next.

Now click on the option “Edit as Text” in the query designer:

Type or paste your query in the editor, and press the Run button. Once you’ve done that, you’ll see the result in the bottom window:

In the next window, make sure you add a Point layer, and not a Polygon layer:

Now you will see the new layers I mentioned before:

For this report, choose the bottom one: Analytical Marker Map, and click next.

In the next window, select the dataset that contains your points, and click next.

In the window with options, make sure you select the right type of marker (in this case PushPin):

Once you’ve done that, click Finish. Now you see a double layer map with 3 PinPoint on your map:

Using a custom PinPoint
I can imagine that you might want to use your own PinPoint image on your report. This is possible, but remember this might screw up the readability of your report.

In the Map Layers menu, right click your point layer, and click Point Properties:

Now change your marker type from PinPoint to Image, and click Import:

Now if you select an image, the name of your image will show in the “Use this image” text box. If you want to, change the “Marker size”, so your logo will appear larger on the map. Now if you click OK, and run the report, you’ll see something like this:

And if you can’t resist, you can download this EPIC marker right here ;)

Visualizing Spatial Data – Create an interactive report

In my previous blog post I showed you how to create a report containing spatial data. But what if you want to give your users a little bit more information. How about a mouse-over tooltip, of what they’re actually looking at? Or maybe even a click-through to another report or website? Let’s take a look at how to achieve this.

Report
I’m starting with the report that I created in my previous blog post. I’m not going to add data to the report, but instead change some options.

Mouseover tooltip
Because you’re working with a full map in your report, it might be hard to see what kind of location you’re looking at. So the easiest way to fix this, is to add labels to your map control. But if you have a lot of small polygons, you might end up with somethings that looks like this:

In the image above you see the province of South-Holland. In the red rectangle, you see the 2nd largest city of the Netherlands: Rotterdam. But this illustrates the issue, that if you have a lot of small polygons, you won’t be able to see the labels.

So a workaround for this, is to add a tooltip (a mouseover label) to your report. This will show the label you selected, if you (or the user of your report) can see what the polygon represents. So how can we do that?

Go to your Design window, and right-click on a polygon in your map control, and click on “Polygon Properties”:

In the next window, click on the “Fx” button next to the Tooltip textblock:

In this window, enter the following expression:

Now if you hover over your report, you’ll see the following tooltip:

 
Clickable polygons
If you want to spice things up even more, you could also add a click-event to your report. In my case, I wanted to add a click-event that showed the users the Wikipedia page of the city.

Start by right-clicking again on a polygon in your map control, and click “Polygon Properties”. Now go to the Action tab, and click the “Fx” button again:

Now fill in the following expression:

Now if you go to your report, you can click on a polygon and it will open up the Wikipedia page for that specific city.

Another option would be to open up a report via a click-event, to show your users more information from another report. Or maybe you want to redirect your users to a specific page on your intranet. Just imagine all the opportunities… ;)

Visualizing Spatial Data – Creating a spatial report

In the last few posts in the series, I showed you how to enrich your dataset with spatial data. Now you need to start thinking about how you want to visualize your data, so that (for example) your business analysts can start using the data. But how do you start with that?

For every report I create, I use Report Builder 3.0 myself. This tool is a bit smaller and more responsive than the Business Intelligence Development Studio (BIDS).

Create the basic report first
Starting to build your report starts with creating a blank report from the menu in Report Builder:

Once you’ve done that, you need to add a data source to your report:

Then add a map control to your report, by right-clicking on you report, and choose Map:

Double-click on the map control, and in the menu that appears on the right, choose “New layer wizard”:

Click the last option (SQL Server spatial query) and click next.

Now you’re asked if you would like to create a new dataset in you report. In this case, you can just click next.

The same counts for the next step, datasources. Just select the datasource you created earlier, and click next.

In the next menu, we can select the data we want to use. In this case, I just want to select a city name and a spatial object. You can write this query in your SQL Server Management Studio (SSMS), or in this window. I wrote it in SSMS, and I want to copy it to my report. If you want to do the same, you need to click the option “Edit as Text”:

Just paste your query into the editor, and press the run button. Report Builder will retrieve your data, and shows you the results below:

Click next, and select the options you like in the next few menus. I’ll stick with the basic options. I didn’t change anything in the first menu, and in the next chose “Basic Map”:

Click next, click Finish. There you go, you just created your very first Spatial Report! :)

Enriching your dataset – What are your technical options?

In my previous post I tried to explain how to enrich your dataset, so you can start using spatial data. You’ve seen that there are free options, or you can buy a bunch of files with the information you need.

This time I’ll try to explain the technical option you have. The options all contain the Google Geocoding API, because this is free, and it’s fairly accurate. But this will also work for the files you buy from (for example) GfK GeoMarketing, which I did business with once.

Write your own application
One of the easiest an quickest options for me was to write an application to fetch my data from the Google API. I’m not a die-hard programmer, but I know my way around C# as far as I need to. So with a little help from my colleagues, I managed to write a Windows Forms application that calls the Geocoding API. The results of the API call are either just shown on screen (in a GridView), or exported to Excel (using LinqToExcel and ExcelExporter).

Another option I intend to build in, is the export from and to a SQL Server database. But because this is a project I work on in my own time, this could take a couple of weeks.

CLR
One of the other options I’ve found, is a CLR that calls the API. The CLR you write, is basically a .NET application that you load into SQL Server. It’s more complicated than that, but to keep this story moving on, I’ll leave it at that.

One of the many downsides of using a CLR, is your local DBA. Every DBA I’ve encountered in my life told me: “Don’t use a CLR, EVER!!! Because that’s the biggest security hole you can open up on a SQL Server”. To be completely honest, I’m not that familiar with CLR’s, but I guess it isn’t as easy as that. There might be some pros and cons regarding that…

SSIS Package
In my quest to find all possible options, I found this great article by Donabel Santos (Blog | @sqlbelle). In her article she describes how you can create an SSIS package that fetches a Lat/Long for every record in the dataset you use as input.

Talking about this with Koen Verbeeck (Blog | @Ko_Ver) on Twitter made me realize that for normal businesses, this might be the best option. In every company I know, there’s always a server running SQL Server Integration Services that you can use to execute such a package.

So, what’s your choice?
Looking at the options above, I’m guessing the option you choose depends on the company you work for. I guess that smaller companies would choose the SSIS package over building a tool. But if your company has a development department with a bunch of software developers, writing your own tool might be a better option. In that case writing your own tool gives you more flexibility, because not only your DBA can debug an error in an SSIS package, but every developer can debug the tool they’ve written.

If you’ve got some .NET/C# knowledge, and you have the time to dive into CLR’s, that might be your best option. CLR’s are loaded into SQL Server, and are (as far as I can tell) blazing fast. So if you’re looking for performance, and don’t want external tools or packages to fetch your data, go for it!

Starting with Spatial Data – Enriching your dataset

The biggest issue when you start working with spatial data, is asking yourself the question: “What do I want to visualize?”. Do you want to visualize the areas your clients come from? Or do you want to calculate in which areas your new potential clients live? The next hurdle to take, is thinking about how to enrich the data you have now.

The easy way out
Almost every company has a accessible database with client information. The easy way out is to download a free spatial data set (like described in an earlier post), and visualize your data by joining your data set on the downloaded data set, based on country-, province-, or city name. This way, you don’t alter your data, but you’re still able to visualize your data.

Enriching your data
The way to go (in my opinion), is to permanently enrich your data. In that case, you’re more flexible in visualizing your data. Another advantage is that you can always group your data in the same way as mentioned above. If you want to group it by country, province or city, you can still do it. But remember: you can always group your data, but mining your data is difficult without accurate coordinates. And there is a free option for enriching your data, if you don’t want to spend a lot of money and time on it.

Last year, I spend a lot of time working with spatial data. One of the biggest brainteasers was how to enrich our data set, without actually disrupting the normal running processes. You don’t want to change your underlying data set, and then find out you need to rewrite all your software.

No seriously, it’s free…
One of the options I thought of, was converting a postal code, or zip code for my American friends (Seriously USA?! Do you have a different word for EVERYTHING?! :)). The conversion can’t be done by number crunching, but needs to be done by looking it up. One of the options is to look up every postal code by hand. But if you have a few million addresses in your database, that’s not really an option.

One of the options I came across was the free Google Geocoding API. This allows you to translate a postal code into a Latitude and Longitude. And these coordinates can be plotted on a map.

And if you have a Latitude and Longitude, you can use Reverse Geocoding to translate your coordinates into a postal code.

A quick example of this, is a tool I’ve written. It translates a postal code into Lat/Long:

Or translates a Lat/Long into postal code, using the Reverse GeoCoding:

I know this isn’t a pretty tool yet, but I’m still working on it. Until now, it was only a trial to see if the Google API returns the data I need. This tool is also still based on the 2.0 API, which is deprecated since March 8, 2010. It will still work until March 8, 2013, but I need to rebuild it soon.

The next step of course, is to expand the tool to allow the user to translate a whole list of postal codes, all at once. You don’t want to copy-paste the millions of postal codes by hand into the tool, and copy-paste the result back into an Excel workbook or SQL Server table.

But since I’m not a .NET developer, this could take a while. If you’re a developer, and interested in expanding and developing the postal code finder, contact me. Then we can make it a publicly available tool.

Conclusion
Looking at all of this, you’ll see that it’s possible to enrich your data for free, and without a lot of work. Okay, you still need to figure out if your postal code information is correct. Or let the API respond back with the message “G_GEO_UNKNOWN_ADDRESS”, which tells you that the address might be incorrect. Another limit is the number of API calls: 15.000 calls per day. But then again, it’s free, and you can’t have it all…

Follow

Get every new post delivered to your Inbox.

Join 53 other followers