MSSQL 2008: Spatial Data Types - Geography and Geometry

In addition to new DATETIME data types introduced in MSSQL 2008 there are geography and geometry data types introduced - useful for storing and manipulating geodetic data. Geometry data type is used to store flat map data like points, lines, and shapes. Geography data type takes account of the curvature of the Earth in addition. Both provide the ability to perform spatial operations on geography instances. Geography and Geometry are likely to be supported in MSSQL 2008 Express versions.

"The ability to use the geography data type in a Microsoft SQL Server database enables you to extend business applications to handle geospatial data that describes simple and complex geographic elements on the surface of the Earth."

Geography data type has functions which support parsing back from the known geo formats supported by the OGC (Open Geospatial Consortium) like text formats, binary representations, GML (Geography Markup Language - XML for geo data).

Small example of spatial operations - let's assume we need to find the area (i.e. in square meters) of the stored polygons (SalesRegion of SalesPerson table). The query:

SELECT SalesRegionName, SalesRegion.STArea()
  FROM SalesPerson

Other supported functions include STIntersects(), STUnion() for polygons and STDistance() for points.

To boost performance one can CREATE SPATIAL INDEX for the data of GEOGRAPHY or GEOMETRY column.

Enjoy :)

Related Posts:

• MSSQL 2008: What is DATETIME2
• T-SQL: LIKE Operator and Wildcard Characters
• MSSQL: Search and Replace in a TEXT(NTEXT) column
• T-SQL: Get Age from Date of Birth
• MSSQL 2005 Tip: Using TOP with Subquery
• T-SQL Query Processing Order
• How to Insert DBCC Output into MSSQL Table
• MSSQL: sp_xml_preparedocument is not supported in fibers mode
• MSSQL: Got arithmetic overflow for INT ?
• MSSQL 2005 Express Edition - unbeatable