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?

CodePudding user response：

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.