How to find all points within polygon in postgis?

Question

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

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

UPDATE

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.

Ohlsen1980 Ohlsen1980 · Accepted Answer · 2017-09-19T13:28:42

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:

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.

How to find all points within polygon in postgis?

1 Answers