WL#2377: GIS: Add all missing GIS features to MySQL

Affects: Server-6.x   —   Status: Assigned 

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.


Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.