Our Blog

Spatial queries with MySQL

By Juan Pablo Ventoso
March 24, 2021

Photo by Francois Powell, CC BY 2.0, cropped

MySQL is one of the most widely used relational databases. Most PHP websites rely on MySQL for persisting their information, which makes it one of the DB-Engines top four most popular databases along with Oracle, SQL Server, and PostgreSQL.

One of its capabilities that is not very well known is that the engine supports working with spatial data, allowing you to save different shapes (points, lines, polygons) and querying information based on intersections, distances, or overlaps. This capability was included in MySQL a long time ago, but it became easier to use starting in version 5.6, when the distance and point intersection functions were added.

Spatial data can be useful for many needs, including:

• Searching for places based on latitude/​longitude coordinates
• Displaying information and areas as layers on maps
• Architecture or home design applications

My first experience with spatial queries was for a weather website I developed that displays local alerts/​warnings on a map using MySQL spatial functions to return active weather alerts for a given location, or to inform if lightning has been observed near the user’s current coordinates. So far, MySQL has given me all the resources I need to do such operations with relatively good performance and without needing to write lots of custom code.

Adding spatial information

There are many resources available to import spatial information into our database. From the United States Census Bureau we can find a set of shapefiles with all US states and counties. The Back4App social database platform also has many datasets available to download for free.

Of course, we can also create a table ourselves that contains any kind of spatial information. In the example below, we will create a table named restaurants which will have a name and location (lat/long) geometry for each row.

CREATE TABLE restaurants (
  name VARCHAR(100),
  location GEOMETRY NOT NULL,
  SPATIAL INDEX(location)
);

Note that we are adding an index of type SPATIAL to the location field. This is mainly for performance reasons. Once the table is created, we can populate it with some data using the ST_GeomFromText() function that will convert a string representation of any shape into a geometry:

INSERT INTO restaurants VALUES ('Restaurant 1', ST_GeomFromText('POINT(-26.66115 40.95858)'));
INSERT INTO restaurants VALUES ('Restaurant 2', ST_GeomFromText('POINT(-26.68685 40.93992)'));
INSERT INTO restaurants VALUES ('Restaurant 3', ST_GeomFromText('POINT(-31.11924 42.39557)'));

Querying and filtering with spatial data

Let’s suppose we imported a list of US states from the website above, along with a shape field that holds the geometry associated with each state into a states table. Then, we will be able to get the geometry by just running a SELECT statement:

SELECT state_name, ST_AsText(shape) FROM states ORDER BY state_name;

*-----------------------------*
| state_name | shape          |
*-----------------------------*
| Alabama    | POLYGON((...)) |
| Alaska     | POLYGON((...)) |
| Arizona    | POLYGON((...)) |
| ...                         |
*-----------------------------*

The ST_AsText() function will convert the shape contents into a string representation for us to read and parse, doing the inverse process of the ST_GeomFromText() function we used above. In our application, we can then parse that string and process it the way we need.

Intersecting

For example, we can get a list of states that intersects with a given shape. To do that, we will use the ST_Intersects() function, which will return a boolean value indicating if two shapes intersect or not.

SELECT s.state_name FROM states s WHERE ST_Intersects(s.shape, ST_GeomFromText(?)) ORDER BY s.state_name;

We can define our geometry to be any shape we need. For example, if we want to know which state contains a given point, we can set our parameter to something like 'POINT(40.69 -74.25)' which should return the state of New York.

If we have a polygon, we can set the parameter to specify a polygon shape, i.e. 'POLYGON((40.69 -74.25, 41.10 -74.25, 41.10 -76.11, 40.69 -76.11, 40.69 -74.25))' will mean a rectangle that intersects the states of New York and Pennsylvania. In that case, our query will return two rows with both state names.

Spherical distance

Let’s get back to our restaurants table. If we want to get a list of restaurants that are close to our location, we can use the ST_Distance_Sphere() function that will return a distance in meters between two points in a sphere (defaulting to Earth’s radius):

SELECT * FROM restaurants WHERE ST_Distance_Sphere(location, ST_GeomFromText(?)) <= 10 * 1000 ORDER BY name;

This query will give us a list of restaurants, sorted by name, that are within 10 kilometers from the point we pass as a parameter. We should set the parameter as 'POINT(lat long)' where lat and long represent our current geolocation.

We can use the distance function for many other purposes, like finding people that are close to each other or planning a road trip for the user.

Conclusion

We can avoid writing our own routines to handle spatial data by using MySQL’s built-in spatial types and methods. For most websites, MySQL offers a set of functions that will provide support for most common scenarios. And starting with version 8.0 MySQL includes extended support for geographic and ellipsoid computations.

We should consider that, if we need a complex solution for a robust spatial-based enterprise application, there are alternatives like PostgreSQL’s PostGIS that might be better suited to our purpose. MySQL lacks some advanced features like transformations, custom topology handling, or BRIN indexes, that might have an impact on our development process, depending on our needs.

The image below shows several weather alerts rendered on a Leaflet.js map that were fetched from a field of type GEOMETRY in MySQL:

Resources

There are many other functions we can use to work with our spatial data, from getting the centroid associated to a shape in order to automatically center a map view, to simplifying/​optimizing existing geometry. I recommend reviewing the following links to start trying things out:

• MySQL spatial function reference (8.0)
• Supported geometry (based on OpenGIS model) (8.0)
• GeoJSON support functions

mysql database gis leaflet