[postgis-users] index-based inconsistencies

Paragon Corporation lr at pcorp.us
Wed Mar 11 15:22:32 PDT 2009

P.S. -- I meant Robert.  Sorry I'm a bit out of it today.


From: Paragon Corporation [mailto:lr at pcorp.us] 
Sent: Wednesday, March 11, 2009 6:21 PM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] index-based inconsistencies

Which version of PostgreSQL were you running when you loaded this data?  I
think I may have ran into similar issues with an earlier point version of
The problem was that the stored BBOx was wrong.  To verify if this is the
issue, do the following
SELECT ST_Box2D(the_geom) As the_wrongbbox,  ST_Box2D(ST_AsBinary(the_geom))
As the_rightbbox, ST_Box3D(the_geom) As the_right3dBox
FROM counties
WHERE  NOT (ST_Box2D(the_geom)  = ST_Box2D(ST_AsBinary(the_geom)) )
If you get answers to the above, then that is the culprit

I think to fix I ended up doing a 
ST_DropBBOx(the_geom)  on all those that didn't match the calculated and
ST_AddBBOX(the_geom) on all those where ST_HasBBOX(the_geom) = false
UPDATE counties SET the_geom = ST_DropBBOX(the_geom)
WHERE  NOT (ST_Box2D(the_geom)  = ST_Box2D(ST_AsBinary(the_geom)) );
UPDATe countries SET the_geom = ST_AddBBOX(the_geom)
WHERE ST_HasBBOx(the_geom) = false;
Hope that helps,

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Robert
Sent: Wednesday, March 11, 2009 4:55 PM
To: postgis-users
Subject: [postgis-users] index-based inconsistencies

using postgresql 8.3.4
using postgis 1.3.3
using geos 3.0.0
using proj 4.6.1

I'm encountering a couple of oddities which I'm pretty sure are related.

I have US Census Congressional districts modeled in PostGIS.

I've got a simple php program that uses ST_Within( ) to check a lat,lon
against the congressional district polygons.  A small number of the
districts are causing the query to return zero records incorrectly.

I've also got a simple php-mapscript with a LAYER to simply display all of
these congressional districts.  When zoomed out around 1:400k and further
out, these peculiar districts appear along side their neighbors.  When I
zoom in closer, these same districts stop appearing.

I'm pretty sure this is a problem with the spatial indexes, which were
created by shp2pgsql, because I converted the search program from ST_Within(
) to _ST_Within( ), which is advertised as skipping the indexing.
_ST_Within( ) is a bit slower, but finds these districts ok.

I've run VACUUM ANALYZE on the table, but this hasn't changed anything.

I haven't dug into the mapserver pg driver to see if there is any way for me
to manipulate the generated query into skipping indexes.

I haven't yet dug into the records further to see if they have pathological
bboxes or similar.

There are no holes in these polygons.

Anyone have any ideas on this?

Robert H.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090311/297e0d5a/attachment.html>

More information about the postgis-users mailing list