Operation on mixed SRID geometries error

geometrypostgispostgresqlquerysrid

I have two tables flickr_edin and temmpat.

temppat was created using:

CREATE TABLE temppat (id serial, geom geometry);
INSERT INTO temppat (geom) VALUES (ST_GeomFromText('POINT (326253.33 674110.63)'));

flickr_edin contains columns id, date_taken, user and geom.

I want to find out how many photos have been taken within 200m of this location in temmpat for each day of the week.

This is my query:

SELECT COUNT(*) as count,EXTRACT('dow' FROM date_taken) as dow
FROM flickr_edin a JOIN temppat b ON ST_WITHIN(a.geom,ST_BUFFER(b.geom,200))
GROUP BY dow;

I get this error:

ERROR: contains: Operation on mixed SRID geometries (Polygon, 0) !=
(Point, 27700) SQL state: XX000

Best Answer

An inline SQL test to demonstrate what goes wrong and how to fix the problem.

select ST_Within(
ST_GeomFromText('POINT (326253.33 674110.63)'),
ST_Buffer(ST_GeomFromText('POINT (326253.33 674110.63)',27700),10));

ERROR:  contains: Operation on mixed SRID geometries
(Polygon, 27700) != (Point, 0)
SQL state: XX000


select ST_Within(
ST_GeomFromText('POINT (326253.33 674110.63)',27700),
ST_Buffer(ST_GeomFromText('POINT (326253.33 674110.63)',27700),10));

true

Related Solutions

[GIS] PostGIS Geometry Query returns “Error: Operation on mixed SRID geometries” only for certain values

When you specify a geometry without an SRID, it is actually 0 (or -1 for version < 2):

SELECT ST_SRID('POINT(-122.334172173172 46.602634395263560)'::geometry);
 st_srid
---------
       0

So when you use this geometry with another one with SRID=4326, it is mixing 0 and 4326. This is usually a useful error, if the spatial references are truly different. With your case, the SRIDs are the same, but you didn't encode the SRID to the query point. So, to fix your query always specify the same SRID for your query point, and they will no longer be mixed.

As a side note, the geography type has a default SRID of 4326 (WGS 84), as shown here:

SELECT ST_SRID('POINT(-122.334172173172 46.602634395263560)'::geography::geometry);
 st_srid
---------
    4326

So, if you use geography types instead of geometry types, the SRID does not need to be specified (unless you want a different SRID for an alternative ellipsoid for Mars or whatever).

As to why one query has an error, and the other does not, ST_Intersects first does a && bounding box search, which is fast, and doesn't care about SRIDs. No mixed SRID error messages will be raised if the bounding boxes don't intersect. But if they do intersect, the second filter is _ST_Intersects, which is more precise and checks the two SRIDs to make sure they match, and raises an error if they are mixed. For example:

WITH pad_meta AS (
    SELECT 'SRID=4326;POLYGON((-124 50, -124 47, -121 50, -124 50))'::geometry AS area)

SELECT * FROM pad_meta where ST_Intersects(
    'POINT(-122.334172173172 46.602634395263560)'::geometry, area::geometry 
);

does not have any intersecting bounding boxes, and bypasses _ST_Intersects. But POINT(-122.334172173172 47.602634395263560) will raise the error because the bounding boxes do overlap (even though the geometries don't actually intersect).

However, with the same geometries and different filter:

WITH pad_meta AS (
    SELECT 'SRID=4326;POLYGON((-124 50, -124 47, -121 50, -124 50))'::geometry AS area)

SELECT * FROM pad_meta where _ST_Intersects(
    'POINT(-122.334172173172 46.602634395263560)'::geometry, area::geometry 
);

throws a mixed SRID error, because bounding boxes are not considered.

[GIS] Override “ERROR: Operation on mixed SRID geometries” and force PostGIS to do the query

You're going to want to replace this construction

st_intersects(proj_shape, st_buffer(cary_proj_point, 5280))

with this one

st_dwithin(proj_shape, cary_proj_point, 5280)

(because otherwise you'll have a terrible scalability problem in the future) and it will won't give you the answer you want unless proj_shape and cary_proj_point both have SRID = 2264. Do they?

Best Answer

Related Solutions

[GIS] PostGIS Geometry Query returns “Error: Operation on mixed SRID geometries” only for certain values

[GIS] Override “ERROR: Operation on mixed SRID geometries” and force PostGIS to do the query

Related Question