2

I run into a weird situation when I execute PostGIS query like below

SELECT slink_id, ST_Intersection(geom, st_makeenvelope(883712.000000, 1468960.000000, 884736.000000, 1469984.000000, 5179)) geom 
FROM   basemap.DT_TPEG_LINE 
WHERE  LEVEL IN ('101', '102', '103', '104', '105', '107', '109') 
  AND  geom && ST_MakeEnvelope(883712.000000, 1468960.000000, 884736.000000, 1469984.000000, 5179)

When I executed the same query on the PostGIS ver 3.0.7 and 3.3.2 respectively the result value is not the same. PostGIS ver 3.0.7 returns

"4060278300" "GEOMETRYCOLLECTION EMPTY

but ver 3.3.2 returns

"4060278300" "LINESTRING EMPTY"

I don't know why the result has changed even if the same query is executed.

I've searched StackExchange similar cases and found a comment like

https://gis.stackexchange.com/a/360951/217931

If this comment is associated with this case I need some more detailed explanation. Plz.

3
  • I edited the title to name the Intersection function on it (maybe someone will approve the change soon), and be a little more concise. See that the key here is that both results are EMPTY. Pleas look if the answer clarifies the situation.
    – Javier JC
    Jan 13, 2023 at 5:48
  • @JavierJC Thanks a lot for your quick response.! The answer is all what I want.
    – skanto
    Jan 13, 2023 at 12:40
  • You are welcome, please accept the answer as correct.
    – Javier JC
    Jan 13, 2023 at 13:36

1 Answer 1

6

Both queries are returning the empty set because there is no intersection of geometries, meaning no element of the table intersects with the envelope. There is, however, an element whose bounding box intersects with the bounding box of the envelope, fulfilling the where clause.

The difference in the type of the empty object can be confusing but can be tracked down in the documentation.

Version 3.0.7, from the manual:

If the geometries do not share any space (are disjoint), then an empty geometry collection is returned.

Newest versions, from the current docs

If the geometries have no points in common (i.e. are disjoint) then an empty atomic geometry of appropriate type is returned.

Meaning that postGis is guessing the expected type of the intersection, this is usefull when the returned value is used for a query that expects some specific type of geometry in return for the ST_Intersection call.

The answer linked in the question says exactly that, but the question it is answering is different, hence I put this as a separate answer instead of assuming that this is a duplicate.

1
  • Correct. The change in semantics is due to the switch to OverlayNG in GEOS 3.9.
    – dr_jts
    Jan 13, 2023 at 17:36

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.