I have a table of polygons (land parcels in a city) and I am trying to identify "chains" of 5 or more adjacent polygons. For example, if each letter is a polygon and each . is empty space, AB.CD.EFG.HIJKL.MNOP.QRSTU.VWXYZ then I want HIJKL, QRSTU and VWXYZ. In real life they aren't in a line like that of course.
The messy solution I used for a first attempt is this query:
SELECT p1.parcel_c as "Parcel 1", p2.parcel_c as "Parcel 2", p3.parcel_c as "Parcel 3", p4.parcel_c as "Parcel 4", p5.parcel_c as "Parcel 5", ST_Union(array[p1.geom, p2.geom, p3.geom, p4.geom, p5.geom]) as merged_geom FROM abandonded_vacant_lots_table p1 INNER JOIN abandonded_vacant_lots_table as p2 ON ST_TOUCHES(p1.geom, p2.geom) INNER JOIN abandonded_vacant_lots_table as p3 ON ST_TOUCHES(p2.geom, p3.geom) INNER JOIN abandonded_vacant_lots_table as p4 ON ST_TOUCHES(p3.geom, p4.geom) INNER JOIN abandonded_vacant_lots_table as p5 ON ST_TOUCHES(p4.geom, p5.geom) WHERE p1.parcel_c not in(p2.parcel_c, p3.parcel_c, p4.parcel_c, p5.parcel_c) and p2.parcel_c not in(p1.parcel_c, p3.parcel_c, p4.parcel_c, p5.parcel_c) and p3.parcel_c not in(p1.parcel_c, p2.parcel_c, p4.parcel_c, p5.parcel_c) and p4.parcel_c not in(p1.parcel_c, p2.parcel_c, p3.parcel_c, p5.parcel_c) and p5.parcel_c not in(p1.parcel_c, p2.parcel_c, p3.parcel_c, p4.parcel_c)
As you can see this is clumsy and inelegant and I suspect inefficient. It also returns a lot of almost duplicates when there is a chain of more than 5 parcels, eg if the parcels are ABCDEF it returns ABCDE and BCDEF when a better solution would return the longest chain, ABCDEF. How can I make this query more efficient, more elegant and in general more versatile?
أكثر...
The messy solution I used for a first attempt is this query:
SELECT p1.parcel_c as "Parcel 1", p2.parcel_c as "Parcel 2", p3.parcel_c as "Parcel 3", p4.parcel_c as "Parcel 4", p5.parcel_c as "Parcel 5", ST_Union(array[p1.geom, p2.geom, p3.geom, p4.geom, p5.geom]) as merged_geom FROM abandonded_vacant_lots_table p1 INNER JOIN abandonded_vacant_lots_table as p2 ON ST_TOUCHES(p1.geom, p2.geom) INNER JOIN abandonded_vacant_lots_table as p3 ON ST_TOUCHES(p2.geom, p3.geom) INNER JOIN abandonded_vacant_lots_table as p4 ON ST_TOUCHES(p3.geom, p4.geom) INNER JOIN abandonded_vacant_lots_table as p5 ON ST_TOUCHES(p4.geom, p5.geom) WHERE p1.parcel_c not in(p2.parcel_c, p3.parcel_c, p4.parcel_c, p5.parcel_c) and p2.parcel_c not in(p1.parcel_c, p3.parcel_c, p4.parcel_c, p5.parcel_c) and p3.parcel_c not in(p1.parcel_c, p2.parcel_c, p4.parcel_c, p5.parcel_c) and p4.parcel_c not in(p1.parcel_c, p2.parcel_c, p3.parcel_c, p5.parcel_c) and p5.parcel_c not in(p1.parcel_c, p2.parcel_c, p3.parcel_c, p4.parcel_c)
As you can see this is clumsy and inelegant and I suspect inefficient. It also returns a lot of almost duplicates when there is a chain of more than 5 parcels, eg if the parcels are ABCDEF it returns ABCDE and BCDEF when a better solution would return the longest chain, ABCDEF. How can I make this query more efficient, more elegant and in general more versatile?
أكثر...