1

In our application we are performing a bounding box search with incoming bounding box coordinates we are converting to a polygon. In our database we are having existing locations as point in a table in geometry column. We are framing query to get the point locations which are inside the polygon.

select * from table1 where
ST_Intersects(ST_SetSRID('POLYGON((5.372134 85.0868074,5.372134 36.556537,5.9306269 36.556537,5.9306269 85.0868074,5.372134 85.0868074))'::geometry,
4326)::geography,table1.geom);

when point coordinate which lies outside the polygon are present in the column the where clause returns expected result for first row but after that it is returning inconsistent results

7
  • 1
    What is the type of table1.geom ? Which version of Postgres / PostGIS ? Commented Jan 18, 2022 at 15:37
  • 3
    you are using geography, so great circles connect points instead of straight lines. Since your polygon spans like 50 degrees, the arc curvature will be quiet important
    – JGH
    Commented Jan 18, 2022 at 15:39
  • see this post
    – JGH
    Commented Jan 18, 2022 at 15:42
  • @JGH I observed a behaviour when I try to run the below query it returns false but the same co-ordinate present in the table1.geom returns true in the query mentioned in the question select ST_Intersects(ST_SetSRID('POLYGON((5.372134 85.0868074,5.372134 36.556537,5.9306269 36.556537,5.9306269 85.0868074,5.372134 85.0868074))'::geometry, 4326)::geography,ST_SetSRID('POINT(90.4083946672047 23.7897930523202)'::geometry ,4326)::geography);
    – Professor
    Commented Jan 18, 2022 at 16:01
  • @J.Monticolo we are using Aurora Postgres 13.4 and Postgis 3.1.4
    – Professor
    Commented Jan 18, 2022 at 16:04

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.