Operation on Mixed SRID Geometries

[l34marr]

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]

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]

Old code uses SRID=4326 but new code uses SRID=-1 that needs to be checked if related to the error.
timwaters@eb5b3b3 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]

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);