Spatial Convert Latitude/Longitude (Lat/Long) to Geography Point

Convert Latitude/Longitude (Lat/Long) to Geography Point

Prior to SQL Server 2008, geography locations such as bank branches, restaurant locations and household locations, are usually stored as latitude and longitude coordinates in two separate columns. With the introduction of spatial data types in SQL Server 2008, particularly the GEOGRAPHY data type, this can now be stored as points in a single column stored as a spatial data object. This article will show the different ways of converting the latitude and longitude coordinates of geography locations into a geography POINT.

The GEOGRAPHY data type supports seven spatial data objects that you can create and work with, namely Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon and GeometryCollection. From these spatial data objects, we will be using the Point.



Let's say we have a table of famous international geographic landmarks where we store its latitude and longitude coordinates and we want to take advantage of SQL Server 2008's new geography data type. (Note: The latitude and longitude coordinates of these landmarks were taken from Google maps and not from Microsoft's Virtual Earth (or Bing Maps).)

CREATE TABLE [dbo].[Landmark] (
[ID] INT IDENTITY(1, 1),
[LandmarkName] VARCHAR(100),
[Location] VARCHAR(50),
[Latitude] FLOAT,
[Longitude] FLOAT
)
GO

INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
GOUsing geography::STPointFromText

The first thing to do is to add the new column where we will store the geography point. This can easily be done by the ALTER TABLE statement and we will use the GEOGRAPHY data type for the new column.

ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] GEOGRAPHY
GO
To convert the latitude and longitude coordinates to a geography point, we will use the StPointFromText function. The STPointFromText returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GOAs you can see from this UPDATE statement, the longitude is specified first before the latitude. The reason is because in the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, the format is (x, y). Geographic coordinates are usually specified by Lat/Long but between these two, the X is the Longitude while the Y is the Latitude.

You may be wondering why the X-coordinate is the Longitude while the Y-coordinate is the Latitude. Think of the equator of the earth as the x-axis while the prime meridian is the Y-axis. Longitude is defined as the distance from the prime meridian along the x-axis (or the equator). Similarly, latitude is defined as the distance from the equator along the Y-axis.

If the latitude and longitude were accidentally interchanged in the parameter for the STPointFromText, you may get the following error message particularly for the Sydney Opera House landmark record:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
Using geography::STGeomFromText

Another way of creating a geography POINT is with the use of the STGeomFromText OGC static method. Just like the STPointFromText OGC static method, the STGeomFromText returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STGeomFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GOUnlike the STPointFromText method, which only accepts a POINT, the STGeomFromText can accept any of the seven spatial data objects (POINT, POLYGON, LINESTRING, MULTIPOINT, MULTIPOLYGON, MULTILINESTRING and GEOMETRYCOLLECTION). If you try to pass any other spatial data object to the STPointFromText aside from a POINT, you will get the following error message:

Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24142: Expected "POINT" at position 1. The input has "LINES".
Using geography::Point

Yet another way of creating a geography POINT spatial data object is with the use of the geography::Point extended static geography method. The geography::Point method constructs a geography instance representing a POINT instance from the latitude and longitude values.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326)
GO
Unlike the STGeomFromText and STPointFromText geography static methods, which accepts as parameter the point coordinates in the Open Geospatial Consortium (OGC) Well-Known Text representation, the Point geography static methods accepts as parameters the Latitude and Longitude coordinates of the point in that order.

Using geography::Parse

Yet another way of creating a geography POINT spatial data object is with the use of the geography::Parse extended static geography method. The geography::Parse static method is similar to the STGeomFromText static method in the sense that it returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Parse('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')')
GOThe only difference between the geography::Parse method and the geography::STGeomFromText method is that in the Parse method, it assumes a spatial reference ID (SRID) of 4326, which is the second parameter of the geography::STGeomFromText method.


Reference link: