• Compound Operators
• Multiple Value Inserts
• Row Constructor
• MERGE Statement
• Table-Valued Parameters

• Error in Importing/Exporting Geography/Geometry Columns in SQL Server 2008 Management Studio
• SQL Server 2008 to Windows Server 2008 Connection Error 1326
• Drawing a Circle with the Geometry Data Type
• Store Locator Using Geography Data Type
• Convert Latitude/Longitude to Geography Point
• MERGE Statement With Table-Valued Parameters
• SqlConnection Connection Strings
• SQL Server 2008 Date Formats

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 )
GO

Using 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)
GO

As 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)
GO

Unlike 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)) + ')')
GO

The 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.

• What's New in SQL Server 2008
• Drawing a Circle with the Geometry Data Type
• Multiple Value Inserts Using a Single INSERT Statement