Official Mafiree Blogs

Keeping you informed about Databases

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.

SQL Server On Linux
By Sujith   |   March 30, 2019   |   Posted in : SQL Server
SQL Server 2017 brings the best features of the Microsoft relational database engine to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability/ disaster recovery, and unparalleled data security.

Types of SQL Server Replication
By Sujith   |   February 19, 2018   |   Posted in : SQL Server
Understanding on the types of replication in SQL Server and the type apt for your traffic pattern.

Data File Splitting in SQL Server
By Murali   |   March 15, 2021   |   Posted in : SQL Server
Huge data blocks resides under single MDF file might cause a performance of the query and impact the application services. This blog help you to understand how we can the split the single MDF file into multiple data files.

 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