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

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?

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).

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.