id,summary,reporter,owner,description,type,status,priority,milestone,component,version,resolution,keywords,cc 2556,geography ST_Intersects results depending on insert order,gekorob,pramsey,"We have problems with ST_Intersects on geography feature using postgis 2.1.0 and 2.1.1. The result of the query {{{ select id, name from images where st_intersects(extent, st_geogfromtext('SRID=4326;POLYGON((-46.625977 81.634149,-46.625977 81.348076,-48.999023 81.348076,-48.999023 81.634149,-46.625977 81.634149))')); }}} on a table {{{ create table images ( id integer, name varchar, extent geography(POLYGON,4326) ); create index extent_idx on images using gist(extent); }}} is depending on the insert order. In the following situation {{{ insert into images values(47409, 'TDX-1_2010-10-06T19_44_2375085', st_geogfromtext('SRID=4326;POLYGON((-59.4139571913088 82.9486103943668,-57.3528882462655 83.1123152898828,-50.2302874208478 81.3740574826097,-51.977353304689 81.2431047148532,-59.4139571913088 82.9486103943668))')); insert into images values(1, 'first_image', st_geogfromtext('SRID=4326;POLYGON((-162.211667 88.046667,-151.190278 87.248889,-44.266389 74.887778,-40.793889 75.043333,-162.211667 88.046667))')); }}} we can't find 'first_image' (with id=1), the query returns '0 rows'. In the opposite situation, when 'first image' is inserted before, the query, correctly returns the record with id = 1; Adding an additional condition at the end of the query, always returns expected result, for example: {{{ select id, name from images where st_intersects(extent, st_geogfromtext('SRID=4326;POLYGON((-46.625977 81.634149,-46.625977 81.348076,-48.999023 81.348076,-48.999023 81.634149,-46.625977 81.634149))')) and name like 'first%'; }}} We experimented this strange behavior only with version 2.1.x of Postgis. The last one we tried was, on Mac: {{{ PostgreSQL 9.3.1 on x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.0 (clang-500.2.75) (based on LLVM 3.3svn), 64-bitPOSTGIS=""2.1.1 r12113"" GEOS=""3.4.2-CAPI-1.8.2 r3921"" PROJ=""Rel. 4.8.0, 6 March 2012"" LIBXML=""2.9.1"" LIBJSON=""UNKNOWN"" }}} and on Ubuntu {{{ PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit POSTGIS=""2.1.0 r11822"" GEOS=""3.4.2-CAPI-1.8.2 r3921"" PROJ=""Rel. 4.8.0, 6 March 2012"" LIBXML=""2.7.8"" LIBJSON=""UNKNOWN"" }}} With Postgis 2.0.x and postgres 9.1.x we can't reproduce the problem (everything seems to work correctly, after one year in production mode). {{{ PostgreSQL 9.1.7 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bitPOSTGIS=""2.0.1 r9979"" GEOS=""3.3.5-CAPI-1.7.5"" PROJ=""Rel. 4.8.0, 6 March 2012"" LIBXML=""2.8.0"" LIBJSON=""UNKNOWN"" }}}. ",defect,closed,high,PostGIS 2.1.2,postgis,2.1.x,fixed,st_intersects intersection geography,