That's really quite disturbing, since it would seem to indicate that even the core algorithms are messed up. And yet you say 2.0 was fine? Because _ST_DistanceUncached is functionally "I want 2.0" (brute force algorithm all the time).
In other news, do try the 2.1 SVN branch, it contains a fix for the cache problem that "small polygon in big polygon" cases exposed. P. On Mon, Feb 3, 2014 at 5:30 PM, Jerry Sievert <je...@legitimatesounding.com> wrote: > Paul, > > Unfortunately, when we use the _ST_DistanceUncached via the _st_distance() > stored procedure change, we start to see failures in other places. As I > said, I am trying to isolate them, but the failures that we start to see are > fairly random: the initial issue is fixed, but we start to receive bad data > back from other queries, and not repeatable through two test runs. That > makes it much harder to isolate, given the size of our application. > > Thus, this fix introduces a bit of "randomness". > > > On Mon, Feb 3, 2014 at 4:19 PM, Paul Ramsey <pram...@cleverelephant.ca> > wrote: >> >> Jerry, >> >> Pretty important to isolate them, as that function's at the bottom of >> the cached tree algorithm, so even if I fix the gap in the cache logic >> I think I have found, you'll still run into other issues later. >> >> _ST_DistanceUncached uses brute force (aka 2.0) methods, and should >> work without change from previous versions (would be nice to hear >> confirmation of *that* assumption at least) >> >> P. >> >> On Mon, Feb 3, 2014 at 12:55 PM, Jerry Sievert >> <je...@legitimatesounding.com> wrote: >> > The workaround as provided exposes multiple other issues for us. I can >> > try >> > to isolate them, but essentially it causes random looking test failures >> > in >> > our app. >> > >> > On Feb 1, 2014, at 6:55 PM, "Paragon Corporation" <l...@pcorp.us> wrote: >> > >> > Jerry, >> > We know it's a PostGIS bug. The bug I think came in 2.1.0. It was the >> > change in geography to use geography cache /tree as I recall. You >> > aren't >> > the first to notice (though you provided a very succinct example). It's >> > a >> > hard bug to nail where it's happening. My naive guess is that it's >> > somewhere >> > around here - that's about as far into tracing I've gotten >> > unfortunately. >> > >> > >> > http://postgis.net/docs/doxygen/2.2/d8/de3/geography__measurement__trees_8c_a399a5e30071a3979b414ce094ff94de9.html#a399a5e30071a3979b414ce094ff94de9 >> > (where it computes the last distance) ( >> > >> > circ_tree_distance_tree(tree_cache->index, circ_tree, s, FP_TOLERANCE); >> > >> > >> > and >> > >> > >> > http://postgis.net/docs/doxygen/2.2/de/dc0/lwgeodetic__tree_8c_a15d46de87264f168426b23b4b34d75ab.html#a15d46de87264f168426b23b4b34d75ab >> > (circ_tree_distance_tree) >> > >> > As a work around if you are in a rush and until we have the issue >> > resolved, >> > you could swap out the definition of _ST_Distance with the >> > _ST_DistanceUncached. It will be slower but should produce correct >> > results. >> > Basically run this: >> > >> > CREATE OR REPLACE FUNCTION _st_distance(geography, geography, double >> > precision, boolean) >> > RETURNS double precision AS >> > '$libdir/postgis-2.2', 'geography_distance_uncached' >> > LANGUAGE c IMMUTABLE STRICT >> > COST 100; >> > >> > >> > See my note here: >> > http://trac.osgeo.org/postgis/ticket/2556 >> > >> > Thanks for your patience, >> > Regina >> > >> > http://www.postgis.us >> > http://postgis.net >> > >> > ________________________________ >> > From: postgis-users-boun...@lists.osgeo.org >> > [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Jerry >> > Sievert >> > Sent: Saturday, February 01, 2014 1:24 PM >> > To: PostGIS Users Discussion >> > Subject: Re: [postgis-users] Odd Behavior with ST_Intersects (PostGIS >> > 2.1, >> > Ubuntu 12.04) >> > >> > After spending some time trying to track down which package/version the >> > bug >> > occurs in, I found that it is actually from the upgrade of PostGIS from >> > 2.0.4 to 2.1.1. I will see if I can spend more time to isolate it >> > further, >> > but I did confirm that it is PostGIS, not one of the dependencies. >> > >> > >> > >> > On Fri, Jan 31, 2014 at 8:33 PM, Paragon Corporation <l...@pcorp.us> >> > wrote: >> >> >> >> ah found the other one: >> >> >> >> http://trac.osgeo.org/postgis/ticket/2556 >> >> >> >> ________________________________ >> >> From: postgis-users-boun...@lists.osgeo.org >> >> [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Paragon >> >> Corporation >> >> Sent: Friday, January 31, 2014 11:27 PM >> >> To: 'PostGIS Users Discussion' >> >> Cc: 'PostGIS Development Discussion' >> >> Subject: Re: [postgis-users] Odd Behavior with ST_Intersects (PostGIS >> >> 2.1,Ubuntu 12.04) >> >> >> >> Jerry, >> >> >> >> Thanks very much for the report. I had noticed what I think might be >> >> the >> >> same issue, but couldn't find such a short and succinct example as >> >> yours to >> >> reproduce. >> >> >> >> I have added to my original ticket: >> >> http://trac.osgeo.org/postgis/ticket/2422 >> >> >> >> I think there is another ticket in our bug tracker that might be the >> >> same >> >> issue too but can't recall the number. >> >> >> >> Thanks very very much for this great example. >> >> >> >> Paul -- if you are watching you have work to do :) >> >> >> >> Thanks, >> >> Regina >> >> http://www.postgis.us >> >> http://postgis.net >> >> >> >> >> >> >> >> >> >> ________________________________ >> >> From: postgis-users-boun...@lists.osgeo.org >> >> [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Jerry >> >> Sievert >> >> Sent: Friday, January 31, 2014 6:25 PM >> >> To: postgis-users@lists.osgeo.org >> >> Subject: [postgis-users] Odd Behavior with ST_Intersects (PostGIS >> >> 2.1,Ubuntu 12.04) >> >> >> >> Hello, >> >> >> >> I am using Ubuntu 12.04 with the official PostgreSQL apt repo (via >> >> https://wiki.postgresql.org/wiki/Apt). I am running into an easily >> >> reproducible issue, and was hoping for some help to solve this. >> >> >> >> When using ST_Intersects() not all rows that intersect are returned. >> >> This >> >> was not the case in previous versions that we have upgraded from. >> >> >> >> These are the steps to reproduce on a fresh install of Ubuntu 12.04 >> >> with >> >> all packages updated and PostGIS/PostgreSQL 9.3 installed: >> >> >> >> test=# CREATE TABLE test (id serial, condition_geo geography); >> >> CREATE TABLE >> >> test=# INSERT INTO test (condition_geo) VALUES >> >> (ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0)); >> >> INSERT 0 1 >> >> test=# SELECT id FROM test WHERE ST_Intersects("condition_geo", >> >> ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'), 20.0)) IS TRUE; >> >> id >> >> ---- >> >> 1 >> >> (1 row) >> >> >> >> test=# INSERT INTO test (condition_geo) VALUES >> >> (ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0)); >> >> INSERT 0 1 >> >> test=# SELECT id FROM test WHERE ST_Intersects("condition_geo", >> >> ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'), 20.0)) IS TRUE; >> >> id >> >> ---- >> >> 1 >> >> (1 row) >> >> >> >> test=# SELECT id FROM test WHERE ST_Intersects("condition_geo", >> >> ST_Buffer(ST_GeogFromText('POINT(20.0 30.0)'), 20.0)) IS TRUE AND id = >> >> 2; >> >> id >> >> ---- >> >> 2 >> >> (1 row) >> >> >> >> Note that the SELECT should return both rows 1 and 2 in the first >> >> SELECT. >> >> >> >> Any thoughts? >> >> >> >> >> >> >> >> _______________________________________________ >> >> postgis-users mailing list >> >> postgis-users@lists.osgeo.org >> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > >> > >> > _______________________________________________ >> > postgis-users mailing list >> > postgis-users@lists.osgeo.org >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > >> > >> > _______________________________________________ >> > postgis-users mailing list >> > postgis-users@lists.osgeo.org >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> _______________________________________________ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
