Override "ERROR: Operation on mixed SRID geometries" and force PostGIS to do the query

Question

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

Answer 1

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?

Answer 2

You are performing an operation of intersectacion ST_INTERSECTS between two different projections geometries (SRID), which causes the problem what to do is transform a geometry to the same projection of the other geometry with which you want to intersect using:

st_transform(geometria, newsrid)