(678) 835-8539 info@key2consulting.com


Andy Kim, Business Intelligence Consultant at Key2 Consulting

By: Andy Kim

 

Introduction

If you ask a SQL professional to name some data types, you might hear: varchar, int, datetime, decimal, or something to that effect. Rarely do people mention the spatial data types of: geography and geometry. This is because most SQL professionals haven’t been exposed to these data types.

Well – I would like to change that here.

The spatial data types are implemented using windows CLR functions, which becomes more apparent when working with the spatial insert scripts, queries, indexing, etc.
 

The Spatial Data Types

SQL Server comes with two spatial data types built in:

Geography – Represents data in a round-earth coordinate system like GPS Latitude/Longitude coordinates

Geometry – Represents data in flat coordinate system

These data types have many similarities and some differences as well.
 

Spatial Data Analysis – Demo Outline

In this demo, I would like to:
1) Create a custom restaurant map using Google Maps.
2) Export the restaurant geography data from Google Maps in Keyhole Markup Language (KML) format.
3) Prepare and load the data into SQL Server.
4) Run a simple query on the spatial data.
5) Demonstrate spatial data analysis by adding spatial richness to a PowerBI report.

Typically, you would get large, complete data sets from GIS systems, or other files, but I would like to do something simpler here. For this demo, I have manually put together a list of coordinates using Google Maps.
 

1. Creating a custom restaurant map using Google Maps.

a) Go to Google Maps and sign in.

Google Maps 1

b) Click the menu button to open it.

Google Maps 2

c) Click on “Your Places”.

Google Maps 3

d) Click on “Maps”.

Google Maps 4

e) Click “Create Map”.

Google Maps 5

Now, there is a blank map to add interest points.

Google Maps 6

f) You can rename the map, and beginning adding points to your map. I searched for restaurants and added them to my map.

Google Maps 7

Google Maps 8

Notice the Latitude/Longitude shows up once you add a place to your map.

Google Maps 9

g) In this way, you can create a map with all your points of interest. Here is an example of a finished map.

Google Maps 10

 

2. Exporting the Data from Google Maps in KML format.

a) Click the menu button, then click on “Export to KML/KMZ”…

Google Maps 11

b) Set the appropriate options and download the KML.

The KML looks a lot like XML. You can open it with a text editor like Notepad.

KML code

 

3. Loading the Data into SQL Server.

There are several ways you can load this data into SQL Server, but in this example, I will use a free online tool and Microsoft Excel.

a) Navigate to http://convertcsv.com/kml-to-csv.htm. Click “Choose File”, and select the KML file you downloaded. Click the “KML to Excel” button and download the outputted Excel file.

Convert KML to Excel

b) Open the downloaded file in Excel, then add a new column to calculate the Insert command.

In this case, the Excel formula is:

= CONCATENATE(“INSERT INTO dbo.DimRestaurantGeography (RestaurantName,RestaurantGeography) VALUES (‘”,A2,”‘, geography::STPointFromText(‘POINT(“,C2,” “,D2,”)’, 4326))”)

KML to Excel Spreadsheet

c) In SQL Server, let’s run a script to create the table and load it with the data from above.

SQL Server Script

 

4. Running a simple query on the Spatial Data.

As I mentioned earlier, querying spatial data looks a little different than your standard SQL queries due to the Windows CLR implementation. However, these functions are very powerful. Here is an example query below that finds all points (restaurants) within 0.4 miles of my office.

 

5. Add Geography Dimension to PowerBI Report.

a) Create a simple fact table and load it for this demo.

b) Open PowerBI Desktop and Get Data from the SQL Server source similar to below:

Power BI Desktop SQL Statement

Here are some examples of standard reports you might see:

Power BI Example Report 1

Next, let’s use the “Map” visual to leverage the geography data. Configure as shown below. Be sure to map the Latitude and Longitude data to the appropriate fields.

Power BI Map Feature

As you can see, adding a spatial dimension can add value and richness to your data model. In many industries such as utilities, this spatial aspect is crucial. This is how all their equipment is mapped, where customers are located, how problem areas are detected, etc.
Alternatively, Power BI can use postal addresses or the concatenated coordinates in the “Location” field to perform the same operation, but the real power of the spatial data types is the ability to run queries on them. Here, we have just scratched the surface of spatial data types. In reality, there are more complex objects such as lines, polygons, and buffers. There are also other functions to calculate all kinds of spatial metrics.

Now that you have been exposed to spatial data types, I encourage you to learn more about them and their functions. If you have recently worked with any cool projects using spatial data types, please tell me about it. I would love to hear how you have used them.
 

References

  1. Microsoft: Spatial Data Types Overview
  2. Microsoft: Spatial Data (SQL Server)
  3. Microsoft: Spatial Data (Geography)
  4. Microsoft: Spatial Types (Geometry – Transact-SQL)

 

Questions?

Thanks for reading! We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, (Star Wars), or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
 

Keep your data analytics knowledge sharp by subscribing to our mailing list

Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!

 
 
 


Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications. 

Share
Tweet
Share
Reddit