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().