Opened 4 years ago

Closed 3 years ago

Last modified 3 years ago

#4096 closed defect (wontfix)

ST_Intersects does not return error when comparing different SRIDs

Reported by: katbot Owned by: pramsey
Priority: low Milestone: PostGIS 2.4.5
Component: postgis Version: 2.4.x
Keywords: Cc:


I have Point geometry objects with SRID 4326 in one table, and MultiPolygon? geometry objects with SRID 3042 in another. I was trying to find all the Points in the first table that fall within any MultiPolygon? in the second. I originally tried the first query below, using ST_Intersects with different SRIDs but didn't get an error - the only messages are "O rows affected." The second query works fine and returns the expected data after transforming to the correct SRID. Is it a bug that there is no error returned when comparing different SRIDs?

Query 1:

SELECT points.*
FROM tweets AS points
JOIN census_data AS poly
ON ST_Intersects(poly.geom, points.tweet_geom);

Query 2:

SELECT points.*
FROM tweets AS points
JOIN census_data AS poly
ON ST_Intersects(poly.geom, ST_Transform(points.tweet_geom, 3042));

PostGIS Version: "POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released 2018/03/19" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER"

PostgreSQL Version: "PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit"

Change History (4)

comment:1 Changed 4 years ago by komzpa

This happens due to following optimization: ST_Intersects is in fact replaced to calls to && and _ST_Intersects. && is fast indexed operator that works on boxes and does not check srid, as boxes don't have one. _ST_Intersects performs the check, but is never called if boxes don't intersect.

comment:2 Changed 4 years ago by katbot

OK, understood about the reasons for optimization. Perhaps this is a documentation issue, then? Maybe this is common sense to some, but was not obvious to me that matching SRIDs was necessary for the ST_Intersects function (or ST_Contains, ST_Within, or others), and was not documented.

comment:3 Changed 3 years ago by pramsey

Resolution: wontfix
Status: newclosed

I'm going to close this out as "won't fix". I don't really think a doco fix will help anyone, and as komzpa noted it's quite hard to sneak around the implementation to squeeze out an error message in this case.

comment:4 Changed 3 years ago by komzpa

I've put it into wishlist for index tuple in PostGIS 3, to keep SRID:

Note: See TracTickets for help on using tickets.