I am currently preparing data for Ramon. The idea is to have an easy-to-use QGIS template file with all geographical data he might want to use to analyse, visualise, and present his well-filled treasure trove of demographic data.
My concept foresees a server-side read-only repository of vector datasets, served by WFS and/or PostGIS, which feature the necessary columns to join to the demographic datasets which would remain in the local spread sheet files Ramon is used to keep them in. Using the marvellous PostgreSQL feature MATERIALIZED VIEWS, I stride to keep redundancy at a minimum by disolving the higher-order statistical unit polygons out of the smallest ones used.
While the primary use-pattern would be for Ramon to open one of the supplied template files, discarding any unused layers and starting work from there, I still want him to be able to (re-)add additional layers from the server-side database. What I found a merely cosmetic thing until now started to bother me more than I liked: the QGIS dialog for adding PostGIS layers lists many layers twice, once with geometry column, geometry type and reference system pre-selected, once with input fields for manually specifying them. Ramon is quite tech-savy himself, but still I felt like this is nothing you want to have an end-user having to deal with.
A look at the table properties gave it away: some SELECTs return fully specified geometry column types, some don’t:
wirel=# d+ nuts1_2014;
Materialisierte Sicht „public.nuts1_2014“
Spalte | Typ | Attribute | Speicherung | Statistikziel | Beschreibung -----------+------------------------------+-----------+-------------+---------------+-------------- nuts1 | text | | extended | | nuts1name | character varying(16) | | extended | | geom | geometry(MultiPolygon,31287) | | main | | gkz14 | integer[] | | extended | | wirel=# d+ nuts1_2015; Materialisierte Sicht „public.nuts1_2015“ Spalte | Typ | Attribute | Speicherung | Statistikziel | Beschreibung -----------+------------------------------+-----------+-------------+---------------+-------------- nuts1 | text | | extended | | nuts1name | character varying(16) | | extended | | geom | geometry | | main | | gkz14 | integer[] | | extended | |
The fix was quick and easy: the column of tables can be changed using ALTER TABLE tablename ALTER COLUMN geometrycolumn SET DATA TYPE geometry(GeometryType,SRS-Code). For views, first a consistent geometry type for all rows has to be guaranteed (I chose to add an ST_Multi, which might cost some processing time, but I frankly didn't care about that – materialized views are generated only on forced update), then the geometry column has to be type-cast:
DROP MATERIALIZED VIEW IF EXISTS nuts1_2015; CREATE MATERIALIZED VIEW nuts1_2015 AS SELECT gn.nuts1 AS nuts1, nn.nuts1name AS nuts1name, ST_Multi(ST_Buffer(ST_Collect(g.geom), 0::double precision))::geometry(MultiPolygon,31287) AS geom, array_agg(gn.gkz15) AS gkz15 FROM "gem_at_2015" g LEFT JOIN "gkz15-nuts1" gn ON g.gem_nr=gn.gkz15 LEFT JOIN "nuts1-name" nn ON gn.nuts1=nn.nuts1 GROUP BY gn.nuts1, nn.nuts1name; CREATE INDEX ON nuts1_2015 USING GIST(geom); CLUSTER nuts1_2015 USING nuts1_2015_geom_idx; COMMENT ON MATERIALIZED VIEW nuts1_2015 is 'Österreich: NUTS1-Einheiten (2015)';
Voilà:
