0

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.

enter image description here

3

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 ST_Union.

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;
1
  • One issue with this approach is that polygons which touch in only a single point will still be merged. This is data-dependent, so may not be a problem. But it would be nice to find a solution which does not have this limitation. – dr_jts Jul 1 '20 at 5:06
0

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.

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.