MySQL will implement full support for the Open Geospatial Consortium's OpenGIS specification for simple geospatial feature collections. The goal is to be able to claim compliance with OpenGIS. Rationale --------- Although we currently support much of this de-facto standard, in December/2004 I did a comparison of MySQL's GIS support with the OpenGIS specification and found that some of its features have not been implemented. This worklog entry is intended to provide specifications for all GIS features which still need to be implemented. The approach chosen is to write up a worklog entry for each set of tasks; this entry will act as the "parent" entry for the entire set. Compatibility ------------- My analysis of MySQL 5.0's support for spatial features used the following copy of the GIS specification, available from www.opengeospatial.org/, for comparison purposes: * OpenGIS Simple Features Specification For SQL, Revision 1.1 OpenGIS Project Document 99-049; Release Date: May 5, 1999
Background ---------- -- OpenGIS Specification Compliance The Overview for the OpenGIS document says: “The purpose of this specification is to define a standard SQL schema that supports storage, retrieval, query and update of simple geospatial feature collections via the ODBC API. A simple feature is defined by the OpenGIS Abstract specification to have both spatial and non-spatial attributes. Spatial attributes are geometry valued, and simple features are based on 2D geometry with linear interpolation between vertices.” That is, to comply with the OpenGIS specification, a DBMS must: (a) support only 2D geometry via a standard SQL schema; (b) support use of the geometry features via ODBC. In the section on compliance, the same document says: “In order to be compliant with this OpenGIS ODBC/SQL specification for geospatial feature collections, implementers shall choose to implement any one of three alternatives (1a, 1b or 2) described in this specification: 1. SQL92 implementation of feature tables a) using numeric SQL types for geometry storage and ODBC access. b) using binary SQL types for geometry storage and ODBC access. 2. SQL92 with Geometry Types implementation of feature tables supporting both textual and binary ODBC access to geometry. -- Summary MySQL has chosen to comply with the OpenGIS specification via option #2 above: SQL92 with Geometry Types, with access via ODBC as well as other APIs. At this time, MySQL supports only a subset of the required features, so we cannot claim compliance with OpenGIS. Missing Required Features ------------------------- The MySQL implementation is missing the following: 1) The INFORMATION_SCHEMA.GEOMETRY_COLUMNS view; see WL#2378. 2) The INFORMATION_SCHEMA.SPATIAL_REF_SYS view; see WL#2378. 3) The following WKT formats (see WL#2381): -- POINT EMPTY -- LINESTRING EMPTY -- POLYGON EMPTY -- MULTIPOINT EMPTY -- MULTILINESTRING EMPTY -- MULTIPOLYGON EMPTY -- GEOMETRYCOLLECTION EMPTY 4) The Boundary(g) function 5) The ConvexHull(g) function 6) The PointOnSurface(p) and PointOnSurface(mp) functions 7) The Relate(g1,g2,patternMatrix) function Known Problems -------------- The MySQL implementation includes the following features, which have a different name from that expected under the OpenGIS specification: 1) The GeomCollection data type, which MySQL calls GeometryCollection; see WL#2388 2) The Length(ls) function, which MySQL calls Glength(ls) 3) The Length(mls) function, which MySQL calls Glength(mls) 4) The NumInteriorRing(p) function, which MySQL calls NumInteriorRings(p); see WL#2388 Missing Optional Features -------------------------- The OpenGIS specification includes some optional functions, which MySQL has not implemented. Although these functions are not required, we may wish to implement them anyway, for compatibility with other DBMSs: 1) BdMPolyFromText (wkt,srid) 2) BdPolyFromText (wkt,srid) 3) BdMPolyFromWKB (wkb,srid) 4) BdPolyFromWKB (wkb,srid) However, this worklog entry deals only with the required features that MySQL does not support. Other notes: -- MySQL supports a projected (X,Y) coordinate system. OpenGIS also provides support for a geographic (latitude-longitude) coordinate system and a geocentric (X,Y,Z) coordinate system. -- MySQL supports only 2D geometry, not 3D geometry. Although 3D geometry is not mentioned in the OpenGIS specification, it may be part of the ISO GIS Standard. 3D geometry requires support for a geocentric coordinate system. -- The OpenGIS spec says that a Spatial Reference Identifier (SRID) defines the properties of the coordinate system. MySQL both stores and provides the ability to retrieve the SRID of a spatial object, but does not actually use the SRID in any geometric analyses. -- Spatial data can only be used with MyISAM tables. This means that users who need to make transactional spatial data-changes can not use MySQL.