I have a polygon table that consists of 700 records with the subtype of 'OutdoorRecreationalFacility'. this postgres table is supposed to be a table of 1000 foot buffers around certain parcels. However in some instances the parcel geometry exists in a separate record shown in the picture below

The problem is none of these records have addresses so I cannot st_union them by address which would solve my problem. There is no attribute link between the parcels and the parcel buffer. they are only spatially linked. I am trying to come up with a query that would either

  1. select the larger geometry if one geometry falls completely within another geometry or select the larger geometry

  2. for all the geometry records that fall within another geometry record st_union them together

enter image description here

I formulated this query but it just keeps running and running (has an index)

 select case
when st_within(st_makevalid(t1.geom),st_makevalid(t2.geom)) then st_union(st_makevalid(t1.geom),st_makevalid(t2.geom))
else t2.geom end geom
from buffer_unions t1 cross join buffer_unions t2
where t1.subtype = 'OutdoorRecreationalFacility' and t2.subtype  ='OutdoorRecreationalFacility'

My thought process behind this query is if a geom falls within another geom, union them together, if not just return the t2.geom ....

1 Answer 1


If the small polygons are more or less rectangular, not overlapping each other and have a simple geometry, you could match the buffered polygons to the small ones by assessing if their centroid falls within the small polygons.

The query to identify the small polygons that contains the centroid of another (buffered) one should be similar to this. You can then delete the not-null IDs

  select  smallpoly.id smallID_to_delete ,
          bufferedpoly.id bufferID_for_reference
  from buffer_unions bufferedpoly
  left join buffer_unions smallpoly
  on st_within(ST_Centroid(bufferedpoly.geom), smallpoly.geom) 
  where smallpoly.subtype = 'OutdoorRecreationalFacility'

PS: the query you posted will return way too much results, count(*) * (count(*) - 1), as you do a cross join and return the 2nd geometry if t1 is not within t2 (and each t1 polygon is at most in one t2, so you get almost a copy of t2 for each t1). With such query you must keep the matching entries only

  • A similar approach that is more robust to irregular-shaped polygons would be to create a buffer for each polygon, and do the ST_Within() on that buffer. If the large polygons are a 1000-ft buffer of the original parcels, then substitute this for JGH's ST_Within() clause: on st_within(ST_Buffer(smallpoly.geom,990),bufferedpoly.geom). The 990 feet provides some tolerance. If the buffering is precise, use `ST_Equals(ST_Buffer(smallpoly.geom,1000),bufferedpoly.geom).
    – amball
    Jun 21, 2017 at 6:32
  • @amball you must know how the buffer was created (think of projections) and/else a much higher tolerance is required... which makes it inaccurate for small polygons (5ft wide in your example).
    – JGH
    Jun 21, 2017 at 11:58
  • so this query does not work. 1. many of the geometries polygons are not rectangular (lots of weird shapes) 2. the buffers often overlap 3. wouldn't be more accurate to do a st_within(small,large)? 4. point taken about the cross join 5. I tried a st_equals function but as you said in your above comment the tolerance and accuracy of the buffer boundaries makes it difficult to rely on that method
    – ziggy
    Jun 21, 2017 at 13:30

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.