Geospatial Data type is specifically designed for geometry oriented objects such as points, lines, polygons etc., It was widely used in location mapping services. In MySQL 5.6 and its lower versions they used decimal datatype for storing point coordinates (latitude, longitude). From MySQL 5.7, geospatial data type was introduced and it improved MySQL performance. Also MySQL 5.7 avoided full table scan.
What’s new in MySQL 8.0?
-
Introduced ellipsoid computation instead of flat plane cartesian.
-
Introduced multiple spatial reference systems.
-
Introduced new functions support for ellipsoid computation.
-
Geographic support is not complete in 8.0.x, since some of the functions that only support Cartesian data.
Coordinate system
We already know the two coordinate systems, flat and spherical. But there are many coordinate systems based on the shape, location of the origin. Hence a point on a surface can have a different meaning based on in which coordinate system it lies. Hence saying P(x,y) is just not enough.
We can see the sample record as below.
mysql> select * from ST_SPATIAL_REFERENCE_SYSTEMS limit 2\G
*************************** 1. row ***************************
SRS_NAME:
SRS_ID: 0
ORGANIZATION: NULL
ORGANIZATION_COORDSYS_ID: NULL
DEFINITION:
DESCRIPTION: NULL
*************************** 2. row ***************************
SRS_NAME: Anguilla 1957 / British West Indies Grid
SRS_ID: 2000
ORGANIZATION: EPSG
ORGANIZATION_COORDSYS_ID: 2000
DEFINITION: PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Anguilla 1957",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6600"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4600"]],PROJECTION["Transverse Mercator",AUTHORITY["EPSG","9807"]],PARAMETER["Latitude of natural origin",0,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",-62,AUTHORITY["EPSG","8802"]],PARAMETER["Scale factor at natural origin",0.9995,AUTHORITY["EPSG","8805"]],PARAMETER["False easting",400000,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["E",EAST],AXIS["N",NORTH],AUTHORITY["EPSG","2000"]]
DESCRIPTION: NULL
2 rows in set (0.00 sec)
In MySQL 5.7, there is no SRID support, By SRID 0 is assumed by default. (means 2D coordinate plane )
Changes in Spatial functions in MySQL 8.0:
Spatial functions are classified into two types:
- Function with prefix “ST_” performs calculation on the geometry.
- Function with prefix “MBR” uses a minimum bounding rectangle (MBR) instead of the exact shape of the geometry.
- Even Though some of the spatial functions which doesn’t have a prefix “ST_” & “MBR” still exists which creates confusion to the users. In order to avoid such confusion, those spatial functions will be deprecated.
- This has lead to change in Spatial function names from MySQL 5.7 to MySQL 8.0.
Here are some of the sample functions which do not support geography yet (means flat coordinate system).
-
ST_Buffer
-
ST_Area
-
ST_Centroid
-
ST_ConvexHull
-
ST_Difference
-
ST_Envelope
-
ST_Intersection
-
ST_IsClosed
-
ST_Simplify
Here are some of the functions newly added in MySQL 8.0.x
- ST_Latitude()
-
ST_Longitude()
- ST_SwapXY()
- ST_Transform()
Let’s see how some of this function works.
ST_Latitude()
This functions returns the latitude value from the given point.
Here POINT(45 90) is co ordinate of point with latitude 45 and longitude 90 and with SRID 4326.
It returns the latitude of the point which is 45.
ST_Longitude()
This functions returns the longitude value from the given point.
It returns the longitude of the point which is 90.
ST_SwapXY()
Gets the geometric data input and swap x and y co ordinates.
ST_Transform()
Gets the geometric data from one SRID type to another SRID type.
Setter functions:
ST_X():
This function returns the x co ordinate to point (x,y)
ST_Y():
This function returns the y co ordinate to point (x,y)
ST_SRID()
This function changes the SRID with out affecting the geometry co ordinates
Comparing Geospatial data type with MySQL & Postgres:
- Postgres is much more stable than MySQL.
-
Lack of functions to support Geospatial data type.
-
Lack of support in 3D and 4D objects.
Conclusion:
Postgres is completely out scored while comparing to spatial data type. MySQL 8.0.x has taken a big step to support ellipsoid geometrical features. But the Geospatial support is not complete yet. Hope we can expect much additional support in the future releases.
