Create, Populate, and Transform Geographic Coordinates in SQL Server

By: Rick Dobson | Comments | Related: > TSQL

Problem

Please demonstrate how to create and populate a SQL Server table for storing geographic coordinates in a string format. Also, show how to transform the geographic coordinates into two common numeric formats. Illustrate how to accomplish these goals with SQL Server stored procedures that leverage previously created T-SQL scripts.

Solution

A couple of prior tips "Collecting, Storing, and Displaying Geographical Coordinates in SQL Server" and "From String to Numeric Geographic Coordinates with T-SQL" demonstrated how to collect geographic coordinates for a set of world capital cities and transform their display into a couple of alternative formats.

This tip leverages previously developed T-SQL code for implementing the solution in the prior tips. The previously developed code is encapsulated in three separate stored procedures. Each stored procedure populates a table of geographic coordinates. The formats for the geographic coordinates show different ways of storing geographic coordinates within a database.

The first stored procedure handles the populating of a table with jurisdiction names and geographic coordinates in a string format for capital cities from around the globe.
The second stored procedure extracts components from within the string-valued geographic coordinates into degree-minute-second numeric formats along with string hemisphere indicators.
The third stored procedure transforms the degree-minute-second coordinate values along with their hemisphere indicators into two signed numeric geographic coordinates that are appropriate for designating the SQL Server point geographic data type.

Encapsulating previously developed scripts in stored procs simplifies re-using the code in different contexts. When a solution includes two or more stored procs, you can run the stored procedures individually. Also, wrapping T-SQL code for specialized applications within stored procedures can simplify updating an overall solution. For example, if the code in one stored proc needs to be modified to either add new features or accommodate a specification change, this can be accomplished without affecting or even reviewing the code for other stored procs in the solution.

An overview of using multiple stored procedures for a SQL Server solution

It is well known that stored procedures allow you to encapsulate code for easy re-use. This tip illustrates how to accomplish this goal with T-SQL code from two prior tips. This goal can be implemented with a create proc statement that designates a name for the stored procedure. The syntax for encapsulating code can be as simple as the following.

The create proc statement initiates the creation of a new object with a name that can run the code within it.
The as keyword in the script below delimits the declaration of a new stored procedure from the code that runs when the stored procedure is invoked.
The go keyword ends the code batch and causes SQL Server to create the new stored procedure object named my_first_stored_proc_name in the dbo schema of the default database. The go keyword is optional when the create proc statement is a stand-alone statement, but it can be required if additional data definition language statements trail the create proc statement.

create proc dbo.my_first_stored_proc_name
as
   --T-SQL encapsulated code for the first task
go

If you create multiple stored procedures as parts of an overall solution, then the output from one stored procedure can build on the tables populated from one or two previously run stored procedures. This tip demonstrates that kind of SQL Server solution. With this approach, you can simplify the execution of a complex task by running a few relatively less complex code blocks instead of one long, hard-to-follow, and maintain code block. For example, the following script sequentially runs three stored procedures. If the first stored procedure populates a table that serves as a data source for the second stored procedure which, in turn, populates a table used by the third stored procedure, then the solution is a system of interrelated stored procedures.

exec dbo.my_first_stored_proc_name
exec dbo.my_second_stored_proc_name
exec dbo.my_third_stored_proc_name

This kind of system is implemented for geographic coordinates stored in a SQL Server database within this tip.

The solution framework starts by creating three tables – one to be populated by each of three stored procs.
The first table is populated by the my_first_stored_proc_name stored proc. This table represents a collection of geographic coordinates stored as string values in a table.
The second table is populated by the my_second_stored_proc_name stored proc. The results set generated by second stored proc incorporates the results set from the first stored proc and adds a new format for representing the geographic coordinates from the first table.
The third table is populated by the my_third_stored_proc_name stored proc. This table adds a third format for representing the geographic coordinates from the first and second tables.

The remainder of this tip walks you through the highlights of successive parts of the solution.

The first part of the solution is the creation of three tables.
The second part of the solution is the creation of three stored procedures. Each stored procedure is reviewed in a separate section.
The third part of the solution is the successive invocation of each of the three stored procedures and the display of the tables populated from running the stored procs.

Creating the tables for the solution

The first step in the solution is to create each of the three tables. There are two parts to creating a fresh copy of each table. The following code block commences with a reference to a default database for the overall solution. This database (WorldCapitalCoordinates) stores the three tables as well as three stored procs in the solution. You can use the name of any database you choose as your default database.

The first table (city_country_coordinates) has just three columns. The column names are city, country, and coordinates. The city column has name of the capital city for a country, such as London for the United Kingdom or Washington, D.C. for the United States. Within the code for this tip, all three columns have a nvarchar data type of up to 50 characters in length.

The coordinates column, like the other two columns, is a string. While a latitude value and a longitude value can be represented partly with numbers, the numbers have meaning within the context of some symbols, such as the degree (°) symbol. You can type the degree symbol by holding the Alt key as you type 0176 on the numeric keypad. The degree symbol is a Unicode character.

There are also hemisphere indicators for latitude and longitude values. A latitude value can reside in the northern (N) hemisphere or southern (S) hemisphere. Similarly, a longitude value can reside in either of two hemispheres -- the eastern (E) hemisphere or the western (W) hemisphere.

The second table has the name city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates. This table has three sets of columns.

The first set of columns are the three from the first table (city, country, coordinates).
The second set are four columns for the latitude value in a coordinates field.
- These latitude columns have a numeric data type for degree, minute, and second values.
- The N_S hemisphere indicator is a character field with a length of 1.
  - The N hemisphere indicator is for latitude values that are north of the equator.
  - The S hemisphere indicator is for latitude values that are south of the equator.
The third set are four columns for the longitude value in a coordinates field.
- The longitude columns also have a numeric data type for degree, minute, and second values.
- The E_W hemisphere indicator is a character field with a length of 1.
  - The E hemisphere indicator is for longitude values that are east of the prime meridian.
  - The W hemisphere indicator is for longitude values that are west of the prime meridian.

The third table has the name city_country_coordinates_from_dms_to_signed_dd. The This table has the same columns as in the second table plus two additional columns. The additional columns are the last two in the table.

The next-to-last column has the name lat_dms_to_lat_dd. This is a numeric field for latitude values in a decimal-degree format. Values to the left of the decimal point are for whole degrees. Digits to the right of the decimal point are for fractions of a degree. Furthermore, the lat_dms_to_lat_dd field denotes the hemisphere by its sign. Latitude values in the southern hemisphere have a negative value, and latitude values with a positive value are in the northern hemisphere.
The last column has the name lng_dms_to_lng_dd. This is a numeric field for longitude values in a decimal-degree format with digits to the left of the decimal point denoting whole degrees and digits to the right of decimal point representing fractions of a whole degree. Just as with latitude values, the sign of a longitude value specifies its hemisphere. A positive value is for a longitude value in the eastern hemisphere, and a negative value is for a longitude value in the western hemisphere.

use WorldCapitalCoordinates
go
 
-- create a fresh version of the city_country_coordinates table
drop table if exists dbo.city_country_coordinates
go
 
create table city_country_coordinates(
city nvarchar(50)
,country nvarchar(50)
,coordinates nvarchar(50)
) 
 
-- create a fresh version of the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table
drop table if exists dbo.city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates
go
 
create table dbo.city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates(
   city nvarchar(50) NULL,
   country nvarchar(50) NULL,
   coordinates nvarchar(50) NULL,
   lat_degrees real NULL,
   lat_minutes real NULL,
   lat_seconds float NULL,
   N_S_hemisphere varchar(1) NOT NULL,
   lng_degrees real NULL,
   lng_minutes real NULL,
   lng_seconds float NULL,
   E_W_hemisphere varchar(1) NOT NULL--,
)
go 
 
-- create a fresh version of the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table
drop table if exists dbo.city_country_coordinates_from_dms_to_signed_dd
go
 
create table dbo.city_country_coordinates_from_dms_to_signed_dd(
   city nvarchar(50) NULL,
   country nvarchar(50) NULL,
   coordinates nvarchar(50) NULL,
   lat_degrees real NULL,
   lat_minutes real NULL,
   lat_seconds float NULL,
   N_S_hemisphere varchar(1) NOT NULL,
   lng_degrees real NULL,
   lng_minutes real NULL,
   lng_seconds float NULL,
   E_W_hemisphere varchar(1) NOT NULL,
   lat_dms_to_lat_dd real NULL,
   lng_dms_to_lng_dd real NULL
)
go

A stored procedure to populate the city_country _coordinates table

The following script shows an excerpt from this tip's code to create a fresh version of the pop_city_country_coordinates stored proc. This procedure populates the city_country_coordinates table with data copied from Wikipedia. This prior tip "Collecting, Storing, and Displaying Geographical Coordinates in SQL Server") demonstrates the process for collecting the coordinates data for world capitals. There are just a few T-SQL statements in the script.

The first statement is a drop proc if exists statement.
- This statement conditionally drops the pop_city_country_coordinates stored proc if it exists in the dbo schema of the default database, which is WorldCapitalCoordinates in this tip.
- The go keyword is a SSMS statement to separate into two different batches the code before and after the go keyword.
The second T-SQL statement is a create proc statement. The layout of the statement is described in the "An overview of using multiple stored procedures for a SQL Server solution" section.
The third statement is an insert into statement for populating the city_country_coordinates table with a succession of rows having capital city names, country names, and geographic coordinates for the cities on the globe. The row data is excerpted to show just the first and last five rows. In the download file for this tip, you can find the full set of 89 rows comprising this tip's sample dataset.

-- create a fresh version of the pop_city_country_coordinates stored procedure
drop proc if exists dbo.pop_city_country_coordinates
go
 
create proc dbo.pop_city_country_coordinates
as 
 
-- insert city, country, and city coordinates from the Wikipedia website
-- into the city_country_coordinates table in the WorldCapitalCoordinates database
insert into dbo.city_country_coordinates
values 
('Abu Dhabi', 'United Arab Emirates','24°28′N 54°22′E')
,('Accra','Ghana','5°33′N 0°12′W')
,('Addis Ababa','Ethiopia','9°1′48″N 38°44′24″E')
,('Amman', 'Jordan', '31°56′59″N 35°55′58″E')
,('Ankara', 'Turkey', '39°55′48″N 32°51′00″E')

…

,('Warsaw', 'Poland', '52°14′N 21°1′E')
,('Washington, D.C.', 'United States', '38.9101°N 77.0147°W')
,('Wellington', 'New Zealand', '41°17′20″S 174°46′38″E')
,('Yaoundé', 'Cameroon', '3°52′N 11°31′E')
,('Yerevan', 'Armenia', '40°10′53″N 44°30′52″E')

A stored procedure to populate the city_country_coordinates _from_str_coordinates_to_numeric _dms_coordinates table

The following script presents the broad outline of an approach for creating a stored procedure named pop_city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates in the dbo schema of the WorldCapitalCoordinates database. The stored procedure populates the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table. In order to improve the script's readability, comment statements within the script serve as place markers for T-SQL code within the script. This tip's download file contains the script with all the T-SQL code. The code is fully explained in this prior tip "From String to Numeric Geographic Coordinates with T-SQL". The remainder of the text in this section provides a high-level overview of the code and selected topics of special interest for this tip.

This newly populated table contains the three fields from the city_country_coordinates table as well as eight new fields.

The first four of the eight new fields disaggregate latitude values from the string coordinates in the city_country_coordinates table.
- lat_degrees, lat_minutes, and lat_seconds are numeric data type values for displaying the latitude value in a coordinates field in degrees, minutes, and seconds.
- N_S_hemisphere is a string value for displaying the latitude's hemisphere.
The second four of the eight new fields disaggregate longitude values from the string coordinates in the city_country_coordinates table.
- lng_degrees, lng_minutes, and lng_seconds are numeric data type values for displaying the longitude value in a coordinates field in degrees, minutes, and seconds.
- E_W_hemisphere is a string value for displaying the longitude's hemisphere.

Notice that the script reveals a union operator for concatenating results sets from two different select statements.

The first select statement returns 86 rows from the sample data in this tip. These rows are for source coordinates values with string coordinates in degree-minute-second (dms) format.
The second select statement returns 3 rows from the sample data in this tip. These rows are for source coordinates values with string coordinates in decimal-degree (dd) format.
It is common in datasets for geographic coordinates to encounter some rows in one format and some rows in another format. Therefore, a robust T-SQL geographic application should account for this possibility.

Numeric values are expressed as whole degrees and parts of a whole degree.

One degree can have up to 60 fractional parts called minutes.
In turn, each whole minute can have up to 60 fractional parts called seconds.
If it is necessary to express the precision of a latitude value or a longitude value more precisely than to the nearest whole second, then the second component of a latitude value or a longitude value can be rounded to express second values to the nearest tenth, hundredth, thousandth, and so forth of a second.

The parsing of string values for degrees, minutes, and seconds from the coordinates field of the city_country_coordinates table depends on the position of the degree symbol (°), the minute symbol (‘), and the second symbol ("). The degrees symbol is a Unicode character that has a nvarchar data type and a length of 1 character. If the default format for a sql file is not Unicode, then you will be prompted the first time that you try to save a file with Unicode characters. Reply yes to the prompt to have the degree symbol represented correctly when the file is re-opened.

-- create a fresh version of the 
-- pop_city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates stored procedure
drop proc if exists dbo.pop_city_coun7try_coordinates_from_str_coordinates_to_numeric_dms_coordinates
go
 
create proc dbo.pop_city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates
as 
 
-- extract dms geographic coordinates as numeric values 
-- from string dms geographic coordinates
 
declare @deg_sym nvarchar(1) = '°'
 
-- for converting from string to numeric degree-minute-second (dms) values
insert into dbo.city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates
 
-- T-SQL select statement items from the city_country_coordinates table that are in 
-- dms format
 
-- along with T-SQL expressions that extract string coordinate component values and convert the 
-- string values to numeric dms values and extract string hemisphere indicators
 
-- a where clause filters the coordinates field values to just those in dms format
-- 86 rows in this results set are from this select statement
 
union
 
-- extract dms geographic coordinates as numeric values 
-- from string dd geographic coordinates
 
-- T-SQL select statement items from the city_country_coordinates table that are in 
-- dd format
 
-- along with expressions that extract and convert string dd geographic coordinates 
-- to numeric dms values and extract string hemisphere indicators
 
-- a where clause filters the coordinates field values to just those in dd format
-- 3 rows in this results set are from this select statement

A stored procedure to populate the city_country_coordinates _from_dms_to_signed_dd table

The following script shows the T-SQL code for a stored procedure that converts latitude and longitude values in degree-minute-second format to decimal-degree format. Additionally, the conversion represents the decimal-degree values as signed numbers so that northern and eastern hemisphere values appear, respectively, as latitude and longitude positive values. In contrast, southern and western hemisphere values appear, respectively, as latitude and longitude negative values.

Here are a few comments about the code to help you follow the process that it implements.

As with the preceding two stored procs, the code starts by conditionally dropping a stored procedure. In the script below, the name of the stored procedure is pop_city_country_coordinates_from_dms_to_signed_dd within the dbo schema of the default database.
The create proc statement also repeats the design feature of an insert-select statement. The insert statement deposits the results set from the select statement into the city_country_coordinates_from_dms_to_signed_dd table.
- The select statement derives its source data from the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table. The previous section provides an overview of how to populate the source data table for this section.
- The first eleven columns of the city_country_coordinates_from_dms_to_signed_dd table are pulled directly from the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table.
- Both the twelfth and thirteenth columns are populated by two expressions – one for latitude values (lat_dms_to_lat_dd) and the other for longitude values (lng_dms_to_lng_dd). The structure of each computed field is the same.
  - A case statement converts degree, minute, and second numeric values to a decimal-degree value. The case statement also assigns a sign to the decimal-degree value.
    - The value is positive for latitude and longitude values, respectively in the northern and eastern hemispheres.
    - The value is negative for latitude and longitude values, respectively in the southern and western hemispheres.
  - The case statements are nested within a round function. This function rounds the result from the case statement to the nearest millionth of a degree.

-- create a fresh version of the pop_city_country_coordinates_from_dms_to_signed_dd stored procedure
drop proc if exists dbo.pop_city_country_coordinates_from_dms_to_signed_dd
go
 
create proc dbo.pop_city_country_coordinates_from_dms_to_signed_dd 
as 
-- for converting from dms to dd
insert into dbo.city_country_coordinates_from_dms_to_signed_dd
select
   city
  ,country
  ,coordinates
  ,lat_degrees
  ,lat_minutes
  ,lat_seconds
  ,N_S_hemisphere
  ,lng_degrees
  ,lng_minutes
  ,lng_seconds
  ,E_W_hemisphere
  ,round(
   case
     when N_S_hemisphere = 'N' then lat_degrees+lat_minutes/60 + lat_seconds/3600
     else -1*(lat_degrees+lat_minutes/60 + lat_seconds/3600)
     end, 6) lat_dms_to_lat_dd
  ,round(
   case
     when E_W_hemisphere = 'E' then lng_degrees+lng_minutes/60 + lng_seconds/3600
     else -1*(lng_degrees+lng_minutes/60 + lng_seconds/3600)
     end,6) lng_dms_to_lng_dd
from
dbo.city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates

Running the stored procs and displaying the populated tables

Up until this point in the tip, three tables are created and three stored procs are created. However, the tables are empty because no stored proc has run to populate any of the tables. Happily, it is very simple to run the stored procedures and display results from the populated tables.

Here is a short script to run successively the first, second, and third stored procs.

The pop_city_country_coordinates stored proc populates the city_country_coordinates table. This first stored proc does not depend on a source table. Instead, its internal code inserts a set of 89 rows to the city_country_coordinates table. The values for each row are derived by copying and pasting text from Wikipedia, which is made available to users without charge. I donate to Wikipedia because it is such a valuable internet resource.
The pop_city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates stored proc depends on the city_country_coordinates table populated by the first stored proc and a lot of parsing code, data type transformations, and mathematical expressions for converting geographic coordinates in string format to numeric values for latitude and longitude values represented as degrees, minutes, and seconds as well as string-valued hemisphere indicators. The results set from the code inside the second stored proc populates the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table.
The third stored proc, pop_city_country_coordinates_from_dms_to_signed_dd, uses the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table as a data source for converting the numeric degree, minute, and second values as well as the string hemisphere indicators to coordinate values as two signed numeric values. The select statement with its source data and transformations from the third stored proc adds two columns to the values from the source data -- one for the latitude and the other for longitude within a geographic coordinates field.

-- run the stored procs
exec dbo.pop_city_country_coordinates
 
exec dbo.pop_city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates
 
exec dbo.pop_city_country_coordinates_from_dms_to_signed_dd

The next script includes three select statements. The first select statement is for all rows from the city_country_coordinates table. The second select statement is for all rows from the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table. The third select statement is for all rows from the city_country_coordinates_from_dms_to_signed_dd table.

-- display geographical coordinates only with strings, 
-- with strings and dms, with strings and dms, and dd
select * from dbo.city_country_coordinates
 
select * from dbo.city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates
 
select * from dbo.city_country_coordinates_from_dms_to_signed_dd

Here are two excepts from the select statement based on the city_country_coordinates table. The first excerpt shows the first five rows from the results set, and the second excerpt shows the last five rows from the results set. The coordinates column has the coordinates as string values with latitude values preceding longitude values.

Except for Washington, D.C., all coordinates column values are in a degree-minute-second format.
- Up to three trailing symbols follow number representations in degree-minute-second format.
  - The ° symbol denotes whole degree values.
  - The ‘ symbol denotes whole minute values.
  - The " symbol denotes whole second values.
- Some city capitals, such as Abu Dhabi in the first row of the first screen excerpt, have minute values but no second values.
- Other city capitals, such as Addis Ababa in the third row of the first screen excerpt have minute as well as second values.
The data for Washington, D.C. in the second row of the second screen excerpt displays its coordinates value in a decimal-degree format. With a decimal-degree format, numbers to the left of the decimal point represent whole degree values. In contrast, numbers to the right of the decimal point represent fractional degree values. Both the digits to the left and right of the decimal point are part of the degree value.

Here are two additional excepts from the select statement based on the city_country_coordinates_from_str_coordinates_to_numeric_dms_coordinates table. Again, the first excerpt is for the first five rows, and the second excerpt is for the last five rows.

The first three columns in both excerpts match those from the city_country_coordinates table.
The next four columns show the degree, minute, and second values along with the hemisphere indicator extracted from the latitude component of the coordinates column.
The last four columns show the degree, minute, and second values along with the hemisphere indicator extracted from the longitude component of the coordinates column.

Here are the final two excerpts for this tip. These two excerpts show the first and last five rows from the select statement for the city_country_coordinates_from_dms_to_signed_dd table. The first eleven columns are based on the source table from the city_country_coordinates_from_dms_to_signed_dd table.

The twelfth column displays the latitude value as a signed decimal-degree value. When the latitude value is for the southern hemisphere, such as for Wellington in the second screen shot, then its sign is negative. Otherwise, its sign is positive.
The thirteenth column displays the longitude value in a signed decimal-degree value. When the longitude is in the western hemisphere, such as for Washington, D.C. in the second screen shot, then its sign is negative. Otherwise, its sign is positive.

Next Steps

You can verify this tip's code by running the script in the download file for this tip.
You can run the queries from the tip within any convenient database storing geographic coordinates data. If you have a set of geographic coordinates other than those used in this tip, then you can try them with the code. You will, of course, need to modify the code in the pop_city_country_coordinates stored proc to extract geographic coordinates data from your alternative data source.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Create, Populate, and Transform Geographic Coordinates in SQL Server Tables