I have a spatial table in PostGIS that has millions of land parcels. The parcels have ownership information and also a column that specifies whether the parcel is private or public. I would like to merge/dissolve only the private parcels that have the same owner name and share a boundary (adjacent parcels owned by same owner) but also keep the private parcels that were not dissolved.Below is a sample table. Based on this table I would like to dissolve parcels with id 2,3, and 4 into a single parcel (assuming they are adjacent) but also keep parcel with id 1. I tired using ST_Union but when I specify the union on column owner_name, all the parcels (including parcel id =1) just have one column owner_name in the final table. I would like to keep all the columns for the parcels that did not merge/dissolve and keep only owner_name for the merged/dissolved parcel. So parcel with id 1 should have all the columns and the merged parcel from 2,3,4 should have only owner_name. How can I achieve this? Ideally I will like to keep all the resultant data in one table.
One way to do this is to cluster the owner polygon groups using
ST_ClusterDBSCAN (as suggested here).
By clustering with a very small distance value, each cluster should contain only adjacent polygons, or a single disjoint polygon. Then the clusters can be merged using
Counting the number of records forming each merged polygon allows distinguishing between single and multiple parentage. Other column values can be preserved by using the
MAX aggregate (in absence of the more appropriate
FIRST aggregate function).
Here's a synthetic example showing the approach:
WITH parcels AS ( SELECT i id, 'Name ' || i AS owner, CASE j WHEN 0 THEN ST_MakeEnvelope(0,i, 1,i+1) WHEN 1 THEN ST_MakeEnvelope(1,i+0.5, 2,i+1.5) WHEN 2 THEN ST_MakeEnvelope(3,i, 4,i+1) WHEN 3 THEN ST_MakeEnvelope(5,i, 6,i+1) END geom FROM generate_series(0, 5) t(i) CROSS JOIN generate_series(0, 3) s(j) ), cluster AS ( SELECT *, ST_ClusterDBSCAN(geom::geometry, 1e-10, 1) OVER (PARTITION BY owner) AS cluster_id, FROM parcels ) SELECT MAX(id) id, owner, COUNT(*) size, ST_Union(geom) geom FROM cluster GROUP BY owner, cluster_id;
As I am new to spatial SQLs, I was not able to use the above mentioned method. I ended up using a longer approach but that definitely worked. If there is someone like me who is not very well versed with spatial SQLs they can try this approach. Here is how I tackled this problem:
First I created a table from the existing table where for_symbol = 'PRIVATE PROPERTY'
CREATE TABLE b AS SELECT * FROM original table name WHERE for_symbol = 'PRIVATE PROPERTY'
Next I added a field for updating the count of owner_name. This gave me the number of times an owner was repeated
ALTER TABLE b ADD column count_number int;
and updated with the following SQL
with counted as ( select owner_name, count(*) as name_count from b group by owner_name ) update b set "count_number" = c.name_count from counted c where c.owner_name = b.owner_name;
Next I created two tables from table b, one where count_number >1 (call this table c) and other where count_number = 1 (call this table d). I merged the parcels using ST_Union for table c (because owners are repeating in this table). I also used ST_Dump to convert multi-part parcels to single part. Here are the SQLs for that
CREATE TABLE c AS SELECT owner_name, ST_Union(geom) FROM b WHERE count_number > 1 GROUP BY owner_name;
For table d I used the following SQL
CREATE TABLE d AS SELECT * FROM b WHERE count_number = 1;
Next, I used ST_Dump to convert polygons in table c to single part form multi-part. I had to create a new table for that (let's call this table as e)
CREATE TABLE e AS SELECT c.owner_name, (ST_Dump(c.st_union)).geom AS geom FROM c;
Next step was to combine/merge the two tables (d and e) into a single table. Make sure the columns of both the tables is same and the order of the columns is also same. So you will need to add the columns in table e that match columns in table d. Otherwise the two tables will not merge if the columns and their order is not same. If your order is not same, you can specify the order in the SQL and it should work. Here are the SQLs
For adding columns in table e
ALTER TABLE e ADD gid int ADD apn varchar, ADD owner_zip_code varchar, ADD city varchar, ADD state varchar, ADD county varchar, ADD for_symbol varchar;
For merging the two tables into a single table
CREATE TABLE parcel_final AS (SELECT gid,owner_name,apn, owner_zip_code, city, state, county, for_symbol FROM d UNION SELECT gid,owner_name,apn, owner_zip_code, city, state, county, for_symbol FROM e);
I am aware that this is not an ideal approach, however I just posted this solution here so that it might help someone like me who is almost a beginner level SQL expert.