search

l34marr / mapwarper

public map georectifier and warper
mapwarper.net
MIT License
0 stars 1 forks source link

Operation on Mixed SRID Geometries #2

Open l34marr opened 10 years ago

l34marr commented 10 years ago

ActiveRecord::StatementInvalid in MapsController#geosearch

PG::Error: ERROR: Operation on mixed SRID geometries : SELECT bbox, title, description, updated_at, id, date_depicted FROM "maps" WHERE (ST_Intersects(bbox_geom, ST_GeomFromText('SRID=-1;POLYGON((120.55156329592 24.090678215199,121.33571246581 24.090678215199,121.33571246581 24.902956214376,120.55156329592 24.902956214376,120.55156329592 24.090678215199))'))) AND ("maps"."status" IN (4,5) AND "maps"."map_type" = 1) ORDER BY ABS(ST_Area(bbox_geom) - ST_Area(ST_GeomFromText('SRID=-1;POLYGON((120.55156329592 24.090678215199,121.33571246581 24.090678215199,121.33571246581 24.902956214376,120.55156329592 24.902956214376,120.55156329592 24.090678215199))'))) ASC, updated_at asc NULLS FIRST LIMIT 20 OFFSET 0

/usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in log' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:550:inexecute' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1037:in select_raw' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:1024:inselect' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in select_all_without_query_cache' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/query_cache.rb:60:inselect_all' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/query_cache.rb:81:in cache_sql' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/query_cache.rb:60:inselect_all' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:661:in find_by_sql' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:1548:infind_every' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:615:in find' /home/marr/mapwarper/vendor/plugins/will_paginate/lib/will_paginate/finder.rb:82:insend' /home/marr/mapwarper/vendor/plugins/will_paginate/lib/will_paginate/finder.rb:82:in paginate' /home/marr/mapwarper/vendor/plugins/will_paginate/lib/will_paginate/collection.rb:87:increate' /home/marr/mapwarper/vendor/plugins/will_paginate/lib/will_paginate/finder.rb:76:in paginate' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/named_scope.rb:181:insend' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/named_scope.rb:181:in method_missing' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/base.rb:2143:inwith_scope' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/named_scope.rb:113:in __send__' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/named_scope.rb:113:inwith_scope' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/named_scope.rb:174:in method_missing' /home/marr/mapwarper/app/controllers/maps_controller.rb:173:ingeosearch'

l34marr commented 10 years ago

References: http://ragrawal.wordpress.com/2009/12/08/operations_on_mixed_geometries/ http://gis.stackexchange.com/questions/68711/postgis-geometry-query-returns-error-operation-on-mixed-srid-geometries-only http://stackoverflow.com/questions/20231423/postgis-error-operation-on-mixed-srid-geometries-trying-to-find-overlapping-g

l34marr commented 10 years ago

Old code uses SRID=4326 but new code uses SRID=-1 that needs to be checked if related to the error. https://github.com/timwaters/mapwarper/commit/eb5b3b3f8a5ad9708cba0d51f9425eb0b6bdcb3a brings this change.

A typical correct query looks like the following:

SELECT bbox, title, description, updated_at, id FROM "maps" WHERE (ST_Intersects(bbox_geom, ST_GeomFromText('SRID=4326; POLYGON((120.17047503909 23.404354807302,121.74151996091 23.404354807302,121.74151996091 25.032431119504,120.17047503909 25.032431119504,120.17047503909 23.404354807302))'))) AND ("maps"."status" IN (4,5) AND "maps"."map_type" = 1) ORDER BY ABS(ST_Area(bbox_geom) - ST_Area(ST_GeomFromText('SRID=4326;POLYGON((120.17047503909 23.404354807302,121.74151996091 23.404354807302,121.74151996091 25.032431119504,120.17047503909 25.032431119504,120.17047503909 23.404354807302))'))) ASC, updated_at asc NULLS FIRST LIMIT 20 OFFSET 0

l34marr commented 10 years ago

ref step 3 at http://ragrawal.wordpress.com/2009/12/08/operations_on_mixed_geometries/ http://postgis.net/docs/UpdateGeometrySRID.html

select distinct(ST_SRID(bbox_geom)) as srid, count(*) from maps group by srid;

select id, title, ST_SRID(bbox_geom) from maps;

SELECT UpdateGeometrySRID('maps','bbox_geom',4326);