SQL find value between borders

Question

Given a coordinate consisting of lat,lon I want to find the dataset where the value is between the borders (lat1, lat2) and (lon1, lon2).

Here is a fiddle I've created to solve this problem, however I'm wondering if there is no builtin function for this issue?

Answer 1

If you're on MySQL 5.7 or above, you may wanna take a look at Spatial Functions.

More specifically, MBRContains():

MBRContains(g1, g2)

Returns 1 or 0 to indicate whether the minimum bounding rectangle of g1 contains the minimum bounding rectangle of g2.

mysql> SET @g1 = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
mysql> SET @g2 = ST_GeomFromText('Point(1 1)');
mysql> SELECT MBRContains(@g1,@g2), MBRWithin(@g2,@g1);
+----------------------+--------------------+
| MBRContains(@g1,@g2) | MBRWithin(@g2,@g1) |
+----------------------+--------------------+
|                    1 |                  1 |
+----------------------+--------------------+

To cover your specific need what you might do is something like:

SELECT name
FROM test.FOO
WHERE MBRContains(
    ST_GeomFromText(CONCAT("Polygon((", lat1, " ", lon1, ",", lat2, " ", lon1, ",", lat2, " ", lon2, ",", lat1, " ", lon2, ",", lat1, " ", lon1,"))")),
    ST_GeomFromText(CONCAT("Point(", 1.2, " ", 0.2, ")"))
) = 1;

There might be a less messy function to make Polygons and Points, but I'm not sure.