Filter items that belongs to a Google Maps boundary with MySQL 5.7

filter-items-google-maps-boundary-mysql.md

Filter items that belongs to a Google Maps boundary with MySQL 5.7

Having MySQL >5.7 with a table that contains a column type POINT named location and the following Google Maps response:

"geometry": {
    "bounds": {
        "northeast": {
            "lat": 40.5638447,
            "lng": -3.5249115
        },
        "southwest": {
            "lat": 40.3120639,
            "lng": -3.8341618
        }
    },
    //....

you can perform a SQL query to retrieve all your locations contains in that boundary like this:

SELECT * FROM my_table
WHERE Contains(
  ST_MakeEnvelope(
    ST_GeomFromText('POINT(40.5638447 -3.5249115)'),
    ST_GeomFromText('POINT(40.3120639 -3.8341618)')
  ),
  location
);

Consider to index location to improve the performance of your queries if apply. Also, is possible to use Within instead of Contains changing the order of the parameters.

Useful links:

• https://dev.mysql.com/doc/refman/5.7/en/spatial-type-overview.html
• https://dev.mysql.com/doc/refman/5.7/en/gis-general-property-functions.html
• https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-mbr.html
• https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-makeenvelope
• https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html