MySQL Bugs: #83915: Support Spatial Reference Systems (SRS) in Spatial data types

Bug #83915	Support Spatial Reference Systems (SRS) in Spatial data types
Submitted:	22 Nov 2016 8:47	Modified:	12 Feb 2018 14:16
Reporter:	Nicolai Plum	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	gis, spatial reference system, srid, srs

Description:
MySQL geospatial functions do not support any Spatial Reference Systems. For example ST_DIstance_Sphere assumes a uniform sphere, which is not a good match to the actual geoid of the Earth. They also do not support any set of SRID (Spatial Reference System Identifiers). In the discussion below, GEOMETRY refers to all of GEOMETRY, POINT, LINESTRING, POLYGON, etc. All the other ST_ functions do not support any SRID at all, and therefore don’t return distance, area, etc but only geometric measures (angles, solid angles, etc).

We would like MySQL to have support for:

Choosing an SRS for a column of a GEOMETRY. For example:

CREATE TABLE geom (g GEOMETRY SRID “4326”) -- Use EPSG number 4325 (WGS84) SRS in this table
Supporting a symbolic name for the SRS would also be useful, stored internally as the EPSG. Currently the data is stored assuming a planar geometry, so the default should probably be a planar geometry. At least one spherical and the planar geometry should be supported.

Transforming a GEOMETRY from one SRS to another, as an explicit conversion function or implicitly on inserting into a GEOMETRY column.  For example:

SELECT ST_Convert(“OSGB36”, geom.g) -- display in British OSGB36 datum

ST_Area(), ST_Within(), and similar functions should give correct results around the 180th meridian (most likely by supporting SRS attributes of the parameters but we don’t mind how it is implemented). ST_Area should give an result of area in distance units if an SRS is provided, instead of the current solid angle result.

Spatial indexes on Geometry columns which are NULLable. Currently MySQL does not support this.

In general we would like closer conformance to the OpenGIS standards http://www.opengeospatial.org/standards/sfa
http://www.opengeospatial.org/standards/sfs

Loading and storing SRSs, with some basic SRSs supplied (WGS84 and other major SRSs)

Use cases:

We store geographic places of interest as points and areas (polygons). Currently we store longitude and latitude of points and of polygon boundaries and are forced to do expensive calculations to find all the points in an area. 

We would like to be able to store these places of interest and areas as geospatial columns and use native MySQL queries to compare and process them.

We have multiple sources of geographic data so being able to load and display data with reference to multiple SRS systems would make handling these much easier. 

We need conversion from geometry (latitude and longitude) to distance at any scale to enable us to calculate distances from coordinates. When calculating over large areas (country regions and larger) we need support for a sphere or geoid, because the planar approximation to the Earth’s surface is too inaccurate. 

How to repeat:
This is a feature request, it is not reproducible. 

Suggested fix:
See description for the features that we are requesting.

Hello Nicolai Plum,

Thank you for a reasonable feature request

Thanks,
Umesh

Related:
Bug #7109 	no way to provide srid in creation of geometries

Posted by developer:
 
Hi Nicolai,

I'm closing this as fixed in 8.0.4. There's quite a lot in this FR, but most of it is covered in that release. As you can see below, I've filed a couple of new FRs for items that haven't been completed yet.

SRID type modifier for geometry columns (https://dev.mysql.com/doc/refman/8.0/en/spatial-type-overview.html), e.g. "CREATE TABLE t1 (g GEOMETRY SRID 4326)". Note that the SRID type modifier should be used on all indexed (geometry) columns, otherwise the optimizer can't use them (due to risk of mixing SRIDs in the same index).

Transforming geometries from one SRID to another: Not implemented but duplicate of bug#88871.

Geographic ST_Area: Not implemented. I've filed bug#89591 to track this.

Geographic ST_Within, ST_Overlaps, etc.: Supported since 8.0.3. Works out of the box if the geometry has an SRID that refers to a geographic SRS.

R-tree indexes over nullable columns: Not implemented. I've field bug#89592 to track this.

Loading and storing SRSs: Added in 8.0.4 (https://dev.mysql.com/doc/refman/8.0/en/create-spatial-reference-system.html and https://dev.mysql.com/doc/refman/8.0/en/drop-spatial-reference-system.html). All 2d geographic and projected SRSs from the EPSG Dataset 9.2 have been installed by default. See INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS for details.

I think that covers the entire wishlist. Enjoy! :-)