1

I'm storing polygons in a PostGIS database. The shape of the polygons is stored in a field of type geometry(Polygon,4326). From time to time, I need to check if a point (of type geometry(Point,4326)) is inside one of the polygons. Please note that I just need to check if the point is inside the polygon. I don't need to do any measurement.

Directly from the book "PostGIS in action":

PostGIS can store WGS 84 lon/lat (4326) as a geometry data type, but more often than not you’ll want to transform it to another SRS or store it in the geography data type so it’s usable. You can sometimes get away with using it as a geometry data type for small distances along the same longitude and when two things intersect, but keep in mind that when you use it, PostGIS is really projecting it. PostGIS squashes it on a flat surface, treating longitude as X and latitude as Y, so even though it looks unprojected, in reality it’s projected and in a mostly unusable way

Hence my question: should I cast the types of the polygons and the point to geography before checking if the point is within the polygon, or can I get away with just the geometries? From what I'm reading it looks like the geometries should be enough, but I would like to be sure.

1
  • 1
    There is no possible absolute answer. Geometry use straight lines to connect two points while geography use great circle arcs. Both are valid but can lead to different result. You would have to define your needs and only then you can decide to do the computation in geometry or in geography.
    – JGH
    Commented Jun 27, 2023 at 19:27

1 Answer 1

-1

"You can sometimes get away with using it as a geometry data type...and when two things intersect"

Yes, you can use lat/long geometry objects in SQL for doing point in polygon intersects. PostGIS gives an example of this in their documentation for ST_Intersects.

SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);  st_intersects
---------------  f (1 row) SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);  st_intersects
---------------  t (1 row)

-- Look up in table. Make sure table has a GiST index on geometry column for faster lookup. SELECT id, name FROM cities WHERE ST_Intersects(geom, 'SRID=4326;POLYGON((28 53,27.707 52.293,27 52,26.293 52.293,26 53,26.293 53.707,27 54,27.707 53.707,28 53))');  id | name
----+-------   2 | Minsk (1 row)
4
  • 2
    can and should are not the same... it is dangerous to simply ignore the differences between geometries and geographies. The following query show that two features intersects as geometry but not as geography. Both answers are correct though! with src(geo1,geo2) as (select 'POINT(10 10)'::geometry, 'LINESTRING(0 10, 20 10)'::geometry) SELECT st_intersects(geo1,geo2) as_geom, st_intersects(geo1::geography,geo2::geography) as_geog FROM src;
    – JGH
    Commented Jun 27, 2023 at 19:34
  • @JGH, I understand the differences between geography and geometry. I was just wondering if - for the very specific case of a point being within a polygon - the geometry would always work. Basically, it translates to: "if a point (talking geography here) is within a polygon (as geography), can it land outside of the polygon once projected?"
    – JPFrancoia
    Commented Jun 27, 2023 at 22:52
  • 1
    No, there are a lot of edge cases that it will give you the wrong answer, searching this site for postgis and wrong will find several examples
    – Ian Turton
    Commented Jun 28, 2023 at 9:31
  • Ok, thank you all for your answers, now I'm sure.
    – JPFrancoia
    Commented Jun 28, 2023 at 14:44

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.