Inside Data 59
by Graham Keitch
Graham Keitch takes a closer look at spatial support in Oracle MySQL and Microsoft SQL Server.
HardCopy Issue: 59 | Published: February 1, 2013
Geographic information is important for businesses that need more efficient ways to handle logistics, communications and processes that involve scientific data such as weather or demographic information. Database provider Oracle has been a leading innovator of technologies that support spatial data types, as we discussed in our February 2011 issue (page 11). Oracle Database first supported spatial data as long ago as 1984. With the acquisition of Sun Microsystems in 2010, Oracle became the new owner of the open source MySQL database which has been equipped to handle spatial data since 2004 and continues to be enhanced in this area. Microsoft added spatial support in SQL Server 2008 and this has been extended with the new 2012 release.
Let’s start with a recap concerning Oracle Database 11g. Oracle Locator handles location data as a standard feature in the free Oracle Database Express Edition (XE) and across both Standard and Enterprise editions of the 11g database range. Developers can use Locator to build geographic information into their systems and extend existing Oracle tools and applications. Projects that involve complex GIS and location-based solutions need Oracle Database Enterprise Edition with the separately licensed Spatial Option.
Oracle MySQL is a cross platform database management system that has a number of different storage engine options. Version 4.1 first provided spatial tools for MyISAM tables only while version 5 extended this to bring in other storage types including the more sophisticated default InnoDB engine. In common with other providers, MySQL implements spatial extensions that follow the specification of the Open Geospatial Consortium (OGC). This describes a set of SQL geometry types and functions that allow you to create and analyse geometric data.
MySQL implements a subset of the OGC ‘SQL with Geometry Types’ specification which defines a SQL environment that has been extended with a set of geometry types. Some of these hold single geometry values such as Point and LineString. Others hold collections of values such as MultiPoint and MultiLineString which restrict collection members to those with a particular geometry type. A collection of objects of any type is catered for by GeometryCollection.
To put things into practice you create spatial values using Well-Known Text and Well-Known Binary functions that take either a text representation or binary large object (BLOB) as arguments and return the corresponding geometry. In both cases, an optional spatial reference system identifier (SRID) can be added to the equation. A geometry capable SQL column is implemented as a column that has a geometry type.
The Microsoft story
For Microsoft, the story begins with SQL Server 2008 which introduced support for a wide variety of spatial objects but didn’t include everything in the OGC standard. Features such as circular arc support had to wait for the 2012 release which introduced the CircularString, CompoundCurve and CurvePolygon data types. These facilitate a simpler and more accurate way of representing an arc than the previous vector technique.
SQL Server 2012 also introduced full global results whereas 2008 could only return search results for a single hemisphere. Other valuable enhancements include Spatial Index which allows you to narrow searches to likely candidates. Auto-grid and compression technologies are now included to improve search efficiency and 48-bit precision can be used for more accurate calculations and renditions.
The geometry and geography data types are implemented as .NET common language runtime (CLR) data types in SQL Server. Geometry represents data in a flat earth coordinate system while geography represents data using a round earth model. A number of features that were previously only available as ‘community’ add-ons, such as aggregate methods on geometry and geography classes, are now built into SQL Server 2012.
Aggregate methods allow you to work with multiple shapes to create a union or a collection and define a single envelope that encapsulates them. The shapes could be countries or other territorial boundaries as might be defined by a sales operation. A shortest path method allows you to establish the shortest route to assist with more efficient logistics. Previously, there were fewer methods available to geography objects than geometry but both are now treated more equally. The spatial tools are available across all editions of SQL Server 2012, including Cloud deployment (previously SQL Azure).
For Microsoft Visual Studio 2012 developers, spatial support became available with the release of Entity Framework (EF) 5 and .NET 4.5. EF Spatial provides developers with the ability to build location-aware applications that save and retrieve location data. Cloud and Web developers can also benefit from these spatial features now that Windows Azure Web Sites supports .NET 4.5.