top of page

Geospatial in MySQL 5.7

In MySQL 5.7 and there are nearly thrilling new features now offered that I’m going to discourse in this blog — explicitly about geographic information system (GIS).

I’ve used GIS structures in MySQL for a extensive time. In my earlier blog tickets I’ve exposed how to make geo-enabled requests with MySQL 5.6 and use MySQL 5.6 geo-spatial jobs. In this blog column, I’ll look into what is new-fangled in MySQL 5.7 and how we can custom those new structures in exercise for geo-enabled requests.

New in MySQL 5.7

MySQL 5.7 presents the succeeding main developments and structures for GIS:

Spatial indexes for InnoDB. Finally, it is here! This was a long belated feature, which also barred many corporations from changing all tables to InnoDB.

st_distance_sphere: inherent function to analyze a distance among two points on earth. Finally, it is here.

New purposes: GeoHash and GeoJSON. With GeoJSON we can make the outcomes that are ready for imagining on Google Maps.

New GIS application based on Boost.Geometry library. This is good news, as initially GIS was applied autonomously from scrape with a very incomplete set of features. Manyi Lu from MySQL server side delivers more perceptive behind the high-quality of Boost.Geometry.

This is good news. The immoral news is that apart from for the st_distance_sphere, all other roles use planar geometry (meanwhile MySQL 5.6) and do not provision Spatial Reference System Identifier (SRID). That means that if I want to compute the remoteness of my preferred bike trail in miles or kilometers, I’ll still have to use a deposited function (see beneath for an example) or write an request code for that. Usual function st_distance will disrespect SRID for now and coming back a value which indicates a remoteness on a planar not very useful for our resolves (may be valuable for order by / equate).

Distance on Sphere

MySQL 5.7 presents the function st_distance_sphere, which uses a haversine formula to compute remoteness. He is the example:

The remoteness is in meters by default (you can also alteration the radius of the earth to meters by means of the 3rd non-compulsory parameter, default: 6,370,986). Although our earth is denoted as an oblate spheroid, all applied requests use the remoteness on a sphere. The change between the haversine method and more exact (and much slower) meanings is insignificant for our determinations.

The st_distance_sphere is much quicker than using kept procedures. Here is the non-natural target:

Polygons and Multipolygon

We can also define polygons and multipolygons . Here is the example

ST_Contains(g1, g2)

Returns 1 or 0 to designate whether g1 entirely covers g2. This tests the conflicting connection as ST_Within().

ST_Within(g1, g2)

Returns 1 or 0 to specify whether g1 is spatially within g2. This tests the contrary association as ST_Contains().

27 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


bottom of page