By: Siddharth Mehta | Comments | Related: > SQL Server 2017
Problem
Location data can be found in almost every kind of data domain. The most generic form of location-based data are addresses of users, which contains geographic information like country, state, and city. In this time of visual analytics, users are used to analyzing any geographic data on a map and every other reporting tool does support overlaying geographic data onto a map.
Consider the use-case of applications like Uber, in which you receive an email of a trip receipt with a map of your trip embedded in the email. This is one such scenario where one may have to use the location as an input and based on this generate a map of the location. There can be scenarios like showing static maps of frequently used locations on a web-site and generating maps dynamically for a city that is rarely referenced. In summary, with the increased mobility of data and services, there is an increasing trend to geocode data and generate a map on the fly from every possible service including a database.
In this tip, we will learn how to extract geocodes of a location and generate a map of any given location without the use of any external reporting tool, just by using what is available in SQL Server 2017.
Solution
R can be used to generate geocodes and a map based on the given location. SQL Server 2017 ships with an option to install and use R language inside T-SQL to analyze data. We will use R with T-SQL for the above mentioned purpose.
Before we start with the implementation, first it's necessary to understand what geo-coding means. As per Google documentation, Geocoding is the process of converting addresses (like a street address) into geographic coordinates (like latitude and longitude), which you can use to place markers on a map or position the map. Reverse geocoding is the process of converting geographic coordinates into a human-readable address. Google provides APIs and libraries for geocoding as well as geocoding. We will not be developing any application code using these libraries, instead we will be using packages in R that abstracts this functionality and interfaces with Google APIs. Follow the below steps to setup an environment and code that demonstrates geocoding and map generation.
Step 1
Assuming you have SQL Server 2017 or any higher version installed with SQL Server Mangement Studio (SSMS), open SSMS. It is also assumed that you have installed and configured R on this instance of SQL Server. If not, you can follow this installation section of the R tutorial and install or configure R on SQL Server.
Step 2
The first scenario that we want to address is extracting geocodes – latitude and longitude of any given location based on an address in textual format. For simplicity, we will use any city name and extract the latitude and longitude. For this we will need the following packages installed on the R installation that you would have installed as a part of the SQL Server installation – ggplot2, ggmap, and ggalt. You can read the instructions from this link on how to install packages on a R server.
Step 3
Once you have installed these packages, open a new query window and type the following code.
EXECUTE sp_execute_external_script
@language = N'R',
@script = N'
# load packages
library(ggplot2)
library(ggmap)
library(ggalt)
# Geocode New York
inputcity <- toString(InputDataSet$City[1])
Geocodes <- geocode(inputcity) # get longitude and latitude
print(Geocodes)
',
@input_data_1 = N'Select cast(''New York'' as varchar(8)) as City
Let’s try to understand the above code.
- Sp_execute_external_script is the store procedure used with external scripts that can be executed in SQL Server.
- @language parameter signifies the script language being used, which is R in this case.
- @input_data_1 parameter signifies the data that will be read from SQL Server and will be passed to the R script in the @script parameter. For now, we are using a hard-coded value of a city – “New York” in the select query. You can easily replace this query to pass data from tables of your choice.
- @script parameter contains the actual R script which will be executed. In the script, the first part uses the library R function to load libraries from the packages that we installed in the previous step. Then we are assigning the value from the input data set to a variable named inputcity. Then using the geocode function, we are passing the inputcity as a parameter to extract latitude and longitude. The geocode function of ggmap uses Google's Geocoding API to turn addresses from text to latitude and longitude pairs. If you interested in Google’s Geocoding API, you can read more from here. Finally, after receiving the geocoded values in the variable Geocodes, we are printing it on the console.
You can use this approach to generate geocodes for many locations in bulk. Once you execute this code, the output would look as shown below.
Step 4
The next task we have is to generate a map file based on the location. To achieve this, type the below code in the query window and execute.
EXECUTE sp_execute_external_script
@language = N'R',
@script = N'
# load packages
library(ggplot2)
library(ggmap)
library(ggalt)
inputcity <- toString(InputDataSet$City[1])
jpeg(filename="C:\\temp\\Geocode.jpeg", width = 1080, height = 1080, units = "px", pointsize = 15, quality = 100,);
# Google Road Map
map <- qmap(inputcity, zoom=12, source = "google", maptype="roadmap")
print(map)
dev.off();
',
@input_data_1 = N'Select cast(''New York'' as varchar(8)) as City'
Let’s try to understand this code.
- Sp_execute_external_script is the store procedure used with external scripts that can be executed in SQL Server.
- @language parameter signifies the script language being used, which is R in this case.
- @input_data_1 parameter signifies the data that will be read from SQL Server and will be passed to the R script in the @script parameter. For now, we are using a hard-coded value of a city – “New York” in the select query. You can easily replace this query to pass data from tables of your choice.
- @script parameter contains the actual R script which will be executed. In the script, the first part uses the library R function to load libraries from the packages that we installed in the previous step. Then we are assigning the value from the input data set to a variable named inputcity.
- Using the jpeg function, we are just specifying a file path where we want to save the output of the map that will be created using the gmap function.
- Using the gmap function, we are passing the inputcity as the parameter, and maptype as roadmap. This function uses the Google Static Maps web-service and returns a Google map of the location based on the parameters. You can read more about this function from here. This map is assigned to the variable map. In order to save it in a file, we are printing the map on the graphics device generated using the jpeg function, and then closing the device using dev.off function.
- After the script is executed, it will save the output to the file path mentioned in the jpeg function of the script. The output of the script would look as shown below.
Step 5
Finally, you can navigate to the directory that you specified in the jpeg function, where the Google map file should have been created. Below is the map image.
This approach can be used to generate Google maps in bulk and can be saved to a file outside the database. Also, the output can be assigned to a varchar(max) variable and saved in a table. In this way you can easily create and store a maps repository, which the front-end application can use as required.
Next Steps
- Try using different options of the gmap function to generate different kinds of Google maps for more elaborate and specific addresses.
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
