如何在Postgis中查找多边形内的所有点? [英] How to find all points within polygon in postgis?

问题描述

我将位置存储在location_table(point_location几何)中，现在我在Google地图上绘制了一个多边形并将该多边形(几何)传递到后端，我想找到该多边形内的所有位置.

I have locations stored in location_table (point_location geometry), now i draw a polygon on google map and pass that polygon (geometry) to backend, I want to find all the locations that are within that polygon.

SELECT POINT_LOCATION 
FROM LOCATIONS_TABLW 
WHERE ST_Contains(GeomFromEWKT(?), POINT_LOCATION);

当我将多边形从Google地图传递到后端时，这给了我随机的结果.它没有给我所有完全在多边形内的点.它给我的点甚至在多边形之外.

This is giving me random results when I pass the polygon from google maps to backend. Its not giving me all points that are exactly within the polygon. It gives me points that are even outside the polygon.

在邮政中准确地找到多边形内所有点的正确方法是什么(也包括边界情况)

What is the correct way to find all points within polygon in postgis with accuracy (including border cases also)

更新: 我们尝试使用st_intersects()效果不佳.

Update : we tried with st_intersects() it did not work as well.

更新

请在下面查询

SRID = 4326; POLYGON((-103.30549637500008 20.852735681153252，-103.08103481249998 20.612974162085475，-101.6261045 20.537532106266806，-99.83567868749998 20.395877027062447，-99.80306537500002 22.0572706994358，-99.64994812500538,509.732.3752525250392 SRID = 4326; POINT(-103.496956 20.722446) SRID = 4326; POINT(-103.4955 20.723544)

SRID=4326;POLYGON((-103.30549637500008 20.852735681153252,-103.08103481249998 20.612974162085475,-101.6261045 20.537532106266806,-99.83567868749998 20.395877027062447,-99.80306537500002 22.0572706994358,-99.64994812500004 28.918636198451633,-121.1212769375 8.69559423007209,-103.30549637500008 20.852735681153252)) SRID=4326;POINT(-103.496956 20.722446) SRID=4326;POINT(-103.4955 20.723544)

选择ST_Intersects(GeomFromEWKT('SRID = 4326; POINT(-103.496956 20.722446)')，GeomFromEWKT('SRID = 4326; POLYGON((-103.30549637500008 20.852735681153252，-10 3.08103481249998 20.612974162085475，-101.6261045 20.537532106266806，-99.83567868749998 20.395877027062447，-99.80306537500002 22.0572706994358，-99.64994812500004 28.918 636198451633，-121.1212769375 8.69559423007209，-103.30549637500008 20.852735681153252)')) 这应该返回False，但是返回true.

select ST_Intersects(GeomFromEWKT('SRID=4326;POINT(-103.496956 20.722446)'), GeomFromEWKT('SRID=4326;POLYGON((-103.30549637500008 20.852735681153252,-10 3.08103481249998 20.612974162085475,-101.6261045 20.537532106266806,-99.83567868749998 20.395877027062447,-99.80306537500002 22.0572706994358,-99.64994812500004 28.918 636198451633,-121.1212769375 8.69559423007209,-103.30549637500008 20.852735681153252))')); This Should Return False, but it's returning true.

推荐答案

您可以使用

SELECT POINT_LOCATION 
FROM LOCATIONS_TABLE 
WHERE ST_Contains(ST_GEOMFROMTEXT('POLYGON((P1.X P1.Y, P2.X P2.Y, ...))'), LOCATIONS_TABLE.POINT_LOCATION);

注意:多边形必须关闭(这意味着最后一个坐标==第一个坐标).第二个参数POINT_LOCATION必须是点表中的几何列.

Note: Polygon must be closed (that means the last coordinate == first coordinate). Second parameter POINT_LOCATION must be the geometry column in your point table.

更新: 我试图在我的pg数据库中重播您的步骤.我创建了2个表，LOCATIONS_TABLE(id，geom)和POLYGON(id，geom).之后，我用2分填充了LOCATIONS_TABLE

UPDATE: I have tried to replay your steps in my pg database. I created 2 tables, LOCATIONS_TABLE (id, geom) and POLYGON (id, geom). After that i filled the LOCATIONS_TABLE with the 2 points

SRID=4326;POINT(-103.4955 20.723544)
SRID=4326;POINT(-103.496956 20.722446)

之后，我将多边形插入了POLYGON表中

After that i inserted the polygon in the POLYGON table

SRID=4326;POLYGON((-103.305496375 20.8527356811533,-103.0810348125 20.6129741620855,-101.6261045 20.5375321062668,-99.8356786875 20.3958770270624,-99.803065375 22.0572706994358,-99.649948125 28.9186361984516,-121.1212769375 8.69559423007209,-103.305496375  (...)

我看到了qgis的情况，请参见下图:

I visualized the situation in qgis, see picture below:

如您所见，这两个点在多边形内部.因此，我手动在多边形外部创建了一个点.之后，您可以使用以下sql查询来查看这些点是否在多边形内:

As you can see, the 2 points are inside the polygon. So i manually created a point outside the polygon. After that, you can use the following sql query, to see if the points are inside the polygon:

SELECT ST_Contains(polygon.geom, point.geom) 
FROM public."LOCATIONS_TABLE" point, public."POLYGON" polygon

对于内部的2点返回t，对于第三点返回false.

It returns t for the 2 points inside and false for the third point.

这篇关于如何在Postgis中查找多边形内的所有点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！