2

I am trying to replicate an ArcGIS select by location type query in PosGIS. In ArcGIS I am using the "are within a distance of the source layer feaure" where the target layer is municipalities and the source layer is a 'zone'. I am applying a negative distance to only select the municipalities that are 50 feet deep in the 'zone'. I am doing this query as opposed to the boolean intersect method because I do not want any municipalites that simply touch a boundary of the 'zone'. It works good in ArcGIS. In PostGIS I am trying to use this expression

where ST_DWithin('zone'.geom,mun.geom,-50) 

but it gives me this error

ERROR:  Tolerance cannot be less than zero

********** Error **********

ERROR: Tolerance cannot be less than zero
SQL state: XX000

in ArcGIS

enter image description here

1

Without any tables/test data, it's hard to test this however, ST_Buffer has a note on it that you may find applicable,

Negative radii: For polygons, a negative radius can be used, which will shrink the polygon rather than expanding it.

So I would think you could

WHERE ST_DWithin(
  ST_BUFFER( zone.geom, -50 )
  mun.geom,
  0
)
| improve this answer | |
  • Good answer, you could also use ST_Contains, which is the inverse of ST_Within, ie, zone and mun in the opposite order, seeing as the tolerance no longer matters. I have no idea if there would be any performance implications either way. – John Powell Dec 2 '16 at 17:47
  • yeah that worked perfectly – ziggy Dec 2 '16 at 17:50
  • @JohnBarça in terms of performance my original query took about 20 second to run while the new took about 1:40 – ziggy Dec 2 '16 at 17:52
2

Doing something like this will work, but with performance penalties:

WHERE ST_Contains(
  ST_Buffer(zone.geom, -50)
  mun.geom
)

The trouble is you're doing an unindexed join (you don't have a spatial index on the negative buffer of the zone) and you're also applying the buffer to every candidate pairing of the full join as a result. You want to combine an indexed filter that is a superset of the exact filter, something like:

WHERE ST_Contains(
  zone.geom,
  mun.geom
)
AND ST_Contains(
  ST_Buffer(zone.geom, -50)
  mun.geom
)

Now you get the index working for you (via the first contains) and also an optimized contains test (so only pairings that are already contained at 0m buffer come through) before you run the negative buffer and final test. You'll still be running negative buffers, but hopefully many fewer, so you'll get a faster result.

| improve this answer | |
  • Depending on how often he does it, he could also create a GIST INDEX on ST_Buffer(zone.geom, -50). – Evan Carroll Dec 4 '16 at 0:00
  • 1
    Or even better, just add a geometry column with the smallified shapes. – Paul Ramsey Dec 4 '16 at 15:58

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.