0

So searching for a point within a polygon is quite simple in mysql, doing something like this:

SELECT * FROM table WHERE ST_CONTAINS(ST_GEOMFROMTEXT('POLYGON((-72.0429540 42.9949644, -72.0405293 42.9929398, -72.0401645 42.9905541, -72.0353365 42.9888747, -72.0326543 42.9897850, -72.0343494 42.9935990, -72.0344567 42.9969733, -72.0367098 42.9980562, -72.0417953 42.9970361, -72.0429540 42.9949644))'), POINT(lat, long))

But let's say I wanted to also find items that are within 1 mile of the border of my polygon. Is there a simple way to this in mysql? My other line of thinking was that maybe I can somehow calculate new points for my polygon, but I'm not sure how I would go about knowing the direction to expand in.

clearmend
  • 146
  • 1
  • 10
  • This should help you recalculate the longitude and latitude at the wider border https://gis.stackexchange.com/questions/142326/calculating-longitude-length-in-miles – Lew Perren Nov 15 '17 at 22:55
  • @BusinessPlanQuickBuilder - seems like that would involve rounded areas at the 'corners'. That is, one cannot simply move the coordinates of the corners. – Rick James Nov 16 '17 at 22:20

1 Answers1

0

I think I found my own answer digging through the MySQL docs. Instead of expanding the polygon, I can query the shortest distance (in degrees) between two geometries with ST_Distance like so:

SELECT * FROM table WHERE ST_DISTANCE(ST_GEOMFROMTEXT('POLYGON((-72.0429540 42.9949644, -72.0405293 42.9929398, -72.0401645 42.9905541, -72.0353365 42.9888747, -72.0326543 42.9897850, -72.0343494 42.9935990, -72.0344567 42.9969733, -72.0367098 42.9980562, -72.0417953 42.9970361, -72.0429540 42.9949644))'), ST_GEOMFROMTEXT(CONCAT('POINT(',lat,' ',long,')'))) < 50;

https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-object-shapes.html#function_st-distance

clearmend
  • 146
  • 1
  • 10
  • Those look like lat/lng? In which case, you need `ST_Distance_Sphere()` -- https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html – Rick James Nov 18 '17 at 14:52
  • @RickJames It is lat/lng, I tried `st_distance_sphere` but it does not appear to support polygons, only points. `st_distance` returns the distance in coordinates, so it works absolutely fine :) – clearmend Nov 23 '17 at 11:49