GeoSpatial data handling made easy with MySQL 8.0

May 4, 2020

Mins to Read

All

Human geography examines the dynamics of cultures, societies and economies, and physical geography examines the dynamics of physical landscapes and the environment.

In this blog i am going to explain what are the new Geography features available in MySQL 8.0

Major Difference in MySQL 5.7 & 8.0 Geography :

There is a big change from MySQL 5.7 to 8.0 when it comes to spatial data, it’s support, multiple spatial reference systems and geographic computations.

This means that the SRIDs of geometries actually have meaning and affect computations. In 5.7 and earlier, however, the SRIDs(spatial reference identifier) are ignored, and all computations are Cartesian.

What’s new in MySQL 8.0?

* Introduced ellipsoid computation instead of flat plane cartesian.

* Introduced multiple spatial reference systems.

–> 4326 = WGS 84 (Google Earth is in a Geographic coordinate system)
–> 3857 = WGS 84 / Pseudo-Mercator , Google Maps, Bing (Google Maps is in a projected coordinate system that is based on the wgs84 )

Introduced new functions support for ellipsoid computation.

ST_Latitude()
ST_Longitude()
ST_SwapXY()
ST_Transform()

Geographic support is not complete in MySQL 8.0.x, since some of the functions that only support Cartesian data.

What is SRID ?

SRID is a numerical ID that refers to a Spatial coordinates to the Earth’s coordinates.
By default, MySQL uses SRID 0, which represents an “infinite flat Cartesian plane with no units assigned to its axes”.

Pictorial Representation

The World Geodetic System (WGS) is a standard for use in cartography, geodesy, and satellite navigation including GPS.

It comprises a standard coordinate system for the Earth, a standard spheroidal reference surface.

ST_Latitude() :

Latitude defines how far north (positive) or south (negative) of the equator a point is located.

The north pole has a latitude of 90, and the south pole has a latitude of -90.

So, that’s why all northern hemisphere locations have positive latitude and southern-hemisphere locations have negative values.

Example :

 
mysql> set @pt = ST_GeomFromText('POINT(12.9716 77.5946)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_Latitude(@pt);
+------------------+
| ST_Latitude(@pt) |
+------------------+
|          12.9716 |
+------------------+
1 row in set (0.00 sec)

This functions returns the latitude value from the given point.

Here POINT(12.9716 77.5946) is co ordinate of point with latitude 12.9716 and longitude 77.5946 and with SRID 4326.

ST_Longitude() :

The Longitude of a location on the globe is, the number of degrees it is from the prime meridian (when measured along the equator). Both Latitude and Longitude are measured from the center of the Earth.

Example :

 
mysql> set @pt = ST_GeomFromText('POINT(12.9716 77.5946)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_Longitude(@pt);
+-------------------+
| ST_Longitude(@pt) |
+-------------------+
|           77.5946 |
+-------------------+
1 row in set (0.00 sec)

ST_SwapXY:

The function ST_SwapXY which swaps the X and Y values of each coordinate pair within the geometry, and returns the result.

Example :

 
mysql> set @pt = ST_GeomFromText('POINT(12.9716 77.5946)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_AsText(ST_SwapXY(@pt));
+---------------------------+
| ST_AsText(ST_SwapXY(@pt)) |
+---------------------------+
| POINT(77.5946 12.9716)    |
+---------------------------+
1 row in set (0.00 sec)

ST_Transform() :

Gets the geometric data from one SRID type to another SRID type.The return value is a geometry of the same type as the input geometry with all coordinates transformed to the target SRID.

Example :

 
mysql>  set @pt = ST_GeomFromText('POINT(12.9716 77.5946)', 4230);
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_AsText(@pt);
+------------------------+
| ST_AsText(@pt)         |
+------------------------+
| POINT(12.9716 77.5946) |
+------------------------+
1 row in set (0.00 sec)

mysql> set @pt = ST_Transform(@pt, 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_AsText(@pt);
+---------------------------------------------+
| ST_AsText(@pt)                              |
+---------------------------------------------+
| POINT(12.971068576768081 77.59539259525056) |
+---------------------------------------------+
1 row in set (0.01 sec)

How to Calculate Distance ?

So let’s use ST_Distance_Sphere to calculate the distance between the Chennai Airpot to Bangalore Airpot.

I get the latitude and longitude values from google map.

Chennai – 12.9941° N, 80.1709° E

Bangalore – 13.1986° N, 77.7066° E

Let’s see how to calculate distance using ST_Distance_Sphere.

Image :

The distance between these two places is 268 KM or 2,67,151 meters.

Example :

 
mysql> SET @chennai := ST_GeomFromText( 'POINT(12.9941 80.1709)', 4326, 'axis-order=lat-long' );
Query OK, 0 rows affected (0.00 sec)

mysql> SET @banglore := ST_GeomFromText( 'POINT(13.1986 77.7066)', 4326 );
Query OK, 0 rows affected (0.00 sec)

mysql> select ST_Latitude( @chennai ) AS lat_chennai,ST_Longitude( @chennai ) as long_chennai, ST_Latitude( @banglore ) as lat_banglore,ST_Longitude( @banglore ) as long_banglore,ST_Distance_Sphere( @chennai, @banglore ) as distance_in_m;
+-------------+--------------+--------------+---------------+--------------------+
| lat_chennai | long_chennai | lat_banglore | long_banglore | distance_in_m         |
+-------------+--------------+--------------+---------------+--------------------+
|     12.9941 |      80.1709 |      13.1986 |       77.7066 | 267856.67865603406 |
+-------------+--------------+--------------+---------------+--------------------+
1 row in set (0.00 sec)

From the above result, we can see that Google and we are not that far off.

Google maps measured a distance of 268 KM while we measured it to be around 267.86 KM (difference of less than 200 M).

we have used options argument of ST_GeomFromText function to explicitly provide information about the axis order of POINT geometry in WKT.

Also, we can use ST_Latitude and ST_Longitude to get latitude and longitude value geometry types of 4326 SRID.

Conclusion :

Working with geo-locations in MySQL has improved greatly in version 8. Currently 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 of MySQL.

References :

1) https://dev.mysql.com/doc/refman/8.0/en/gis-point-property-functions.html#function_st-latitude

2) https://dev.mysql.com/doc/refman/8.0/en/gis-format-conversion-functions.html#function_st-swapxy

3) https://dev.mysql.com/doc/refman/8.0/en/spatial-operator-functions.html#function_st-transform

‍

No items found.