我有一个表地理围栏存储多边形的几何。
我也有一个点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;@H_404_6@
我只想在多边形几何的边界上找到两个点。就像我从查询中找到一个：
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;@H_404_6@
其他然后这一点，我必须找到一个更多的距离大于然后点找到以上，但小于其余的点。
我假设你想找到通过最接近问题点的多边形的边缘
为了从线[A，B]获得点’C’的距离’d’
首先翻译所有点，A为0,0
B -= A //vector subtraction C -= A@H_404_6@然后归一化B，使其长度为1.0
len = sqrt( B . B) //dotproduct of two vectors is the length squared B /= len //scalar divide by length@H_404_6@从A找到与C成直角的长度
dotp = B . C //dot product again closestPointOnLine = B * dotp //scalar multiply@H_404_6@现在得到距离
diff = (C - ClosestPointOnLine) d = sqrt(diff . diff)@H_404_6@不知道如何在sql中做到这一点。您将需要对多边形上的每个边做上述操作，然后找到最小值’d’
顺便说一下，B和C的叉积的符号现在会告诉你点是否在多边形的内部
