Two closest points on boundary of Postgis geometry

Question

I have a table geofences which stores geometry of polygon.

I also have a point A which is inside the geometry. What I have to do is find the two closest points from point A that lie on the surface of the polygon geometry.

Function in PostGIS:

CREATE OR REPLACE FUNCTION accuracyCheck(Polygon geometry
                                        ,decimal lat
                                        ,decimal lon)
  RETURNS VARCHAR AS
$BODY$

DECLARE height DECIMAL;
DECLARE accuracy VARCHAR(250);

BEGIN 

CREATE TEMPORARY TABLE closePointStorage AS
SELECT ST_AsText(ST_ClosestPoint(geometry
                                ,ST_GeomFromText('POINT(lat lon)',0)
                                )
                ) AS closestPoint
FROM  (
   SELECT ST_GeomFromText(geometry) as geometry
   FROM gfe_geofences
   WHERE is_active=true
   ) As tempName;

CREATE TEMPORARY TABLE areaStorage ON COMMIT DROP AS
SELECT ST_Area(ST_GeomFromText('Polygon((23.0808622876029 96.1304006624291
                                        ,28.0808622876029 99.1304006624291
                                        ,100              200
                                        ,23.0808622876029 96.1304006624291
                                        ))'
                              ,0)
              ) AS area;

CREATE TEMPORARY TABLE distanceStorage ON COMMIT DROP AS
SELECT ST_Distance(
          ST_GeomFromText('POINT(23.0808622876029 96.1304006624291)',-1)
         ,ST_GeomFromText('POINT(28.0808622876029 99.1304006624291)',-1)
         ) AS distance;

height = (SELECT area FROM areaStorage)
        /(0.5*(SELECT distance FROM distanceStorage));

IF height < (SELECT radius_meters
             FROM gfe_geofences Where is_active=true) THEN
   accuracy = "FullConfirm";
   RETURN accuracy;
ELSE
   accuracy = "PartiallyConfirm";
   RETURN accuracy;
END IF;

END;
$BODY$ LANGUAGE plpgsql;

I just want to find two points on boundary of polygon geometry. Just like I have found one from the query:

CREATE TEMPORARY TABLE closePointStorage AS
SELECT ST_AsText(ST_ClosestPoint(geometry
                                ,ST_GeomFromText('POINT(lat lon)',0)
                                )
                ) AS closestPoint 
FROM  (
   SELECT ST_GeomFromText(geometry) as geometry
   FROM gfe_geofences
   WHERE is_active=true
   ) 
AS tempName;

Other then this point I have to find one more with distance greater then the point find above but smaller then the rest of points.

double lat1 = Math.toRadians(26.5534d); double lon1 = Math.toRadians(75.4925d); double lat2 = Math.toRadians(28.3650d); double lon2 = Math.toRadians(77.1232d); double dellat = (lat2 - lat1); double dellon = (lon2 - lon1); double R = 6371; double a = Math.sin((dellat) / 2) * Math.sin((dellat) / 2) + Math.cos(lat1) * Math.cos(lat2) * Math.sin((dellon) / 2) * Math.sin((dellon) / 2); double c = 2 * (Math.atan2(Math.sqrt(a), Math.sqrt(1 - a))); double d = R * c; System.out.println("Value of distance in km = " + d); — Satish Sharma
calculated the distance in loop for each point from geometry with point A and find up the two least or minimum distance from that all. — Satish Sharma
If you only care about the points in the geometry, and not the line segments between points, you could convert the boundary of the polygon into a MULTIPOINT, find the closest point, remove it and then find the second closest point. — Nathan Villaescusa
It is very hard to decipher the comment you added. Update your question with proper format for such a purpose. Also add some explanation to the big function in your question: what is it supposed to do? Where does it fail exactly? What should the result look like (example values)? — Erwin Brandstetter
If you care about points on the line segments, the second closest point isn't well defined. Imagine you have the line y=1, and the point x=2,y=2. The closest point on the line is x=2, y=1. The second closest point would be "right next to" x=2, y=1, but for any point you choose (say x=2.01,y=1) there is another closer one (x=2.005,y=1). — Cirdec

Markus Mikkolainen Markus Mikkolainen · Accepted Answer · 2013-01-04T13:21:34

Use ST_DumpPoints() to dump the points of the polygon, then select from that order by ST_Distance to A limit 2. ?

So it is something like

SELECT * from ST_DumpPoints(poly) order by ST_Distance(A,geom) asc limit 2;

(assumes that this is an inner select where poly is the polygon, A is the point to compare to and geom is the geom column of one of the points in the poly being compared)

Two closest points on boundary of Postgis geometry

4 Answers