3

I have a property parcel table a in PostGIS that has the following columns/fields

owner_name,
owner_city,
county_name,
state,
owner_address,
owner_mailing_address

I want to dissolve/aggregate the adjoining parcels that have the same owner name, owner_city, and state. I achieved it by using ST_Union and GROUP BY functions. Here is the SQL that I used

  create table b as
select
    owner_name
    , owner_city
    , county_name
    , state
    , ST_Union(ST_Buffer(geom, 0))
from
    a
group by
    owner_name
    , owner_city
    , county_name
    , state;

Is there a way for me to include owner_address and owner_mailing_address in table b without adding them to the aggregate function (GROUP BY)? The SQL should pick the first value it can find for these fields and update them to the two columns in table b. Please note that joining the tables is not a option.

2
  • Is that union on the 0 buffer working for you? I don't think that is actually going to pick up any adjoining parcels... I could be wrong, but you would still end up with parcels across your city belonging to the same owner, rather than parcels that are adjacent. – DPSSpatial_BoycottingGISSE Oct 23 '20 at 18:12
  • I tried different options and this one worked the best. If I understood your question, I do run ST_Dump on the geometry column after the above SQL and it gives me just the adjoining ones merged and any property with same owner name in the same city that is not adjoining is separated out. – P G Nov 2 '20 at 23:38
6

The SQL should pick the first value

To give an accurate answer a definition of what is "first" is needed. I mean, it could be just the first random result returned by the database or it cat be sorted by some criteria.

One easy, probably inaccurate, way of getting a result, is to use an aggregate function in the additional desired columns:

CREATE TABLE b AS
    SELECT
        owner_name, owner_city, county_name, state, ST_Union(ST_Buffer(geom, 0))
        , max(owner_address)
        , max(owner_mailing_address)
    FROM a 
    GROUP BY owner_name, owner_city, county_name, state;

Another approach instead of just getting the value of one random row will be build a string (with string_agg) or an array (with array_agg) that contains all the values of the grouped rows.

CREATE TABLE b AS
    SELECT 
        owner_name
        , owner_city
        , county_name
        , state
        , ST_Union(ST_Buffer(geom, 0))
        , string_agg(owner_address, ' - ')
        , string_agg(owner_mailing_address, ' - ')
    FROM
        a 
    GROUP BY
         owner_name
         , owner_city
         , county_name
         , state
;

Or even better, array_agg and string_agg admit an order by clause to define the order of the items within the array. As example you could use string_agg(owner_address, ' - ' order by gid). In this case I assume that a primary key column called gid exists.

Using the order by clause is easy to define what "first" is and "slice" the array to get only the value of interest.

CREATE TABLE b AS
    SELECT 
        owner_name
        , owner_city
        , county_name
        , state
        , ST_Union(ST_Buffer(geom, 0))
        , (array_agg(owner_address order by gid))[1]
        , (array_agg(owner_mailing_address order by gid))[1]
    FROM
        a 
    GROUP BY
         owner_name
         , owner_city
         , county_name
         , state
;

Probably this query can be more performant or easy to understand using LATERAL, Window Functions or even CTE, but as you said not to use JOIN the presented approach could be valid.

3
  • Thanks! that's what I was looking for. This is very helpful. – P G Nov 2 '20 at 23:40
  • You are welcome @PG. Remeber to mark an answer as accepted if it solves your issue. – Francisco Puga Nov 3 '20 at 8:21
  • 1
    Thank you for mentioning that. I am new to StackExchange so didn't know there is an acceptance feature that I can click. – P G Nov 3 '20 at 21:12
4

If you do not want to include these fields in the GROUP BY clause, you must use an aggregation function in your SELECT clause. I illustrate an example using the array_agg function. (Documentation array_agg function)

For the demonstration, I have created the following table:

enter image description here

Execute the following query by using the array_agg function directly in the SELECT clause

 SELECT owner_name, owner_city, county_name, state, 
 array_agg(owner_address) as array_owner_address, 
 array_agg(owner_mailing_address) as array_owner_mailing_address
 FROM a 
 GROUP BY owner_name, owner_city, county_name, state;

The result is the following (no geometry field for the example):

enter image description here

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.