0

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

  • can you post the relevant rows from the geometry_columns table? Because you did use addGeometryColumn didn't you? – Ian Turton Sep 3 '13 at 5:52
3

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?

  • They do have the same srid. The airport model also has a column with cary_proj_point's location data stored in WGS84 (srid:4326), but cary_proj_point is of the same projected coordinate system as WakeCountyParcel's proj_shape – boulder_ruby Sep 3 '13 at 2:12
  • Basically, I have a model (here, Airport) whose source data (i.e. its shapefile) was originally WGS84 but has been also converted into NAD83 (North Carolina 3000 feet) (2264) in the cary_proj_shape column. The table airports has two columns representing two distinct projected coordinate systems. PostGIS does not respect this and is throwing an error. – boulder_ruby Sep 3 '13 at 2:17
  • Without looking at your system directly, there's not much more I can say. PostGIS of course is not throwing this error for fun, there are truly differing SRIDs somewhere, this check has been in place for years, it's not something new. Turn on statement logging in PostgreSQL and get the ACTUAL SQL that Ruby is generating and see if it makes sense, if the columns being referenced do in fact have geometries of the same SRID. – Paul Ramsey Sep 3 '13 at 19:00
  • Oh, and run "SELECT DISTINCT ST_SRID(proj_shape) FROM yourTAble" and "SELECT DISTINCT ST_SRID(cary_proj_point) FROM yourTable)". See that they're the same in all instances. – Paul Ramsey Sep 3 '13 at 19:01
  • WakeCountyParcel.connection.select_value("SELECT DISTINCT ST_SRID(proj_shape) FROM wake_county_parcels")#=> 2264 – boulder_ruby Sep 3 '13 at 20:26
0

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)

  • No, I'm not. The two shapes I'm doing an operation for have the same projected coordinate system. One of the table's srid values is set to 4326, but the column I'm operating with in that table is of the same PCS as the other table. If I were to transform anything it would be inaccurate. – boulder_ruby Sep 2 '13 at 20:57
  • You and the database, however, are clearly on the same page. If you and the database were correct, it would be a different story. – boulder_ruby Sep 2 '13 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.