postgresql – Postgis几何边界上的两个最近点

我也有一个点A在几何体内。我要做的是找到位于多边形几何体表面上的点A的两个最近点。

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;

我只想在多边形几何的边界上找到两个点。就像我从查询中找到一个：

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

其他然后这一点，我必须找到一个更多的距离大于然后点找到以上，但小于其余的点。