PostGIS snippets

postgis_snippets.sql

 -- Fix table with wonky 4D geometries and SRID=0
 ALTER TABLE krth.dagi_kommune
   ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, 25832) 
     USING ST_Force2D(ST_SetSRID(geom, 25832));

-- Alter geometry type on table
ALTER TABLE krth.clc_dk
  ALTER COLUMN geom TYPE geometry(MULTIPOLYGON, 25832) USING ST_Force2D(ST_SetSRID(geom, 25832));

-- Materialized view with subset of data
DROP MATERIALIZED VIEW IF EXISTS krth.corine_dk;

CREATE MATERIALIZED VIEW krth.corine_dk AS (
 (WITH vars AS (SELECT ST_Buffer(ST_Transform(ST_SetSRID(ST_Extent(geom), 25832), 3035), 10000) AS bbox FROM krth.dkn)
  SELECT id, objectid, code_12, ST_Transform(geom, 25832)::geometry(MultiPolygon, 25832) AS geom FROM krth.corine
  WHERE ST_Intersects(geom, (SELECT bbox FROM vars)))
) WITH DATA;

-- Materialized view with buffered geometries
DROP MATERIALIZED VIEW IF EXISTS krth.kommune_buffer;

CREATE MATERIALIZED VIEW krth.kommune_buffer (komnavn, komkode, regionkode, geom) AS (
    SELECT k1.komnavn
         , k1.komkode
         , k1.regionkode
         , ST_Multi(ST_Difference( ST_Buffer(ST_Union(k1.geom), 250),
                                   (SELECT ST_Union(k2.geom) FROM krth.dagi_kommune AS k2 WHERE k2.komnavn <> k1.komnavn)
                                 ))::geometry(MultiPolygon, 25832)
      FROM krth.dagi_kommune AS k1
  GROUP BY k1.komnavn, k1.komkode, k1.regionkode
) WITH DATA;

CREATE INDEX kommune_buffer_komnavn_idx
    ON krth.kommune_buffer USING btree (komnavn ASC NULLS LAST) WITH (FILLFACTOR=100)
    TABLESPACE pg_default;

CREATE INDEX kommune_buffer_geom_idx
    ON krth.kommune_buffer USING gist (geom)
    TABLESPACE pg_default;

-- Materialized view removing holes from the above
-- https://geospatial.commons.gc.cuny.edu/2013/11/04/filling-in-holes-with-postgis/
DROP MATERIALIZED VIEW IF EXISTS krth.kommune_buffer_noholes;

CREATE MATERIALIZED VIEW krth.kommune_buffer_noholes (komnavn, komkode, regionkode, geom) AS (
    SELECT sub.komnavn
         , sub.komkode
         , sub.regionkode
         , ST_Collect(St_MakePolygon(sub.geom))::geometry(MultiPolygon, 25832) AS geom
      FROM (
          SELECT komnavn
               , komkode
               , regionkode
               , ST_ExteriorRing((ST_Dump(geom)).geom) AS geom
            FROM krth.kommune_buffer
      ) AS sub
     GROUP BY sub.komnavn, sub.komkode, sub.regionkode
) WITH DATA;
 
CREATE INDEX kommune_buffer_noholes_komnavn_idx
    ON krth.kommune_buffer_noholes USING btree (komnavn ASC NULLS LAST) WITH (FILLFACTOR=100)
    TABLESPACE pg_default;

CREATE INDEX kommune_buffer_noholes_geom_idx
    ON krth.kommune_buffer_noholes USING gist (geom)
    TABLESPACE pg_default;

-- Use of arrays to select multiple values per row
SELECT noholes.komnavn
     , noholes.komkode
     , noholes.regionkode
     , neighbours.neighbours
     , array_to_string(ARRAY(SELECT kom.komnavn FROM krth.kommune_buffer AS kom WHERE kom.komkode = ANY (neighbours.neighbours) ORDER BY kom.komnavn), ', ')
  FROM krth.kommune_buffer_noholes AS noholes
  JOIN krth.kommune_buffer_neighbours AS neighbours ON noholes.komkode = neighbours.komkode
 WHERE noholes.regionkode = '1084'
   AND cardinality(neighbours.neighbours) <> 0;