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.
Thursday, May 15, 2008 2:28 AM