1

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?

Improve this question
3
  • 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
    Commented Nov 20, 2019 at 14:02
  • 5
    Use a geopackage and forget about the 2GB limit
    – Ian Turton
    Commented Nov 20, 2019 at 14:02
  • 2
    Obligatory link: Shapefile must die!
    – geozelot
    Commented Nov 20, 2019 at 17:10

1 Answer 1

Reset to default
2

[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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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