http://www.phparch.com \ May 2019 \ 17MySQL 8.0 Geographic Information System or How Did I Get to This Point?It is easy to check if our data is as we entered it by using ST_
Longitude() and ST_Latitude() to convert it from the internal
binary format to something more readable for humans as
shown in Listing 4.
Listing 4- mysql> SELECT name,
- ST_Latitude(loc) as Lat,
- ST_Longitude(loc) AS Lon
- FROM city;
- +-------------+-----------+------------+
- | name | Lat | Lon |
- +-------------+-----------+------------+
- | Justin | 33.09993 | -97.340499 |
- | Trophy Club | 33.009335 | -97.22672 |
- +-------------+-----------+------------+
- 2 rows in set (0.00 sec)
And now we can use ST_Distance_Sphere() to get the
distance in meters between the two cities.
SELECT ST_Distance_Sphere(
(SELECT loc FROM city WHERE name='Justin'),
(SELECT loc FROM city WHERE name='Trophy Club')
) as Distance_meters;
And, thus, we find the two cities are 14626.
meters apart. Remember, the proper way to convert meters to
miles is to convert it first to furlongs, cubits, and then smoots!
No, that last sentence was a joke, and I am sure the readers
of this fine publication already have their preferred meters to
mile conversion formula or can find one quickly.
However, this query illustrates the power of having your
database do the calculations for you.
Conclusion
This article has barely scratched the surface of what can be
done with GIS such as drawing a polygon around a location,
say a city limit or postal code area, and doing something
really useful such as finding the pizza restaurants inside that
polygon. There is a lot of fascinating stuff in the GIS world
to read up on, or maybe your job is looking for a way to
corner the market on their new “Piggy Back Ride to Pizza as
a Service” business strategy.More to Read
- MySQL 8.0 Spatial Data Types:
https://phpa.me/mysql8-spatial-data - MySQL 8.0 Spatial Analysis Functions:
https://phpa.me/mysql8-spatial-analysis
Dave Stokes started using PHP when it
was known as Personal Home Page and
started working for MySQL AN as a PHP
Developer. He is now a MySQL
Commmunity Manager for Oracle Corpo-
ration. He lives in Texas with the required
hound dog and pickup truck. @stokerRelated Reading
- MySQL Without The SQL—Oh My!
by Dave Stokes. July 2018.
https://phparch.com/article/mysql-without-the-sql-oh-my/ - Think Global, Act Local by Jeremy Cook. June 2012.
https://phparch.com/magazine/2012-2/june/