Why does only one of the following queries throw an error?

Question

There are 2 SQL queries:

SELECT wkb_geometry FROM building_polygon WHERE ST_Contains(wkb_geometry, ST_GeomFromText('POINT (29.4 60.1)'));
SELECT wkb_geometry FROM building_polygon WHERE ST_Contains(wkb_geometry, ST_GeomFromText('POINT (30.32670 59.92843)'));

The first query works fine but returns 0 rows. But the second query throws an error Operation on mixed SRID geometries. It is not problem to point SRID for second query but it is not clear for me why the first query doesn't returns the same error. Is it expected behavior?

Looks like there could be something strange in your data. Can you make a representative set available? — BradHards, Jan 26 '13 at 23:36
It looks like not data issue. Main difference between first and second query: first query returns 0 rows, but second returns 1 row if I point SRID value. ST_AsText in this case returns
MULTIPOLYGON(((30.3258306 59.9286599,30.3262269 59.9288546,30.327901 59.9279989,30.3275046 59.9278042,30.3258306 59.9286599))) — drnextgis, Jan 27 '13 at 03:23

score 4 · Accepted Answer · edited Jan 27 '13 at 09:37

Short answer:

Always define the SRID with geometries. So use one of these to make your point:

ST_GeomFromText('POINT (29.4 60.1)', 4326)
'SRID=4326;POINT(29.4 60.1)'::geometry
ST_SetSRID(ST_MakePoint(29.4, 60.1), 4326)

If the input geometry does not have an SRID, then it is undefined (either -1 or 0, changed in version 2.0).

Long answer

The definition of ST_Contains uses the SQL statement on two input geometries ($1 and $2):

SELECT $1 && $2 AND _ST_Contains($1,$2)

which first does a bounding-box (index) search with the && operator. This first part query does not require the SRID, so if zero results are found with the bounding boxes, then the second and more expensive query is not needed. However, if there are bounding box matches, then _ST_Contains is called to see if the actual geometries meet the requirements of "Contains". This function checks for matching SRIDs.

The first query had no bounding box matches with &&, so _ST_Contains was not called.

The second query had bounding box matches, but caught an error with _ST_Contains.

Why does only one of the following queries throw an error?

1 Answers1

Short answer:

Long answer

Linked