CODEWITHSUNDEEP
postgresqlgispostgis
Brijan Elwadhi
Brijan Elwadhi

Reputation: 450

How to find all points within polygon in postgis?

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);

This is giving me random results when I pass the polygon from google maps to backend. It's 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?

We tried with st_intersects(); it did not work. enter image description here

Please find below queries

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)


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.

Upvotes: 8

Views: 15960

Answers (1)

Ohlsen1980
Ohlsen1980

Reputation: 314

You can use 

SELECT POINT_LOCATION 
FROM LOCATIONS_TABLE 
WHERE ST_Contains(ST_GEOMFROMTEXT('POLYGON((P1.X P1.Y, P2.X P2.Y, ...))'), LOCATIONS_TABLE.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.

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)

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  (...)

I visualized the situation in qgis, see picture below: Sample

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

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

Upvotes: 7

Related Questions