SQL: How to replace values in a union of conflicting types

Question

I have a single table in Postgres (with PostGIS) in which I have a few thousand rows, each with the name(text), code(text), city(text) and geom(geometry) of a statistical area unit (like a county). The geometries tessellate perfectly.

I'm trying to write a single query that will select all the rows that meet some criteria, and aggregate the rest into a single row. For that single row, I'm not interested in the name and code, just the aggregated geom. E.g., something like this:

 code | name               |  geom
------+--------------------+---------
 102  | Central Coast      | geo...
 115  | Sydney - Baulkham  | geo...
 116  | Sydney - Blacktown | geo...
 117  | Sydney - City      | geo...
 118  | Sydney - Eastern   | geo...
 000  | Remaining Counties | geo... <---Second SELECT just to get this row

I used this answer to come up with the following:

SELECT code, name, ST_Force2D(geom) FROM mytable WHERE mytable.city = 'Greater Sydney' UNION 
SELECT 
  CASE WHEN count(code) > 0 THEN '0' ELSE 'x' END AS code, 
  CASE WHEN count(name) > 0 THEN 'Rest of Sydney' ELSE 'x' END AS name, 
  ST_Collect(geom) 
FROM mytable WHERE mytable.city <> 'Greater Sydney';

This seems like a really roundabout and unclear way of accomplishing something pretty simple. Is there a better way to do this?

score 1 · Accepted Answer · answered May 03 '16 at 20:29

You can hard-code what you want those cells to populate with. I believe in postgres you do this with ticks:

SELECT code, name, ST_Force2D(geom) 
FROM mytable 
WHERE mytable.city = 'Greater Sydney'

UNION 

SELECT '0', 'Remaining Countries', ST_Collect(geom) 
FROM mytable 
WHERE mytable.city <> 'Greater Sydney';

The answer you found compensates for zero values and replaces them with X. If you prefer to see Xs in that case, you can do it that way instead. Seems unnecessary to me.

score 0 · Answer 2 · answered May 03 '16 at 20:40

I don't know if this would work in PostGreSQL since I haven't worked with it in years and never used PostGIS, but you could try:

SELECT
    CASE WHEN city = 'Greater Sydney' THEN code ELSE '000' END AS code,
    CASE WHEN city = 'Greater Sydney' THEN name ELSE 'Remaining Counties' END AS name,
    ST_Collect(geom) AS geom
FROM
    MyTable
GROUP BY
    CASE WHEN city = 'Greater Sydney' THEN code ELSE '000' END,
    CASE WHEN city = 'Greater Sydney' THEN name ELSE 'Remaining Counties' END

Since ST_Collect is an aggregate function, if it aggregates over a single row then it should just return the geometry of that one row anyway. You could enclose that in ST_Force2d if that's necessary, but I'm not sure that it is.

SQL: How to replace values in a union of conflicting types

2 Answers2