- SELECT cc as country_code, pc, ST_X(centroid), ST_Y(centroid)
- FROM (SELECT
- COALESCE(plx.country_code, get_country_code(ST_Centroid(pl.geometry))) as cc,
- token_normalized_postcode(pl.address->'postcode') as pc,
- ST_Centroid(ST_Collect(COALESCE(plx.centroid, ST_Centroid(pl.geometry)))) as centroid
- FROM place AS pl LEFT OUTER JOIN placex AS plx ON pl.osm_id = plx.osm_id AND pl.osm_type = plx.osm_type
- WHERE pl.address ? 'postcode' AND pl.geometry IS NOT null
- GROUP BY cc, pc) xx
+ SELECT cc, pc, ST_X(centroid), ST_Y(centroid)
+ FROM (SELECT
+ COALESCE(plx.country_code,
+ get_country_code(ST_Centroid(pl.geometry))) as cc,
+ pl.address->'postcode' as pc,
+ COALESCE(plx.centroid, ST_Centroid(pl.geometry)) as centroid
+ FROM place AS pl LEFT OUTER JOIN placex AS plx
+ ON pl.osm_id = plx.osm_id AND pl.osm_type = plx.osm_type
+ WHERE pl.address ? 'postcode' AND pl.geometry IS NOT null) xx