[postgis-users] Strange problem with &&

Paragon Corporation lr at pcorp.us
Thu Nov 6 20:33:27 PST 2008


I've run into a little bit of a bizarre problem which is causing me some
headache in one of my projects.  I have a recursive trigger that moves
coincident edges when one end of a line is moved and another process which
regenerates tiles for the affected areas.  On occasion the regeneration
fails (and I suspect my trigger may be failing too in some cases).

On further investigation of the problem - I came across something a bit
disturbing.  
Am I wrong in assuming that the raw representation of PostGIS geometry
should not do any rounding and should represent fidelity?
If so, then why do these 2 queries give me different answers and the bboxes
are slightly different.
 
These 2 queries should be equivalent since the static one is just the raw
PostGIS geometry output of a sample of affected tlids
Query 1: 

SELECT sample1.tlid as tlid_1, sample2.tlid As tlid_2,
ST_box2d(sample1.the_geom)  As thebbox, sample1.the_geom &&
ST_GeomFromText('POINT(-9342916.01634406 3876667.13640485)',3395) As
bbox_intersectp, sample1.the_geom && sample2.the_geom As geomsbbox_intersect
FROM 
(SELECT 66664219 as tlid,
'0102000020430D000007000000F8E38580F8D161C1D3B675919D934D4178B6486D08D261C13
884EA3AD2934D414C7935360CD261C1F5A378B80B944D413B53505F10D261C14ACBA88B2E944
D41605BC74815D261C10769A3AD3D944D41EFC4B4161CD261C1B85475A33A944D41621774842
2D261C1AC0092062D944D41'::geometry As the_geom
UNION ALL
SELECT 66664276 as tlid,
'0102000020430D0000030000001F8075C302D261C1DB538FE12E934D41A139835CFCD161C1C
6E4898766934D41FAE38580F8D161C1D1B675919D934D41'::geometry As the_geom
) As sample1
INNER JOIN 
(SELECT 66664219 as tlid,
'0102000020430D000007000000F8E38580F8D161C1D3B675919D934D4178B6486D08D261C13
884EA3AD2934D414C7935360CD261C1F5A378B80B944D413B53505F10D261C14ACBA88B2E944
D41605BC74815D261C10769A3AD3D944D41EFC4B4161CD261C1B85475A33A944D41621774842
2D261C1AC0092062D944D41'::geometry As the_geom
UNION ALL
SELECT 66664276 as tlid,
'0102000020430D0000030000001F8075C302D261C1DB538FE12E934D41A139835CFCD161C1C
6E4898766934D41FAE38580F8D161C1D1B675919D934D41'::geometry As the_geom
) As sample2 ON (sample1.tlid <> sample2.tlid)

Gives this answer which is closer to what I expect:
tlid_1		tlid_2		thebbox
bbox_intersectp	geomsbbox_intersect
66664219	66664276	BOX(-9343253 3876667,-9342916 3876987.5)
t			t
66664276	66664219	BOX(-9342999 3876445.75,-9342916 3876667.25)
t			t


Query 2: The actual table

SELECT sample1.tlid as tlid_1, sample2.tlid As tlid_2,
ST_box2d(sample1.the_geom)  As thebbox, sample1.the_geom &&
ST_GeomFromText('POINT(-9342916.01634406 3876667.13640485)',3395) As
bbox_intersectp, sample1.the_geom && sample2.the_geom As geomsbbox_intersect
FROM 
(select tlid, the_geom, stusps,mtfcc FROM master_edges 
WHERE 
tlid in (66664219,66664276) ) As sample1 
INNER JOIN 
(select tlid, the_geom,stusps,mtfcc FROM master_edges 
WHERE 
tlid in (66664219,66664276) ) As sample2 ON (sample1.tlid <> sample2.tlid) ;

Gives this answer:

tlid_1		tlid_2		thebbox
bbox_intersectp	geomsbbox_intersect
66664219	66664276	BOX(-9343253 3876630.25,-9343013 3876987.5)
f			f
66664276	66664219	BOX(-9342999 3876445.75,-9342916 3876667.25)
t			f


I am running:

POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.5.0, 22 Oct 2006"
USE_STATS 
PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070626 (Red Hat 4.1.2-14)

I thought maybe this was a bug in GIST from before my upgrade, so I dropped
my gist index on the affected table and readded it and still get the same
bizarre behavior.

Thanks,
Regina






More information about the postgis-users mailing list