Collecting, Storing, and Displaying Geographical Coordinates in SQL Server

By: Rick Dobson   |   Updated: 2021-02-22   |   Comments   |   Related: > TSQL

Problem

Geographic coordinates can be useful for tracking the global position of items and in this article we cover how this data can be stored and queried using a SQL Server database.

Solution

Geographic coordinates can be useful for tracking the global position of items. For example, I originally encountered geographic coordinates when I was tracking observations from weather stations within five states to populate a SQL Server weather data warehouse. The current tip focuses on the collection, storing, and displaying 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 a corporation's warehouse and distribution centers.

This tip introduces you to the basics of collecting, storing, and displaying geographic coordinates with SQL Server. 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). However, even with the help of links like these, some SQL Server professionals may be challenged about how to tap SQL Server's geographic analysis features because of a gap between their understanding of the basics for geographic coordinates and the built-in SQL Server features. This tip aims to acquaint SQL Server professionals with the basics of geographic coordinates in the context of an example for collecting, storing, and displaying geographic coordinates. The proper execution of these kinds of steps will facilitate your ability to take advantage of SQL Server's built-in features for handling geographic coordinates data.

A refresher on latitude and longitude values

Latitude and longitude values can be specified with degrees, minutes, and seconds or decimal degrees. Just knowing a latitude and longitude value may not explicitly specify a point on the globe. This is because you need to designate the hemisphere as well as for the latitude and longitude values to designate a point on the globe.

The following image portrays selected latitude values from the north pole through the south poles.

• The equator has a latitude value of 0 degrees. The 0 degrees latitude value for the equator is midway between the north and south poles.
• The latitude values from the equator through the north pole are in the northern hemisphere. Selected countries in this hemisphere include Ethiopia, Switzerland, and Egypt.
• The latitude values from the equator through the south pole are in the southern hemisphere. Selected countries in this hemisphere include New Zealand, Rwanda, and Brazil.
• The north pole has a latitude value of 90 degrees in the northern hemisphere.
• The south pole also has a latitude value of 90 degrees, but it is in the southern hemisphere.
• The image below additionally marks the 45 degrees latitudes in the northern and southern hemispheres.

A longitude extends through half of a great circle. A great circle on the earth's globe divides the globe in half. A prime meridian is a longitude assigned a value of 0 degrees longitude. In October 1884, twenty-five countries adopted the Greenwich meridian (passing through Royal Greenwich Observatory in England) as the prime meridian of the world. The IERS Reference Meridian was widely accepted as the prime meridian starting in 1973. The IERS prime meridian (also called the International Reference Meridian), is 5.3 seconds east of the Greenwich meridian. The IERS meridian is based on a global positioning system that works in coordination with satellites traveling around the world. This Wikipedia article contains a history of prime meridians.

When using geographic coordinates for specifying the location between two or more points on the earth's surface relative to one another, you should use the same prime meridian in specifying the coordinates for all points. This can be achieved by using a single map or a set of maps that consistently references the same prime meridian.

The following image portrays selected longitude values eastward and westward from a prime meridian. The image references the Greenwich meridian as the prime meridian.

• The center spherically shaped arc from north pole through the south pole is the prime meridian. Recall that a meridian is half of a great circle.
• There are up to 180 circles to the right of the prime meridian that are offset successively from each other by 1 degree of longitude through the 180^th degree of longitude, which is sometimes called the antimeridian.
• The longitude values to the right of the prime meridian from 0 through 180 degrees identify the eastern hemisphere. Selected countries in this hemisphere include Germany, Russia, and China.
• There are also up to 180 circles to the left of the prime meridian that are offset successively from each other 1 degree of longitude through the 180^th degree of longitude.
• The longitude values to the left of the prime meridian from 0 through 180 degrees identify the western hemisphere. Selected countries in this hemisphere include Canada, Mexico, and the United States.

Inserting geographical coordinates into a SQL Server table

This tip illustrates how to populate a SQL Server table with jurisdictional names (city name and country name) as well as geographical coordinates (the intersection of latitude and longitude values in the northern/southern and eastern/western hemispheres).

A SQL Server table for storing geographical coordinates

Here is some T-SQL code for creating a fresh copy of the city_country_coordinates table in the WorldCapitalCoordinates database. The city and country column values contain names for human-recognizable areas on the earth's surface. The coordinates values contain geographical coordinates for points on the earth's surface. The city and country columns help to understand where in the world a geographical coordinate points.

use WorldCapitalCoordinates
go
 
-- create a fresh version of the city_country_coordinates table
drop table if exists dbo.city_country_coordinates
 
create table city_country_coordinates (
city nvarchar(50)
,country nvarchar(50)
,coordinates nvarchar(50)
)
go

Sources from which to retrieve geographic data

The data to populate the table created in the preceding code segment is widely available, including at the Wikipedia website. The following image shows an excerpt from a Wikipedia web page with a list of national capitals. The first column on the table from the page has the name City, and the third column has the name Country. The data from these two columns will be used to populate the city and country columns in our SQL Server table. For example, Abu Dhabi is listed as the capital city of the United Arab Emirates. The text for Abu Dhabi is the name for a hyperlink pointing at another Wikipedia web page that includes the geographical coordinates for the city in the United Arab Emirates.

An excerpt from the other Wikipedia web page with the geographical coordinates for Abu Dhabi appears below. The geographical coordinates (24°28'N 54°22'E) are selected in the bottom right corner of the excerpt below.

T-SQL code for transferring geographic data from a source to a SQL Server table

A T-SQL insert into statement with successive city, country, and coordinates values sets copied from the Wikipedia site can populate the city_country_coordinates table in SQL Server. The characters are Unicode characters.

• The first values set in the script below is for Abu Dhabi in the United Arab Emirates. The coordinates value is a string value copied from the preceding web page image with the coordinates value selected.
  • The setting of 24°28′N is the latitude value and its hemisphere designator; N is for the northern hemisphere.
  • The setting of 54°22′E is the longitude value and its hemisphere designator; E is for the eastern hemisphere.
  • The complete string comprised of the latitude and longitude values along with hemisphere indicators comprises the coordinates value for the first city to be inserted.
• The subsequent values rows in the script below are for selected other rows from the "List of national capitals by latitude" table at the Wikipedia site.
  • The insert into statement transfers 89 sets of values to the city_country_coordinates table in SQL Server.
  • A subset of the data associated with the list of national capitals from the Wikipedia website are copied into the following script. This subset allows the tip to present data for four different hemispheres and shows the use of two different coordinates formats.

-- 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')
,('Apia', 'Samoa', '13°50′S 171°45′W')
,('Asmara', 'Eritrea', '15°19′22″N 38°55′30″E')
,('Athens', 'Greece', '37°59′02.3″N 23°43′40.1″E')
,('Bangkok', 'Thailand', '13°45′09″N 100°29′39″E')
,('Basseterre', 'Saint Kitts and Nevis', '17°18′N 62°44′W')
,('Beijing', 'China', '39°54′24″N 116°23′51″E')
,('Belmopan', 'Belize', '17°15′5″N 88°46′1″W')
,('Berlin', 'Germany', '52°31′12″N 13°24′18″E')
,('Bern', 'Switzerland', '46°56′53″N 7°26′51″E')
,('Bissau', 'Guinea-Bissau', '11°51′N 15°34′W')
,('Bogotá', 'Colombia', '4°42′40″N 74°4′20″W')
,('Brasília', 'Brazil', '15°47′38″S 47°52′58″W')
,('Bridgetown', 'Barbados', '13°05′51″N 59°37′00″W')
,('Bucharest', 'Romania', '44°25′57″N 26°6′14″E')
,('Cairo', 'Egypt', '30°2′N 31°14′E')
,('Caracas', 'Venezuela', '10°28′50″N 66°54′13″W')
,('Cayenne', 'French Guiana', '4°56′14″N 52°19′34″W')
,('Conakry', 'Guinea', '9°31′N 13°42′W')
,('Copenhagen', 'Denmark', '55°40′34″N 12°34′06″E')
,('Doha', 'Qatar', '25°17′12″N 51°32′0″E')
,('Dublin', 'Ireland', '53°21′N 6°16′W')
,('Freetown', 'Sierra Leone', '8°29′4″N 13°14′4″W')
,('Gaborone', 'Botswana', '24°39′29″S 25°54′44″E')
,('Hanoi', 'Vietnam', '21°01′42″N 105°51′15″E')
,('Havana', 'Cuba', '23°08′12″N 82°21′32″W')
,('Helsinki', 'Finland', '60°10′15″N 24°56′15″E')
,('Islamabad', 'Pakistan', '33°41′35″N 73°03′50″E')
,('Jakarta', 'Indonesia', '6°12′S 106°49′E')
,('Jerusalem', 'Israel', '31°47′N 35°13′E')
,('Juba', 'South Sudan', '4°51′N 31°36′E')
,('Kathmandu', 'Nepal', '27.7172°N 85.3240°E')
,('Kigali', 'Rwanda', '1°56′38″S 30°3′34″E')
,('Kingston', 'Jamaica', '17°58′17″N 76°47′35″W')
,('Kuwait City', 'Kuwait', '29°22′11″N 47°58′42″E')
,('Kiev', 'Ukraine', '50°27′00″N 30°31′24″E')
,('Lima', 'Peru', '12°03′S 77°02′W')
,('Lisbon', 'Portugal', '38°43′31″N 9°09′00″W')
,('London', 'United Kingdom', '51°30′26″N 0°7′39″W')
,('Madrid', 'Spain', '40°25′N 3°43′W')
,('Managua', 'Nicaragua', '12°8′11″N 86°15′5″W')
,('Manila', 'Philippines', '14°36′N 120°59′E')
,('Maputo', 'Mozambique', '25°58′S 32°35′E')
,('Mexico City', 'Mexico', '19°26′N 99°8′W')
,('Mogadishu', 'Somalia', '02°02′N 45°20′E')
,('Montevideo', 'Uruguay', '34°53′1″S 56°10′55″W')
,('Moscow', 'Russia', '55°45′21″N 37°37′2″E')
,('Muscat', 'Oman', '23°35′20″N 58°24′30″E')
,('Nassau', 'Bahamas', '25°4′N 77°20′W')
,('New Delhi', 'India', '28°36′50″N 77°12′32″E')
,('Niamey', 'Niger', '13°30′42″N 2°7′31″E')
,('Nicosia', 'Cyprus', '35°10′21″N 33°21′54″E')
,('Nuuk', 'Greenland', '64°10′53″N 51°41′39″W')
,('Oslo', 'Norway', '59°54′50″N 10°45′8″E')
,('Ottawa', 'Canada', '45°25′29″N 75°41′42″W')
,('Panama City', 'Panama', '8°59′N 79°31′W')
,('Paris', 'France', '48°51′24″N 2°21′08″E')
,('Phnom Penh', 'Cambodia', '11°34′10″N 104°55′16″E')
,('Port Louis', 'Mauritius', '20°9′52″S 57°30′15″E')
,('Port of Spain', 'Trinidad and Tobago', '10°40′N 61°31′W')
,('Pyongyang', 'North Korea', '39°1′10″N 125°44′17″E')
,('Rabat', 'Morocco', '34°02′N 6°50′W')
,('Riga', 'Latvia', '56°56′56″N 24°6′23″E')
,('Riyadh', 'Saudi Arabia', '24°38′N 46°43′E')
,('Rome', 'Italy', '41°53′N 12°30′E')
,('San José', 'Costa Rica', '9°56′N 84°5′W')
,('San Salvador', 'El Salvador', '13°41′56″N 89°11′29″W')
,('Sarajevo', 'Bosnia and Herzegovina', '43°52′N 18°25′E')
,('Seoul', 'South Korea', '37°33′36″N 126°59′24″E')
,('Singapore', 'Singapore', '1°17′N 103°50′E')
,('St. George''s', 'Grenada', '12°03′N 61°45′W')
,('St. John''s', 'Antigua and Barbuda', '17°07′N 61°51′W')
,('Stockholm', 'Sweden', '59°19′46″N 18°4′7″E')
,('Taipei', 'Taiwan', '25°04′N 121°31′E')
,('Tegucigalpa', 'Honduras', '14°6′N 87°13′W')
,('Tokyo', 'Japan', '35°41′23″N 139°41′32″E')
,('Tunis', 'Tunisia', '36°48′23″N 10°10′54″E')
,('Vaduz', 'Liechtenstein', '47.141°N 9.521°E')
,('Vatican City', 'Vatican City', '41°54′09″N 12°27′09″E')
,('Vienna', 'Austria', '48°12′N 16°22′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')

Formats for displaying geographic data

Geographical data can be exchanged or shared in any of a variety of different formats. It is not uncommon to encounter different formats for geographic data from the same data provider or even within the same dataset. Therefore, your T-SQL code for geographic analysis solutions should account for the possibility of different formats.

Both latitude and longitude values are represented by degrees. There are two different formats for expressing degree values.

• Degree-minute-second is an older more traditional format that is still widely used.
  • There are 180 whole degree latitude values. These 180 whole degree values divide into two sets for the northern and southern hemispheres, respectively.
    • 90 whole degrees from the equator through the north pole are in the northern hemisphere. The first whole degree in the northern hemisphere is 1° from the equator (0°) in the direction of the north pole. The last whole degree in the northern hemisphere is from 89° through the north pole (90°).
    • 90 more whole degrees are in the southern hemisphere from the equator (0°) through the south pole (90°). These whole degrees are distributed similarly to those in the northern hemisphere, except they are in the southern hemisphere.
  • There are 360 whole degree longitude values. These degree values also divide into two sets – namely, for the eastern and western hemispheres, respectively.
    • 180 whole degrees going east from the prime meridian (0°) through the antimeridian (180°) are in the eastern hemisphere.
    • Another 180 whole degrees going west from the prime meridian (0°) through the antimeridian (180°) are in the western hemisphere.
• Latitude and longitude values in a degree-minute-second format use ° to indicate degree values, ‘ to indicate minute values, and " to indicate second values followed by a hemisphere indicator, such as E or W. There are up to 60 minutes in each degree, and up to 60 seconds in each minute.
  • Both the latitude and longitude values for Abu Dhabi in the preceding insert into statement are in degrees and minutes. The latitude value appears as 24°28′N, and the longitude value appears as 54°22′E. The seconds values for Abu Dhabi are missing because its whole seconds value rounds to 0".
  • The coordinates value for the third capital (Addis Ababa in Ethiopia) in the preceding insert into statement does illustrate a common complete format for denoting latitude and longitude values.
    • The latitude designation is 9°1′48″N.
      • Addis Ababa is above the equator, so its hemisphere indicator (N) references the northern hemisphere.
      • The degrees symbol (°) trails the number of degrees (9) for Addis Ababa. You can type the degree symbol (°) in Windows by holding the down the Alt key and typing 0176 in the Numeric pad.
      • The minutes symbol (‘) trails the number of minutes (1) for which Addis Ababa is beyond 9° north of the equator.
      • The second symbol (") trails the number of seconds (48) that Addis Ababa is beyond 9°1′N.
    • The complete coordinates string value for Addis Ababa appears as 9°1′48″N 38°44′24″E. In this representation, the latitude value with its hemisphere designator (9°1′48″N) precedes the longitude value with its hemisphere designator (38°44′24″E). A blank space separates the end of the latitude value from the beginning of the longitude value.
    • When you embrace within an insert into statement a jurisdiction's name that includes a single apostrophe with a beginning and an ending single apostrophe sign (‘), then you need to escape the single apostrophe sign in the jurisdiction's name, so that a name such as St. John's in Antigua and Barbuda becomes St. John''s. However, you do not need to escape a single apostrophe within a coordinates value. I am especially sensitive to this detail because my father was born in Antigua.
• You can also encounter latitude and longitude values in a decimal degree format. This format represents values as whole numbers for the number of degrees associated with a value followed by a period and then a fractional part of a degree in a value beyond the number of whole degrees.
  • The coordinates value for Washington, D.C. represents in a decimal degree format the capital city of the U.S. The coordinates value representation in the preceding insert into statement appears as 38.9101°N 77.0147°W.
    • The latitude value is about 91 hundredths of a degree north of the 38^th degree in the northern hemisphere.
    • The longitude value is about 1 hundredths of a degree west of the 77^th degree in the western hemisphere.
    • Notice there are no symbols denoting minutes (‘) or seconds (") values.
    • The critical issue is that there are no symbols denoting minutes (‘) in the coordinates value. Recall that Abu Dhabi, whose coordinates value appears in a degree-minute-second format, has a minutes (‘) symbol but no seconds (") symbol in its coordinates value. This critical issue (of whether a minutes (‘) symbol is missing) identifies the coordinates as having a decimal degree format.
  • There are two additional coordinates values that are in decimal degree format. Notice that neither of these coordinates values includes a minutes (‘) symbol.
    • Vaduz in Liechtenstein has a coordinates value of 47.141°N 9.521°E.
    • Kathmandu in Nepal has a coordinates value of 27.7172°N 85.3240°E

Filtering geographical coordinates

This section presents two types of scripts for filtering coordinates values. You can filter geographical coordinates with where clause criterion values. Because coordinates values are strings, a where clause criterion can test for the presence of a special string value in the coordinates field with the charindex function.

Find coordinate values in degree-minute-second format versus decimal degree format?

For a coordinate value to be in degree-minute-second format, the field must contain a single apostrophe character to identify minute values. The following script scans the rows in the city_country_coordinates table to return rows with a single apostrophe in the coordinates column. The charindex function is handy for implementing this feature. This function returns a value of 0 if a string field does not contain a character; otherwise, it returns the stating position for the searched string. The script demonstrates how to escape the single apostrophe value with two apostrophes. Because 86 of the 89 coordinates in the table are in degree-minute-second format, the script returns 86 rows.

You can easily run this query in any database of your choice, after running the preceding script to create a fresh version of the city_country_coordinates table and then populating the table with capital city and country names along with the values for the coordinates copied from the Wikipedia website. There is no need to copy the geographic jurisdiction names and coordinates values from the Wikipedia website because these values are embedded in the insert into statement.

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

The next script searches the city_country_coordinates table for coordinates column values in decimal degree format. This script is to return rows without a single apostrophe in the coordinates column values. Therefore, the where clause criterion has a value of 0. As the comments in the script state, there are just three rows with coordinates values in decimal degree format.

-- scan coordinate values to identify coordinates in decimal degree format
-- Kathmandu, Vaduz, and Washington, D.C. have coordinates 
-- without a minute symbol ('), which indicates a decimal degree format
select 
city, country, coordinates
from dbo.city_country_coordinates
where charindex('''',coordinates) = 0

Display results sets for geographic values within hemisphere

There are two pairs of hemispheres in geographical analysis.

• Points in the northern hemisphere are above (or north) of the equator. A point in the northern hemisphere has N in its coordinates value.
• Points in the southern hemisphere are below (or south of) the equator. A point in the southern hemisphere has S in its coordinates value.
• Points in the eastern hemisphere are east (or to the right) of the prime meridian. A point in the eastern hemisphere has E in its coordinates value.
• Points in the western hemisphere are west (or to the left) of the prime meridian. A point in the western hemisphere has W in its coordinate value.

The following pair of queries return rows from the city_country_coordinates table plus one computed column named source coordinates format. The computed column returns a value of ‘degree minute second' for all rows, except those for the cities of Kathmandu, Vaduz, and Washington, D.C. , which have a computed value of ‘decimal degree'. The bottom query returns rows from the southern hemisphere. The criterion value within the where clause at the bottom of each query specifies the rows to be returned from each query.

• A query looks in the northern hemisphere when the criterion (charindex('N',coordinates) <> 0) is true.
• A query looks in the southern hemisphere when the criterion (charindex('S',coordinates) <> 0) is true.

-- northern hemisphere capitals with source coordinates format
select 
 city
,country
,coordinates
,
case
   when city in ('Kathmandu', 'Vaduz', 'Washington, D.C.') 
      then 'decimal degree'
   else 'degree minute second'
end [source coordinates format]
from dbo.city_country_coordinates
where charindex('N',coordinates) <> 0
 
-- southern hemisphere capitals with source coordinates format
select 
 city
,country
,coordinates
,
case
   when city in ('Kathmandu', 'Vaduz', 'Washington, D.C.') 
      then 'decimal degree'
   else 'degree minute second'
end [source coordinates format]
from dbo.city_country_coordinates
where charindex('S',coordinates) <> 0

Here is a view of the first 10 rows from the results sets for each of the preceding query statements.

• All the rows in the top results set, which is for the first of the two preceding query statements, have the letter N in the coordinates column.
• All the rows in the bottom results set, which is for the second of the two preceding query statements, have the letter S in the coordinates column. Notice that there is no scroll bar in the bottom pane for the second results set. This means there are just ten rows returned by the second query statement in the preceding script.

Here is a view of the bottom ten rows from the results sets for the first query statement in the preceding script.

• Just as the results set in the first pane of the preceding screen shot, all the rows in the following results set excerpt have N in their coordinates column.
• These rows are therefore for northern hemisphere cities.
• Because there are 79 rows for the northern hemisphere and another 10 rows for the southern hemisphere the sum of these two row counts is 89. This outcome is a unit test for the results sets from the two queries being mutually exclusive and exhaustive for all the rows in the source table (city_country_coordinates), which also has 89 rows.

The following script shows queries for returning results sets with the capital cities in the eastern and western hemispheres, respectively. The queries in the script are identical except for their where clause criterion values.

• The top query has a criterion value returning cities in the eastern hemisphere.
• The bottom query has a criterion value returning cities in the western hemisphere.

-- eastern hemisphere capitals with source coordinates format
select 
  city
, country
,coordinates
,
case
   when city in ('Kathmandu', 'Vaduz', 'Washington, D.C.') 
      then 'decimal degree'
   else 'degree minute second'
end [source coordinates format]
from dbo.city_country_coordinates
where charindex('E',coordinates) <> 0
 
 
-- western hemisphere capitals with source coordinates format
select 
  city
, country
,coordinates
,
case
   when city in ('Kathmandu', 'Vaduz', 'Washington, D.C.') 
      then 'decimal degree'
   else 'degree minute second'
end [source coordinates format]
from dbo.city_country_coordinates
where charindex('W',coordinates) <> 0

This screen excerpt below returns the top 10 rows for the top and bottom queries in the preceding script. There are two panes in the screen shot.

• The top pane shows the results from the top query in the preceding script.
• The bottom pane shows the results from the bottom query in the preceding script.

The next screen excerpt returns the bottom 10 rows for the top and bottom queries in the preceding script. There are two panes in the screen shot.

• The top pane shows the results from the top query in the preceding script.
• The bottom pane shows the results from the bottom query in the preceding script.

As you can see, the top panes in both the immediately preceding screen shot and the screen shot before that have E in their coordinates values because both panes show results for cities in the eastern hemisphere. In contrast, both bottom panes have W in their coordinate values because these panes show results from the western hemisphere. Also, the counts of the rows in the top and bottom panes are, respectively, 55 and 34. The sum of these two counts is 89, which is the total of the rows in the source dataset. This is a unit test of the validity of the eastern and western hemisphere results.

Next Steps

You can verify this tip's code by copying it from the code windows. Be sure to start with script that creates a fresh copy of the city_country_coordinates table. You can run the queries from the tip within convenient database for you.

If you have a set of geographic coordinates other than those used in this tip, then you can try them with the code.

It is possible to designate hemispheres with positive and negative values. If your test set of coordinates follows this convention, then you will have to tweak the code in this tip to accommodate the positivity or negativity of latitude and longitude values for designating a hemisphere. Sample T-SQL code handling this type of format will appear in a subsequent tip on geographical coordinates.

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