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?

  • Even centroids have a boundary condition issue. You could change your logic to test "overlaps this but not overlaps that", where that is a slowly growing union of the previous this values. – Vince Nov 20 '19 at 14:02
  • 5
    Use a geopackage and forget about the 2GB limit – Ian Turton Nov 20 '19 at 14:02
  • 2
    Obligatory link: Shapefile must die! – geozelot Nov 20 '19 at 17:10

[Edit: Changed to ST_PointOnSurface after suggestion in comments]

How about something like:

SELECT waterext.geom FROM waterext WHERE ST_WITHIN(ST_PointOnSurface(waterext.geom), ST_ENVELOPE(0,0,1,1))

There's probably some better, more automated way to get tiled data out from PostGIS though.

  • 2
    use st_pointonsurface -- its more accurate than st_centroid – ziggy Nov 20 '19 at 14:37
  • 1
    And note that ST_PointOnSurface is much faster in the latest version of PostGIS. – dr_jts Nov 20 '19 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.