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

前端之家收集整理的这篇文章主要介绍了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;@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的叉积的符号现在会告诉你点是否在多边形的内部

猜你在找的Postgre SQL相关文章