2

I have a point at (-130.2, 30.5) and a box at (-130, 30, -129, 31). As geometry, the && operator reports no bounding box intersection, while as geography, it does:

WITH src(point, envelope) AS (SELECT
    ST_SetSRID(ST_MakePoint(-130.2, 30.5), 4326) AS point,
    ST_MakeEnvelope(-130.0, 30.0, -129.0, 31.0, 4326) AS envelope)
SELECT
    point::GEOMETRY && envelope::GEOMETRY AS geom_bbox_intersects,
    point::GEOGRAPHY && envelope::GEOGRAPHY AS geog_bbox_intersects,
    ST_Intersects(point::GEOGRAPHY, envelope::GEOGRAPHY) AS geog_poly_intersects
FROM src;

 geom_bbox_intersects | geog_bbox_intersects | geog_poly_intersects
----------------------+----------------------+----------------------
 f                    | t                    | f

Here's what the scenario looks like in 2D (using QGIS in SRID 4326): point and poly

I assume for geography, PostGIS is using a rectangular bounding box on the spheroid rather than my longitude-parallel envelope. Is that the case? How might I visualize what's going on in PostGIS using a 2D tool like QGIS?

Versions

  • PostgreSQL: 10.17
  • PostGIS: 2.4
sjones
  • 301
  • 4
  • 8

1 Answers1

1

Geography use great circle arcs instead of straight lines. To visualize them, you can segmentize a geography into small segments, then convert these segments to geometry

WITH src(geom) AS (SELECT ST_MakeEnvelope(-130.0, 30.0, -129.0, 31.0, 4326) AS geom)
SELECT
    st_segmentize(geom::geography,1000)::geometry
FROM src;

That being said, a great circle along a meridian is the meridian itself, so I really fail to see why the geography bounding box are said to overlap. If it fits your workflow, using st_intersects() will return false with the two sample shapes as geography.

JGH
  • 15,928
  • 4
  • 31
  • 48
  • Thanks for the helpful response (what `&&` should mean with geographies, and how to visualize great circle arcs). Sadly, I still don't know how to visualize what's going on in PostGIS that would cause these bounding boxes to intersect. I tried `ST_Segmentize()`, and the difference is minimal (in fact, the minimum longitude is still -130). I also added an `ST_Intersects()` case to the original question, which indeed is `false`. – sjones Jan 19 '22 at 19:01