Serverless, ReactPHP, and Expanding Frontiers, May 2019

http://www.phparch.com \ May 2019 \ 15

MySQL 8.0 Geographic Information System or How Did I Get to This Point?

If you do a SELECT * FROM geom; you
get the g column output in binary. By
the way, the POLYGON entered above
is one POLYGON with the exterior and
interior bounds defined. Thankfully, we
can use ST_AsText() to provide us with
something more readable (see Listing
2).

Geometry Class

A big part of the MySQL 8.0 changes
over previous versions for Graphic
Information System support start with
a catalog of 5,108 spatial reference
systems (SRSs): 4,628 projections (flat maps), 479 geographic
(ellipsoidal) representations of Earth, and one Cartesian
all-purpose abstract plane (SRID 0) for simple X, Y type
charting.

Huh?
Okay, let’s take a step back. That was a lot of numbers, buzz-
words, and initials. Let’s take a look at what they mean.

Each geometry value has two parts—the actual value and
the SRID. Any geometric object—lines, polygons, points,
etcetera—exist in a single spatial reference system (SRS). If
you are comparing those objects, they must all be in the same
spatial reference system, and those are denoted by Spatial
Reference Identification (SRID) number. So any point at X
and Y also has an SRID. MySQL and other databases turn up
their noses at your request to work with different SRIDs on
the same query so you can not mix elements from a curved
Earth model and a flat geometry model.

The default SRID is 0 (zero) for a flat, Cartesian grid (think
simple X, Y line graphs) but there are others for Mercator
projections, World Geodetic System, and notably the Euro-
pean Petroleum Survey Group (EPSG) which is the de facto
standard. Yes, each one of them has their use case. Most often
you see the flat Cartesian or the EPSG SRIDs in everyday use.

There are many popular SRSs such as SRID 4326 for GPS
coordinates. Moreover, you have probably used SRID 3857
with Google Maps, OpenStreetMap, and other web maps.
Paper maps are probably in one of the 4,627 other projected
SRSs defined in MySQL 8.0, and the ability to handle multiple
SRSs is a big deal. So if you need to take a longitude and lati-
tude from a paper map to a three-dimensional model, it is
much easier to let your database handle the calculations than
trying to do the math by hand.

But How Do I Know Where I Am?

So with all this new knowledge about SRIDs, points, and all

that, you are probably wondering “How can I use that knowl-

edge?” Or, you are like me and travel a lot and wonder how

does my iPhone know how far it is to the nearest restaurant?

The answer is the magic in GIS.

We need another table to hold information about various

cities.

mysql> CREATE TABLE city (

id INTEGER AUTO_INCREMENT PRIMARY KEY,

name CHAR(20) NOT NULL,

loc POINT SRID 4326 NOT NULL

);

Query OK, 0 rows affected (0.04 sec)

Note that we are using good ol’ SRID 4326 for POINT data

and are storing longitude and latitude data. There are two

cities in Texas named Justin and Trophy Club that are rela-

tively close but how can we find out just how close they are?

Well, first we need our Longitude and Latitude data for these

two municipalities. The data for these two data points I pulled

from a freely available dataset on the internet but the data

here is for illustration and not for accuracy. There are many

similar datasets to be found with a simple search or use Wiki-

pedia for the latitude and longitude for two locations of your

choice. See Listing 3.

Listing 2

1. SELECT type, st_astext(g) FROM geom;


2. 
   


3. +------------+----------------------------------------------------------+


4. | type | st_astext(g) |


5. +------------+----------------------------------------------------------+


6. | point | POINT(1 1) |


7. | linestring | LINESTRING(0 0,1 1,2 2) |


8. | polygon | POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5)) |


9. +------------+----------------------------------------------------------+


10. 
    


11. 3 rows in set (0.00 sec)

Listing 3

1. INSERT INTO city (name,loc)


2. VALUES ('Justin',


3. ST_GeomFromText('point(33.09993 -97.340499)',4326)


4. );


5. 
   


6. INSERT INTO city (name,loc)


7. VALUES ('Trophy Club',


8. ST_GeomFromText('point(33.009335 -97.22672)',4326)


9. );