Points in bounding box MySql using geospatial types-CodePudding

I’m trying to query SQL to return points that fall within a bounding box. The best answer I have found is this sort of idea:

lng1 <= lng2 AND (lng1 <= lng AND lng <= lng2) /* both edges on same side */
OR
lng1 >  lng2 AND (lng1 <= lng OR  lng <= lng2) /* edges on opposite sides */

This can be seen here: DBfiddle

However I’m using a newer version of MySQL where I am storing my coordinates as “points” type. I’ve been trying to understand if this newer version of MySQL (8) can allow me a better way to select rows with points within a bounding box, but I haven’t had any luck. Does this capability exist?

CodePudding user response：

MySQL can do this easily for simple bounding box, but it does not support antimeridian-crossing boxes, so this will be complicated.

If you did not have any boxes crossing antimedirian, you could simply write expression like

ST_Intersects(
  ST_MakeEnvelope(Point(lng1, lat1), Point(lng2, lat2)), 
  Point(lng, lat))

But since in MySQL ST_MakeEnvelope only supports Cartesian CRS, you'll need to code for antimeridian-crossing boxes yourself, e.g. by constructing two boxes - one from lng1 to 180 and one from -180 to lng2.