Get center and furthest point of polygon to extend area by radius in mysql

3
votes

Because my other question wasn't successful (How to extend polygon by a certain distance in PHP/Mysql?), I'm thinking to find a simpler solution.

I have a table of places (defined by lat and lng) and a table of locations (polygons stored as geometry). What I need is to search for records in mysql within polygon + certain radius (e.g. 1/4 miles).

There is the function ST_Centroid for getting the center point of polygon but how to get the distance between the center point and the furthest point in order to get extended radius of the circle?

enter image description here

1
mysqlgeolocationpolygongeo

1 Answers

0
votes

Circle is the simplest solution how to extend polygon but better than nothing.

Here is the calculation for getting the furthest point of a polygon. $polygon and $centroid are taken from database (in mysql: ST_AsText(polygon), ST_AsText(ST_Centroid(polygon))) and converted to arrays.

function get_max_point ($polygon,$centroid) {
  foreach ($polygon AS $point) {
    $distance = (sin(deg2rad($centroid['lat'])) * sin(deg2rad($point['lat']))) + (cos(deg2rad($centroid['lat'])) * cos(deg2rad($point['lat'])) * cos(deg2rad($centroid['lng'] - $point['lng'])));
    $distance = acos($distance);
    $distance = rad2deg($distance);
    $distance = $distance * 60 * 1.152;
    if($distance>$distance_max) $distance_max=$distance;
  }
  return (round($distance_max,2));
}