String to Numeric Geographic Coordinates in SQL Server with T-SQL

By: Rick Dobson | Comments | Related: > TSQL

Problem

Please demonstrate how to convert geographic coordinates in string format to either of three widely used numerical formats: degree-minute-second, decimal-degree, or signed decimal-degree.

Solution

Geographic coordinates are useful for tracking the global position of items. Just like the items can have names, such as the capital cities in the US (Washington, DC) and Germany (Berlin), so can the geographic coordinates for items. Because the geographic coordinates can have names, their values can be represented with strings. On the other hand, the components of the coordinates for a point on the earth are latitude and longitude numeric values. Therefore, geographic coordinates components can be represented with numeric data types as well as strings.

While it is frequently useful to denote geographic coordinates as string values, it can also be useful to represent geographic coordinates with numeric values. SQL Server's geography point data type designates a locale's position based on a pair of signed numeric values – one for the latitude and another for the longitude. This tip shows how to transform geographic coordinates in other formats to the one that SQL Server requires for its geography point data type.

This tip presents and describes T-SQL scripts for converting string format representations for geographical coordinates into either of two common numeric formats: degree-minute-second or decimal-degree. The ability to convert between formats is vital because it is common to find geographic coordinates distributed in multiple formats by a single data provider as well as, of course, across different data providers.

A brief primer for geographic coordinates

Geographic coordinates are for tracking the global position of items, such as weather stations and world capitals. For example, I originally encountered geographic coordinates when I was tracking observations from weather stations spread across five states to populate a SQL Server weather data warehouse. A prior tip, Collecting, Storing, and Displaying Geographical Coordinates in SQL Server, focuses on the collection, storing, and displaying of filtered subsets of geographic coordinates for 89 capitals for countries spread across the globe. Other use cases for geographic coordinates include aerial and naval navigation, tracking the path of hurricanes and other natural phenomena across the globe, as well as keeping track of where a corporation's warehouse and distribution centers are located on a worldwide basis.

The prior tip Collecting, Storing, and Displaying Geographical Coordinates in SQL Server on collecting, storing, and displaying geographical coordinates introduces you to the basics of processing geographical coordinates in string format. As far back as its 2014 release, SQL Server offered special processing capabilities for geographic coordinates; here are two links documenting some of these features (the point geographic data type and how to make sense of SQL Server geography data type).

Latitude and longitude values can be specified with degrees, minutes, and seconds or decimal degrees. Just knowing a latitude value and a longitude value for a point does not unambiguously specify a point's locale on the globe. All the latitude values in the northern hemisphere are also available in the southern hemisphere. You need to designate the hemisphere as well as for the latitude and longitude values to designate unambiguously a point on the globe.

The current tip builds on the introduction to geographical coordinates for SQL Server professionals. While that initial tip introduced the basics of processing geographical coordinates in string format, this tip expands your understanding of geographical coordinates by showing how to convert from string format to either of three other numeric formats (degree-minute-second, decimal-degree, and signed decimal-degree). The SQL Server geographical point data type designates the geographical coordinates for a global point in a signed decimal-degree format.

An introduction to the data source used in this tip

The following script lists the 89 rows from a table with geographical coordinates used for demonstrations in this tip. Each row starts with the capital city name and country name for a world capital. The data are derived based on a subset from the Wikipedia list of national capitals by latitude and related Wikipedia pages. For example, this web page is a related page with the coordinates for Abu Dhabi, the capital city of the United Arab Emirates. See this prior tip Collecting, Storing, and Displaying Geographical Coordinates in SQL Server for details on going from the list of national capitals in Wikipedia to the city_country_coordinates table in the WorldCapitalCoordinates SQL Server database.

use WorldCapitalCoordinates
go
 
-- all 89 rows in the city_country_coordinates
select 
 city
,country
,coordinates
from dbo.city_country_coordinates

The following pair of screen shots show the first and last six rows, respectively, of output from the preceding script. A careful examination of the coordinates column values will yield several interesting tips about the layout of coordinates in string format.

All three columns in the city_country_coordinates table have a nvarchar data type with a length of up to 50 characters.
- Each row has a latitude value before a longitude value within the coordinates field. Each coordinates field also has two hemisphere indicators. The latitude value along with the longitude value includes a trailing character value.
  - The letters N and S denote, respectively, points in the northern and southern hemispheres for the latitude values.
  - The letters E and W denote, respectively, points in the eastern and western hemispheres for the longitude values.
- All the coordinates values in the first screen excerpt are for decimal-minute or decimal-minute-second values. You can think of decimal-minute values as a subset of decimal-minute-second values.
  - The degree symbol (°) is a Unicode character that immediately trails all degree values. You can type the degree symbol by holding down the Alt key as you type 0176.
  - The single apostrophe (') is an ASCII character that immediately trails the minute value in a degree-minute value or a degree-minute-second value in both latitude or longitude characters within the coordinates string.
  - Some of the coordinates values in the first screen excerpt do not have second indicators ("). This can be because there are no seconds for the latitude or longitude values or because there is a fractional value that does not round to at least one second.
- The coordinates value for Washington D.C. is in decimal-degree format within the second screen excerpt. Notice that there is no minute indicator (') in this coordinates value for Washington, D.C. All the remaining coordinates values in the second screen excerpt are in either degree-minute or degree-minute-second format. Therefore, these remaining five coordinates values all have a minute indicator (').

The following code block extracts all rows from the city_country_coordinates table with a coordinates value in decimal-degree format. All of these values have no minute indicator ('). The criterion for the where clause (charindex('''',coordinates) = 0) specifies a search for all rows with a missing minute indicator. There are 3 of 89 rows with a decimal-degree format.

-- scan coordinates values to identify coordinates in decimal-degree format
-- 3 of 89 rows in city_country_coordinates have coordinates values
-- without a minute symbol ('), which indicates a decimal-degree format
select 
 city
,country
,coordinates
from dbo.city_country_coordinates
where charindex('''',coordinates) = 0

A script searching for coordinates values in degree-minute or degree-minute-second format merely must search for rows with a minute indicator in the coordinates value. Therefore, its criterion for the where clause would be: charindex('''',coordinates) != 0. There are 86 rows in the city_country_coordinates table for this tip in either degree-minute or degree-minute-second format.

The preceding code sample indicates how to filter coordinates values when they are in string format. Another example of processing coordinates values in string format appears below. This script returns the counts for a cross tabulation table with the number of rows in northern and southern hemispheres by those in eastern and western hemispheres. Notice that a nested query is used.

In the inner query, two case statements assign values to each row based on whether the row is in the northern or southern hemisphere as well as whether the row is in the eastern or western hemisphere.
In the outer query, the inner query rows are grouped by N_S_hemisphere, E_W_hemisphere values. A count function reports the number of rows at the intersection of N_S hemisphere rows with E_W hemisphere rows.

-- count of capitals by N_S_hemisphere and E_W_hemisphere
select N_S_hemisphere,E_W_hemisphere ,count(*) [coordinates in cell]
from
(
-- N_S_hemishpere and E_W_hemishpere extracted from coordinates
select 
 city
,country
,coordinates
,
case
   when charindex('N', coordinates) != 0 then 'N'
   else 'S'
end N_S_hemisphere
,
case
   when charindex('E', coordinates) != 0 then 'E'
   else 'W'
end E_W_hemisphere
from WorldCapitalCoordinates.dbo.city_country_coordinates
) coordinates_w_extracted_hemishperes
group by N_S_hemisphere, E_W_hemisphere

Here is the output from the preceding script. There are four rows in the output: two rows for the eastern hemisphere with counts for the northern and southern hemispheres as well as two more rows for the western hemisphere with counts for the northern and southern hemispheres.

Parsing degree-minute-second numeric values from coordinates strings

The objective of this section is to present and describe T-SQL code for extracting degree-minute-second latitude and longitude numeric values from the coordinates field in the city_country_coordinates table. Recall that there are 86 rows in this tip's sample data (the city_country_coordinates table) with a degree-minute-second format in the coordinates column. It was also noted previously that degree-minute format is merely a subset of degree-minute-second format.

A two-step process extracts the latitude and longitude degree, minute, and second numbers from the 86 coordinates strings. As reported in the preceding section, 3 coordinates strings are in decimal-degree format as opposed to degree-minute-second format.

The first step finds the positions for degree-minute-second values in the coordinates strings. You need to compute the position of degree, minute, and second values because the length of strings and the position of latitude and longitude component values can change from one coordinates value to the next.
- The latitude and longitude values for some coordinates include degree, minute, and second values. However, the coordinates for other capital cities include just degree and minute values.
- Also, the length of fields can change from one coordinates value to the next based on the location of capital cities. For example, latitude degree numbers can be either 1 or 2 digits long for values from 0 through 90. On the other hand, longitude values can range from 1 through 3 digits for values from 0 through 180.
The coordinates values in the city_country_coordinates table are string values with a nvarchar data type having a length of up to 50 characters. After the characters are extracted from the string in the first step, they need to be converted to numeric values for subsequent calculations within this tip.

The following T-SQL script computes a set of parsing helper field values for extracting degree, minute, and second values from the 86 coordinates values that are in degree-minute-second format.

The where clause criterion at the end of the script filters for rows from the city_country_coordinates table that are in degree-minute-second format.
The first select field value is coordinates. This column contains coordinates column values from the city_country_coordinates table when the values are in degree-minute-second format.
The second select field returns the position of a blank column (first_blank) separating the latitude coordinates components (latcoordinate) from the longitude coordinates components (lngcoordinate).
The third and fourth field values return the position of the degree symbol in the coordinates field for the latitude value (lat_deg_sym_pos) and the longitude value (lng_degree_sym_position).
The fifth and sixth field values return the position of the minute symbol in the coordinates field for the latitude value (lat_min_sym_pos) and the longitude value (lng_min_sym_pos). The minute symbol is escaped by using two consecutive minute symbol characters ('') inside a charindex function. These two consecutive minute symbols are embedded within apostrophe symbols inside charindex functions.
Case statements are used to define the position of the second component value within latitude and longitude values.
- The case statement for the latitude second component (lat_ss_field_is_null) can return a null value if there is no second value in the latitude within the coordinates field. Otherwise, the lat_ss_field_is_null select field value is the position of the second component in the latitude within the coordinates field.
- The case statement for the longitude second component (lng_ss_field_is_null) similarly returns either a null value or the position of the second indicator if there is one in the coordinates field.

-- parsing helpers for extracting degree-minute-second values from string coordinates values
declare @deg_sym nvarchar = '°'
select 
   coordinates
   -- separates end of latcoordinate from beginning of lngcoordinate
  ,charindex(' ',coordinates) first_blank 
  ,charindex(@deg_sym,coordinates) lat_deg_sym_pos
  ,charindex(@deg_sym,coordinates,charindex(@deg_sym,coordinates)+1) lng_deg_sym_pos
  ,charindex('''',coordinates) lat_min_sym_pos
  ,charindex('''',coordinates,charindex('''',coordinates)+1) lng_min_sym_pos
  ,case
     when charindex('"',coordinates) = 0 then null
     else charindex('"',coordinates)
     end lat_ss_field_is_null
  ,case
     when charindex('"',coordinates,charindex('"',coordinates)+1) = 0 then null
     else charindex('"',coordinates,charindex('"',coordinates)+1)
     end lng_ss_field_is_null
from dbo.city_country_coordinates
where charindex('''',coordinates) != 0

Now that the code for computing parsing helpers is available from the preceding script, we can join the output from the script with a subset of rows from the original values from the city_country_coordinates table. The subset is for the 86 out of 89 rows from the city_country_coordinates table that specify latitude and longitude values in degree-minute-second format within the coordinates column. An inner join between the rows from the city_country_coordinates table and the parsing helpers results set provide a data source for computing degree-minute-second components in numeric format along with northern/southern hemisphere indicators for latitude values and eastern/western hemisphere indicators for longitude values.

Here is the script to perform the join and use the results set from the join to compute numeric degree-minute-second latitude and longitude values along with hemisphere indicators for the latitude and longitude values.

The top part of the select statement returns city, country, and coordinates column values from the subset of city_country_coordinates table rows with degree-minute-second values.
- The results for latitudes appear in the four columns after the coordinates column.
  - The first three of these columns are degrees, minutes, and seconds as real data types.
  - The fourth column is for hemisphere indicators of N (for northern hemisphere) and S (for southern hemisphere).
- The results for the longitudes appear in the four columns after the four columns for latitudes. The longitude values are output in the same format as the latitude values.
The bottom part of the query begins with the select's statement from clause.
- It performs the join between the 86 rows from the city_country_coordinates table with coordinates values specified in degree-minute-second format and the rows from the parsing_helpers query.
- The join is performed on coordinates column values from each data source.

-- first round of coordinates string parsing into degree-minute-second real values
-- with N_S_hemisphere and E_W_hemisphere for values starting in degree-minute-second string format
 
declare @deg_sym nvarchar(1) = '°'
 
select 
   city_country_coordinates.city  
  ,city_country_coordinates.country
  ,city_country_coordinates.coordinates
  ,cast(substring(city_country_coordinates.coordinates,1,lat_deg_sym_pos-1) as real) lat_degrees
  ,cast(substring(city_country_coordinates.coordinates,lat_deg_sym_pos+1
  ,lat_min_sym_pos-lat_deg_sym_pos-1) as real) lat_minutes
  ,case
     when isnull(lat_ss_field_is_null,0) = 0 then 0
     else cast(substring(city_country_coordinates.coordinates, lat_min_sym_pos+1,lat_ss_field_is_null+1-lat_min_sym_pos-2) as real)
     end lat_seconds
  ,case
     when charindex('N', city_country_coordinates.coordinates) != 0 then 'N'
     else 'S'
     end N_S_hemisphere
---------------------------------------------------------------------------------------------------
  ,cast(substring(city_country_coordinates.coordinates,first_blank+1,lng_deg_sym_pos-first_blank-1) as real) lng_degrees
  ,cast(substring(city_country_coordinates.coordinates,lng_deg_sym_pos+1,lng_min_sym_pos-lng_deg_sym_pos-1) as real) lng_minutes
  ,case
     when isnull(lng_ss_field_is_null,0) = 0 then 0
     else cast(substring(city_country_coordinates.coordinates, lng_min_sym_pos+1, lng_ss_field_is_null+1-lng_min_sym_pos-2) as real)
     end lng_seconds
  ,case
     when charindex('E', city_country_coordinates.coordinates) != 0 then 'E'
     else 'W'
     end E_W_hemisphere
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
from
(
  -- latitude and longitude coordinates in degree-minute-second string format
  -- 86 of 89 rows in city_country_coordinates have coordinates values
  -- with a minute symbol ('), which indicates a decimal-minute-second string format
  select 
     city
    ,country
    ,coordinates
  from dbo.city_country_coordinates
  where charindex('''',coordinates) != 0
) city_country_coordinates
 
inner join
 
(
  -- parsing cols from dd_mm_ss coordinates
  select 
     coordinates
     -- separates end of latcoordinate from beginning of lngcoordinate
    ,charindex(' ',coordinates) first_blank  
    ,charindex(@deg_sym,coordinates) lat_deg_sym_pos
    ,charindex(@deg_sym,coordinates,charindex(@deg_sym,coordinates)+1) lng_deg_sym_pos
    ,charindex('''',coordinates) lat_min_sym_pos
    ,charindex('''',coordinates,charindex('''',coordinates)+1) lng_min_sym_pos
    ,case
       when charindex('"',coordinates) = 0 then null
       else charindex('"',coordinates)
       end lat_ss_field_is_null
    ,case
       when charindex('"',coordinates,charindex('"',coordinates)+1) = 0 then null
       else charindex('"',coordinates,charindex('"',coordinates)+1)
       end lng_ss_field_is_null
  from dbo.city_country_coordinates
  where charindex('''',coordinates) != 0
) parsing_helpers
 
on city_country_coordinates.coordinates = parsing_helpers.coordinates

Here are two screen shots with the first six rows and the last six rows from the results set for the preceding script.

The first three columns from the first screen excerpt below exactly match the first six rows from the complete results set of columns in the city_country_coordinates table. See the first screen shot in the "An introduction to the data source used in this tip" section to confirm the results for yourself.
The last six rows from the screen shot below do not match the last six rows from the last six rows from the complete results set for the city_country_coordinates table. In the last six rows, there is no row for Washington, D.C., whose coordinates are in a decimal-degree format as opposed to a degree-minute-second format.
Also, there are just 86 rows (as opposed to 89 rows) in the screen shot below because the following results are just for rows with coordinates values formatted as degrees, minutes, and seconds.
The most important difference is for the 8 columns to the right of the coordinates column.
- The first 4 of these 8 columns are for latitude values.
- The second 4 of these 8 columns are for the longitude values.
- You can confirm the transformation from string values in the coordinates column to separate columns with numbers in the last 8 columns by comparing the latitude and longitude component values from the coordinates column to values for individual columns in the last set of 8 columns.

Transforming decimal-degree string values to decimal-degree numeric values

When the value in a coordinates field is in decimal-degree format, the process for translating the string value to degree-minute-second format as a numeric value is distinctly different than in the preceding section. Just 3 out of 89 rows in the sample dataset for this tip have decimal-degree formats for latitudes and longitudes. Despite the small frequency of this occurrence, it confirms the tendency for geographic coordinates data to be presented in multiple different formats even from a single data source (in this case, Wikipedia). Consequently, robust solutions for geographic coordinates need the capability to process geographic data in different formats to a shared common format.

Before displaying and describing the T-SQL code for translating string decimal-degree values into numeric degree-minute-second values, this tip explains the logic behind the process for performing the translation. For this translation, it is convenient to start by extracting latitude and longitude values as SQL Server real data type values.

An integer data type translation of the decimal-degree value is the degree term in the degree-minute-second target representation for the translated decimal-degree value. You can accomplish this in two steps.
- First, cast the extracted decimal-degree value from the coordinates field as a real value. You will need this real value to compute the minute component of the decimal-degree value.
- Second, cast the converted real value as an integer value. This integer value is the degree component of the target degree-minute-second representation for the decimal-degree value.
Next, compute the number of minutes in the digits to the right of the decimal in the decimal-degree value. This tip accomplishes this goal in three steps.
- First, subtract the integer value computed for the number of degrees in the first leg of the translation from the real value for the decimal-degree format. This difference is the value to the right of the decimal.
- Second, multiply the difference by 60 because there are 60 minutes in each degree.
- Third, cast the product as an integer value to get the number of whole minutes in the digits to the right of the decimal. This integer value is the number of minutes for the target degree-minute-second representation of the decimal-degree value.
The third step is to compute the number of seconds in the digits to the right of decimal in the decimal-degree value. This tip accomplishes this in three steps.
- First, compute the real data type value for decimal-degree minutes in the coordinates field.
- Second, subtract it from the real data type value for decimal-degree minutes in the coordinates field the number of whole minutes in the coordinates field. This difference is for computing the real value of the number of seconds as a decimal-degree quantity.
- Third, multiply the difference from the preceding step by 60 and round the result to three places, which returns the number of seconds to a relatively high level of precision (one one-thousandth of a second).

Here is the script that implements the calculations for translating decimal-degree string values in the coordinates field to degree, minute, and second values in three separate numeric fields as well as a fourth string field for the hemisphere indicator.

A select statement reports results from the city_country_coordinates table.
The select statement's where clause after the from clause restricts the output to rows without a minute indicator (') in the coordinates field. There are just three rows in the table that match this criterion for rows with their coordinates column in decimal-degree format.
The select statement includes three fields from the source table (city, country, coordinates) along with a collection of 8 T-SQL expressions for computed field values.
- 4 of the 8 T-SQL expressions are for latitude degree, minute, and second components of the degree-minute-second representation of latitude values; the 4^th expression is for either a northern (N) or a southern (S) hemisphere indicator.
- The other 4 T-SQL expressions are for longitude degree, minute, second components of the degree-minute-second representation of longitude values; the 4^th expression is for either an eastern (E) or a western (W) hemisphere indicator.
The 1^st, 2^nd, and 3^rd expressions extract, respectively, degree, minute, and second components as numeric values for latitude values in the coordinates field.
- The latitude values start in the first column of the coordinates field.
- The extracted numeric values have the names lat_degree, lat_minute, and lat_second.
The 4^th expression is a computed field that returns a value of N or S in the N_S_hemisphere field.
The 5^th, 6^th, and 7^th expressions extract, respectively, degree, minute, and second components as numeric values for longitude values in the coordinates field.
- A blank character separates the end of the latitude value from the beginning of the longitude value.
- The longitude value starts after the blank column value trailing the latitude value.
- The extracted values have the names lng_degree, lng_minute, and lng_second.
The 8^th expression is a computed field that returns a value of E or W in the E_W_hemisphere field.

-- translate decimal-degree values in strings to degree-minute-second values as numbers 
-- with N_S_hemisphere and E_W_hemisphere indicators
 
declare @deg_sym nvarchar(1) = '°'
 
select 
   city
  ,country
  ,coordinates
  -- extract from coordinates latitude as a real number 
  -- with an integer part followed by a period and a fractional part
  -- nest the real number inside another cast function that 
  -- extracts just the integer value from the real number
  -- assign the alias name of lat_degree to the integer value
  ,cast( cast(substring(coordinates,1,charindex(@deg_sym, coordinates)-1) as real) as int) lat_degree
  -- extract from coordinates latitude as a real number 
  -- with an integer part followed by a period and a fractional part
  -- then subtract from the real number 60 times the value of lat_degree
  -- then cast the difference as an integer and assign the alias of lat_minute to the result
  ,cast( 
     ((cast(substring(coordinates,1,charindex(@deg_sym, coordinates)-1) as real) 
       - cast(cast(substring(coordinates,1,charindex(@deg_sym, coordinates)-1) as real) as int))*60)
     as int) lat_minute
  -- compute lat_minute as a real number value
  -- then subtract from the real number value lat_minute computed as an integer
  -- multiply the difference by 60
  -- then round the multiplied difference to three places after the decimal
  -- assign the alias lat_second to the final result
  ,round
   (
     (
       ((cast(substring(coordinates,1,charindex(@deg_sym, coordinates)-1) as real) 
         - cast(cast(substring(coordinates,1
         ,charindex(@deg_sym, coordinates)-1) as real) as int))*60)
         - (cast(((cast(substring(coordinates,1,charindex(@deg_sym, coordinates)-1) as real) 
         - cast(cast(substring(coordinates,1
         ,charindex(@deg_sym, coordinates)-1) as real) as int))*60) as int))
     )*60
   ,3) lat_second
  -- extract the hemisphere indicator for the latitude value
  ,case
     when charindex('N', city_country_coordinates.coordinates) != 0 then 'N'
     else 'S'
     end N_S_hemisphere
  ---------------------------------------------------------------------------------------------------
  -- adapt the preceding code to extract longitude, instead of latitude, 
  -- degree-minute-second values starting with the first blank in the coordinates field
 
  -- extract from coordinates starting with the first blank value longitude as a real number 
  -- nest the real number inside another cast function that 
  -- extracts just the integer value from the real number
  -- assign the alias name of lng_degree to the integer value
  ,cast(cast(substring(coordinates,charindex(' ',coordinates)
  ,charindex(@deg_sym, coordinates)-1) as real) as int) lng_degree
  -- extract from coordinates starting with the first blank value longitude as a real number 
  -- then subtract from the real number 60 times the value of lng_degree
  -- then cast the difference as an integer value and assign the alias of lng_minute to the result
  ,cast(
     (cast(substring(coordinates,charindex(' ',coordinates),charindex(@deg_sym, coordinates
        ,charindex(@deg_sym, coordinates)+1)-charindex(' ',coordinates)) as real) 
        - cast(cast(substring(coordinates,charindex(' ',coordinates)
        ,charindex(@deg_sym, coordinates)-1) as real) as int))*60 
     as int) lng_minute
  -- compute lng_minute as a real number value
  -- then subtract from the real number value lng_minute computed as an integer
  -- multiply the difference by 60
  -- then round the difference to three places after the decimal
  -- assign the alias lng_second to the final result
  ,round
   (
      (
        (cast(substring(coordinates,charindex(' ',coordinates)
           ,charindex(@deg_sym, coordinates
           ,charindex(@deg_sym, coordinates)+1)-charindex(' ',coordinates)) as real) 
           - cast(cast(substring(coordinates,charindex(' ',coordinates)
           ,charindex(@deg_sym, coordinates)-1) as real) as int))*60
           - cast((cast(substring(coordinates,charindex(' ',coordinates)
           ,charindex(@deg_sym, coordinates
           ,charindex(@deg_sym, coordinates)+1)-charindex(' ',coordinates)) as real) 
           - cast(cast(substring(coordinates,charindex(' ',coordinates)
           ,charindex(@deg_sym, coordinates)-1) as real) as int))*60 as int)
      )*60
   ,3) lng_second 
  -- extract the hemisphere indicator for the latitude value
  ,case
     when charindex('E', city_country_coordinates.coordinates) != 0 then 'E'
     else 'W'
  end E_W_hemisphere
from dbo.city_country_coordinates
where charindex('''',coordinates) = 0

Here is a screen excerpt showing the results set from the preceding script. Notice that there are just three rows. This is because only 3 of 89 rows have coordinates field values without a minute indicator.

The first three columns are from the city_country_coordinates table.
The next four columns show computed latitude value fields.
The next four columns show computed longitude value fields.

Calculating signed decimal-degree numeric values from degree-minute-second numeric values

This section concludes this tip with one additional transformation demonstration. The conversion for this section is to go from degree-minute-second numeric values to signed decimal-degree numeric values. By using signed decimal-degree values, you can denote the hemisphere for a latitude or a longitude with the sign of the value. Positive values are conventionally used for latitudes and longitudes that are, respectively, in the northern and eastern hemispheres. Conversely, negative values are used to represent latitudes and longitudes, respectively, in the southern and western hemispheres. Signed decimal-degree values are widely used, including by SQL Server for specifying geographical point values.

The numeric degree-minute-second values and the hemisphere indicators computed from the preceding two sections represent all 89 coordinates in the sample data for this tip. The computed values from both sections were copied to a new SQL Server table named city_country_coordinates_dms_w_hemi. This table serves as the source data for this section.

The logic for transforming degree-minute-second values to signed decimal-degree values has two parts.

You first need to convert the minutes and seconds from the degree-minute-second values to fractions of a degree.
- The number of minutes for a latitude or a longitude can be converted to a fraction of a degree by dividing the minutes value by 60. This is because there are 60 minutes in each geographical degree.
- The number of seconds for a latitude or a longitude can be converted to a fraction of a degree by dividing the seconds value by 3600. This is because there are 3600 seconds in each geographical degree.
- You can represent the converted degree-minute-second value as a decimal-degree value by
  - Adding to the degree value from the degree-minute-second value the minute value from the degree-minute-second value divided by 60.
  - and, adding to that quantity, the second value from the degree-minute-second value divided by 3600.
Next, assign a sign to the computed decimal-degree value with these steps.
- Leave the value as positive (no sign is positive) if the latitude is in the northern hemisphere or the longitude is in the eastern hemisphere.
- Make the value negative if the latitude is in the southern hemisphere or the longitude is in the western hemisphere.

Here is a T-SQL script that implements these transformation steps for the values in the city_country_coordinates_dms_w_hemi table.

As you can see, it contains a single select statement that references the dbo.city_country_coordinates_dms_w_hemi table in its from clause.
All the columns from the source table serve as select items, except for the coordinates column. The coordinates column is excluded because the coordinates column is not required for the transformation.
Two case statements enveloped by round functions perform the transformations.
- The lat_dms_to_lat_dd statement performs the transformation for latitude values.
- The lng_dms_to_lng_dd statement performs the transformation for longitude values.
- The round function enveloping each case statement rounds the output from each case statement to one one-millionth of a degree.

-- for converting from dms to 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_dms_w_hemi

Here are the first six rows from the results set from the preceding T-SQL script.

Abu Dhabi, Addis Ababa, Amman, and Ankara all reside in the northern hemisphere and the eastern hemisphere because their latitude and longitude values are both positive.
Accra resides in the northern hemisphere because its latitude is positive, but it resides in the western hemisphere because its longitude value is negative.
Apia resides in the southern hemisphere and the western hemisphere because its latitude and longitude values are both negative.

Here are the last six rows from the results set.

You can confirm on your own the signed decimal-degree values reflect the hemispheres in which world capitals reside.
You can also confirm that the transformed latitude and longitude decimal-degree values match those in the coordinates field for Washington, D.C. for the original source data by referring to the last screen shot in the preceding section.

Next Steps

You can verify this tip's code by copying it from the code windows. However, the download file for this tip illustrates some additional capabilities, such as how to copy transformed data in degree-minute-second format from two different data sources to a single table. The download file also includes an Excel workbook file with two tabs: one with 89 rows from the city_country_coordinates_dms_w_hemi table and a second tab with 89 rows from city_country_coordinates table.

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.

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