2

I have a polygon table in postgres that has important columns of: address,type_(i know confusing wasnt me),subtype and geom.

this table is parcels buffered 1000 feet. some records like the picture below contains both the parcel geometry and buffer geometry with the same exact records address,type_,subtype. I use this layer to perform various distance calculations to other parcels by the b_uniqueid and the records with just the parcel geometries are giving me a boat load of trouble.

enter image description here

things I have tried:

drop table if exists test2;
create table test2 as
select st_union(geom) geom,address,type_,subtype from buffer_unions group by address,type_,subtype

gives me a

ERROR:  GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-intersection at or near point 20021031.841838982 -322552.71008741739 at 20021031.841838982 -322552.71008741739
********** Error **********

ERROR: GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-intersection at or near point 20021031.841838982 -322552.71008741739 at 20021031.841838982 -322552.71008741739
SQL state: XX000

and in arcgis the dissolve tool does not give me the correct results.

I have also tried:

select st_multi(st_collectionextract(st_collect(geom),3))::geometry(multipolygon,102689) geom,address,type_,subtype from buffer_unions group by address,type_,subtype 

but because the 700 records of subtype OutdoorRecreationFacility has no address field those columns get dissolved down to one record.

What I am looking for is a query which goes through the table and analyzes 1. if a smaller geometry is inside a larger geometry and has the same address and subtype, dissolve them or alternatively maybe if have the same address and subtype compare there st_area's and just take the larger one (not sure how to do that) 2. for the OutdoorRecreationFacility records, because they have no addresses I need to either dissolve if a geometry falls completely within another geometry or just select the larger one

I tried this query but got the the topology error like above

select case
when st_contains(t1.geom,t2.geom) then st_union(t1.geom,t2.geom) 
else t1.geom end geom
from buffer_unions t1 cross join buffer_unions t2
where t1.subtype = 'OutdoorRecreationalFacility' and t2.subtype  ='OutdoorRecreationalFacility'
  • This sort of question comes up a lot. Have you checked all your polygons are valid to start with. Have you used ST_MakeValid if not. – John Powell Jun 20 '17 at 16:43
  • didnt know st_makevalid exists il give that a shot. whats the best way to use it? im thinking about putting it inside the st_union function – ziggy Jun 20 '17 at 17:30
  • @JohnPowellakaBarça st_makevalid works! but I still have the issue with the 'OutdoorRecreationalFacility' 700 records collapsing down to 1 record. I only want to union the the parcels that fall completely within the buffers – ziggy Jun 20 '17 at 18:06
1

You can select the entries using a Distinct On clause on the 3 fields and ordering them by area descending. Since Distinct On keeps the first returned row only, you are therefore keeping the largest polygon, i.e. the buffer. As you said some entries need a special handling, you can omit them from the first query and union them (or apply another rule for distincting them)

create table test2 as (
select  DISTINCT ON (address,type_,subtype) address, type_, subtype, geom
  from buffer_unions
  where subtype <> 'OutdoorRecreationalFacility' 
  order by address,type_,subtype, st_area(geom) desc
UNION ALL
select  address,type_,subtype,geom 
  from buffer_unions
  where subtype = 'OutdoorRecreationalFacility')
  • ahh neat little trick with the distinct on clause. il give that a shot – ziggy Jun 20 '17 at 17:44
  • I tried @John Powell aka Barca st_makevalid suggestion and that worked within the st_union as well – ziggy Jun 20 '17 at 17:45
  • your query did not work. kept giving me an error I think your syntax for the distinct on clause was off. I modified it to this: but it did not give me accurate results: – ziggy Jun 20 '17 at 18:05
  • drop table if exists test2; create table test2 as ( select * from (select DISTINCT ON (address,type_,subtype) address,type_,subtype, geom from buffer_unions where subtype <> 'OutdoorRecreationalFacility' order by address,type_,subtype,st_area(geom)) t1 UNION ALL select address,type_,subtype,geom from buffer_unions where subtype = 'OutdoorRecreationalFacility') – ziggy Jun 20 '17 at 18:05
  • @ziggy ah yes, good catch. The area needs to be sorted Desc though to get rid of the smaller polygons. – JGH Jun 20 '17 at 18:19

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.