1

Using PostGIS, I have a table of data with geographic points defined as last_known_coordinates GEOGRAPHY(POINT,4326). I want to find all of these within a geographic bounding box with given top left and bottom right geographic coordinates, and be sure that the calculations are geographic, not geometric, e.g. that the results account for the curvature of the earth.

The query I'm using contains the fragment: last_known_coordinates && ST_MakeEnvelope(?, ?, ?, ?, 4326)::geography

As you can tell I'm a bit new to this, and I know what I want, but not necessarily how to do it correctly. Other answers hint that this is correct, but I'd like to know for sure: Will this point data and SQL fragment take into account the curvature of the Earth in the bounding box?

8
  • Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short tour for more about how the site works
    – Ian Turton
    Apr 24, 2021 at 15:02
  • 1
    The most reliable way to find this out is to test it, rather than accepting assurances from others. I'd assume that there would be issues, especially crossing the International Date Line.
    – Vince
    Apr 24, 2021 at 15:15
  • 2
    ST_Envelope postgis.net/docs/ST_Envelope.html does not work at all with geography but you seem to ask another question: Does casting a rectangular polygon geometry with four corner vertices into geography take the earth curvature into account.
    – user30184
    Apr 24, 2021 at 15:17
  • 3
    I believe that the essential part of your query is not ST_MakeEnvelope()::geography. That certainly yields a geography. You did not show how you are using that geography in your SQL.
    – user30184
    Apr 24, 2021 at 15:24
  • 1
    Have a look at gis.stackexchange.com/questions/393389/… if it happens to be useful. By the second answer ST_Intersects should work. I think that it also means that && does not suit for you.
    – user30184
    Apr 24, 2021 at 18:13

1 Answer 1

1

The answer to the actual question is that ST_Intersects will use a sphere calculation for the bounding box, and if the spherical, not planar, bounding box calculation is desired, use it instead of &&.

ST_MakeEnvelope will return a bounding box that can be cast as a geography, and doesn't bear on the question of spherical vs planar.

My current query is e.g.:

SELECT * FROM ds AS "d" WHERE ST_Intersects("d"."last_known_coordinates", ST_MakeEnvelope(<coords>, 4326)::geography);

In fact ST_Intersects seems to use && first, and then apply another filter using st_intersects. Here's the EXPLAIN:

"QUERY PLAN"
"Index Scan using d_last_known_coordinates_idx on ds d  (cost=0.13..34.39 rows=1 width=7736)"
"  Index Cond: (last_known_coordinates && '<bytes here>'::geography)"
"  Filter: st_intersects(last_known_coordinates, '<bytes here>'::geography)"

Now I just need to deal with the antemeridian.

1
  • Actually, since ST_Intersects uses spherical calculations, it deals with the antemeridian fine.
    – Tony
    Apr 26, 2021 at 13:28

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.