Official Mafiree Blogs

Keeping you informed about Databases

Scaling Databases Horizontally: A Solution for High-Concurrency Systems
By Sukan   |   September 20, 2023   |   Posted in : TiDB
Blog post on how TiDB handles the OLAP requests without compromising OLTP

6 Interesting Features In MongoDB 6.0
By Thivakar   |   June 19, 2023   |   Posted in : MongoDB
Upgrade to MongoDB 6.0 for improved index management, error handling, powerful aggregation, replicaset enhancements, time series collection improvements, and embedded field exclusion. Experience efficient database management.

Access Control & Account Management In MySQL 8
By Sukan   |   June 08, 2023   |   Posted in : MySQL
Discover how MySQL 8 enhances security through features like password expiry, password validation, account lock/unlock, privilege management, and dual passwords. Safeguard your valuable data and streamline access control in your MySQL environment.

Significance of using Invisible Primary key (GIPK) with MySQL 8.0
By Sukan   |   February 21, 2023   |   Posted in : MySQL
How Generated Invisible Primary Keys (GIPK) Can Boost Your Database Efficiency

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

 Home  /  MySQL  
Geospatial Data type in MySQL 8.0
By Jeyaprakash   |   August 02, 2019   |   Posted in : MySQL

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 need an identifier to conclude the point comes in which location. It can be identified by using SRID or Coordinate Reference System Identifier. It is a unique integer associated with a coordinate system proposed by Open Geospatial Consortium(OGC).
 
MySQL 8.0.12 have 5152 SRID which can be checked from INFORMATION_SCHEMA. ST_SPATIAL_REFERENCE_SYSTEMS table.

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 few example functions which is modified.

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.

If you want to know more about Geospatial in Postgres, I would suggest to read my colleague Sujith's blog Geospatial Database :: PostGIS Basics and Beyond



0 Comments


Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree