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.

Answer 1

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)

Answer 2

There generally is no second closest point on the boundary polygon, if you include the lines. Just like there is no real number second closest to zero. Either you only wish to consider the points at the corners, like Markus suggests. Or you have only one closest point.