How to include non aggregate columns in select function if not including them in aggregate function (like GROUP BY) in PostGIS table

Question

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.

Francisco Puga · Accepted Answer

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.

Vincent Bré · Answer

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:

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):

How to include non aggregate columns in select function if not including them in aggregate function (like GROUP BY) in PostGIS table

2 Answers

Add your own answers!

Ask a Question