I'm new to PostGIS and I'm amazed what can be done with it! Currently I'm running a query from a large database where I need to split the extracts into smaller chunks so that I'm not bouncing at the ~2GB limit for DBF files with the extract.
pgsql2shp -f temp1.shp -h localhost -u postgres -P password gb "SELECT waterext.geom FROM waterext WHERE waterext.geom && ST_ENVELOPE(0,0,1,1)" pgsql2shp -f temp2.shp -h localhost -u postgres -P password gb "SELECT waterext.geom FROM waterext WHERE waterext.geom && ST_ENVELOPE(1,0,2,1)" ...
While this works fine with points, it doesn't work so well with polygons. Polygon features that overlap the envelope partially, will show in in the neighboring envelope as well, which creates unwanted duplicates.
My idea is to run the centroid of each polygon feature against the envelope to see if it lies within. Would something like that be possible or are there easier solutions?