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

postgissrid

I'm trying to do an st_intersects query in postgis for two shapes (a point and buffer zone) which are of the same projected coordinate system.

Unfortunately, one of the tables seems to have its table-srid value set to 4326. And yes, the first spatial column added to that table (airports) was of the geographic type (srid=4326). However, that same table has a column in it (added later) which is of the projected coordinate system (srid=2264) that I'm working with.

When I try to run this query:

a = Airport.first
WakeCountyParcel.where{st_intersects(proj_shape, st_buffer(a.cary_proj_point, 5280))}

where cary_proj_point is the projected coordinate point (srid=2264) as is WakeCountyParcel's proj_shape, I get the SRID error:

ActiveRecord::StatementInvalid: PG::InternalError: ERROR:  Operation on mixed SRID geometries

To get around this I simply reconstruct Airport's cary_proj_point to divorce its association from the tainted Airport model

cary_proj_point = RGeo::Geos.factory(:srid => 2264).parse_wkt(a.cary_proj_point.as_text)
WakeCountyParcel.where{st_intersects(proj_shape, st_buffer(cary_proj_point, 5280))}

and all is well. In fact, rails being what it is, I could abstract out all of this code and, say, create a model method for Airport that would return the reconstructed projected point without me ever even having to look at the string-based reconstruction again.

But its a hack and forcing PostGIS to do it anyway without having to trick it would be nice. I furthermore disagree with the concept of having one table per SRID value. I want my database to have geographic and projected spatial objects in different columns of the same table(s) for convenience. How do I over-ride PostGIS's pedagogic error of forcing one-SRID per table here?

Thanks

Best Answer

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?

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] Operation on mixed SRID geometries

Go back to Section 4 and make sure you input SRID 26918 as the SRID for the shapefiles you loaded (if you loaded them in pgAdmin).

PostGIS will throw and error if the SRID's being operated on directly aren't the same.

There's some description in Section 15 about this.

You can check the SRID on your geom columns using:

SELECT ST_SRID(geom) FROM nyc_streets_or_other_table LIMIT 1;

Best Answer

Related Solutions

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

[GIS] Operation on mixed SRID geometries

Related Question