[postgis-tickets] [PostGIS] #2556: ST_Intersects results depending on insert order

[PostGIS]

#2556: ST_Intersects results depending on insert order
--------------------------------------------------+-------------------------
 Reporter:  gekorob                               |       Owner:  pramsey      
     Type:  defect                                |      Status:  new          
 Priority:  high                                  |   Milestone:  PostGIS 2.1.2
Component:  postgis                               |     Version:  2.1.x        
 Keywords:  st_intersects intersection geography  |  
--------------------------------------------------+-------------------------
 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"
 }}}.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2556>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.