From 39658412ab12f332446f1ca8acad37a8255b0009 Mon Sep 17 00:00:00 2001 From: Guilhem Moulin Date: Sun, 27 Oct 2024 23:14:54 +0100 Subject: PostgreSQL: Add NOT NULL constraints on the geometry columns. Among other things this allows CLUSTERing on the GIST indices, cf. https://postgis.net/docs/manual-3.3/performance_tips.html#database_clustering --- schema.sql | 106 +++++++++++++++++++++++++++++----------------------------- webmap-import | 3 ++ 2 files changed, 56 insertions(+), 53 deletions(-) diff --git a/schema.sql b/schema.sql index edae7b7..14260ca 100644 --- a/schema.sql +++ b/schema.sql @@ -111,7 +111,7 @@ CREATE TABLE postgis.dammar ( "RV" double precision, "Kommentar" character varying(254), "XX_Distance" double precision, - wkb_geometry postgis.geometry(Point,3006) + wkb_geometry postgis.geometry(Point,3006) NOT NULL ); @@ -153,7 +153,7 @@ CREATE TABLE postgis.gigafactories ( ogc_fid bigint NOT NULL, "Name" character varying(254), "Url" character varying(254), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -196,7 +196,7 @@ CREATE TABLE postgis.kommunyta ( objektidentitet uuid, skapad timestamp without time zone, kommunkod smallint NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -239,7 +239,7 @@ CREATE TABLE postgis.lansyta ( objektidentitet uuid, skapad timestamp without time zone, lanskod smallint NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -285,7 +285,7 @@ CREATE TABLE postgis."mrr:bearbetningskoncessioner_applied" ( "ApplicationDate" date NOT NULL, "DiaryNr" character(16) NOT NULL, "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -332,7 +332,7 @@ CREATE TABLE postgis."mrr:bearbetningskoncessioner_approved" ( "ValidTo" date NOT NULL, "DiaryNr" character(16), "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -376,7 +376,7 @@ CREATE TABLE postgis."mrr:markanvisningar" ( "DecisionDate" date, "DiaryNr" character(16), "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -422,7 +422,7 @@ CREATE TABLE postgis."mrr:mineral_applied" ( "ApplicationDate" date NOT NULL, "DiaryNr" character(16) NOT NULL, "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -470,7 +470,7 @@ CREATE TABLE postgis."mrr:mineral_approved" ( "ValidTo" date NOT NULL, "DiaryNr" character(16), "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -516,7 +516,7 @@ CREATE TABLE postgis."mrr:olja_gas_diamant_applied" ( "ApplicationDate" date NOT NULL, "DiaryNr" character(16) NOT NULL, "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -564,7 +564,7 @@ CREATE TABLE postgis."mrr:olja_gas_diamant_approved" ( "ValidTo" date NOT NULL, "DiaryNr" character(16), "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -611,7 +611,7 @@ CREATE TABLE postgis."mrr:torvkoncessioner" ( "ValidTo" date NOT NULL, "DiaryNr" character(16), "LastUpdated" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -658,7 +658,7 @@ CREATE TABLE postgis."nva:Naturvardsverket_Lansstyrelse" ( "FASTBET" character varying(64), "DATSTART" date NOT NULL, "DATSLUT" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -709,7 +709,7 @@ CREATE TABLE postgis."nva:Skogsstyrelsen" ( "DatAvtal" date NOT NULL, "Url" character varying(254) NOT NULL, "Undertyp" character varying(64), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -752,7 +752,7 @@ CREATE TABLE postgis."nvr:Biosfarsomraden" ( "SKYDDSTYP" character varying(32) NOT NULL, "NAMN" character varying(32) NOT NULL, "LINK" character varying(254) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -802,7 +802,7 @@ CREATE TABLE postgis."nvr:Biotopskydd" ( "Standort" character varying(254), "Datbeslut" date, "Url" character varying(254), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -859,7 +859,7 @@ CREATE TABLE postgis."nvr:Djur_och_vaxtskyddsomrade" ( "DIARIENR" character varying(62), "LAGRUM" character varying(62) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -901,7 +901,7 @@ CREATE TABLE postgis."nvr:HELCOM" ( ogc_fid bigint NOT NULL, "BSPA_ID" integer NOT NULL, "NAME" character varying(62) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygonZ,3006) + wkb_geometry postgis.geometry(MultiPolygonZ,3006) NOT NULL ); @@ -958,7 +958,7 @@ CREATE TABLE postgis."nvr:Interimistiskt_forbud" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1015,7 +1015,7 @@ CREATE TABLE postgis."nvr:Kultureservat" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1072,7 +1072,7 @@ CREATE TABLE postgis."nvr:Landskapsbildsskyddsomrade" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16), "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1129,7 +1129,7 @@ CREATE TABLE postgis."nvr:Nationalpark" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1183,7 +1183,7 @@ CREATE TABLE postgis."nvr:Naturminne_punkt" ( "DIARIENR" character varying(62), "LAGRUM" character varying(64) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPoint,3006) + wkb_geometry postgis.geometry(MultiPoint,3006) NOT NULL ); @@ -1240,7 +1240,7 @@ CREATE TABLE postgis."nvr:Naturminne_yta" ( "DIARIENR" character varying(62), "LAGRUM" character varying(64) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1297,7 +1297,7 @@ CREATE TABLE postgis."nvr:Naturreservat" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16), "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1354,7 +1354,7 @@ CREATE TABLE postgis."nvr:Naturvardsomrade" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1399,7 +1399,7 @@ CREATE TABLE postgis."nvr:OSPAR" ( "MPA_ID" character varying(16) NOT NULL, "MPA_NAMN" character varying(64) NOT NULL, "N2000_SITE" character varying(16) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygonZ,3006) + wkb_geometry postgis.geometry(MultiPolygonZ,3006) NOT NULL ); @@ -1456,7 +1456,7 @@ CREATE TABLE postgis."nvr:Ovrigt_biotopskyddsomrade" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16) NOT NULL, "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1507,7 +1507,7 @@ CREATE TABLE postgis."nvr:Ramsar" ( "URSPR_BESL" date NOT NULL, "SEN_BESLUT" date, "LEGAL_ACT" character varying(254) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1560,7 +1560,7 @@ CREATE TABLE postgis."nvr:SCI_Rikstackande" ( "KARAKTAR" character varying(254) NOT NULL, "ARTER" character varying(254), "NATURTYPER" character varying(254), - wkb_geometry postgis.geometry(MultiPolygonZ,3006) + wkb_geometry postgis.geometry(MultiPolygonZ,3006) NOT NULL ); @@ -1613,7 +1613,7 @@ CREATE TABLE postgis."nvr:SPA_Rikstackande" ( "KARAKTAR" character varying(254) NOT NULL, "ARTER" character varying(254) NOT NULL, "NATURTYPER" character varying(254), - wkb_geometry postgis.geometry(MultiPolygonZ,3006) + wkb_geometry postgis.geometry(MultiPolygonZ,3006) NOT NULL ); @@ -1662,7 +1662,7 @@ CREATE TABLE postgis."nvr:Tilltradesforbud" ( "BESKRIVN" character varying(254), "OBJEKTNAMN" character varying(254) NOT NULL, "FORSKRNAMN" character varying(254), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1703,7 +1703,7 @@ ALTER SEQUENCE postgis."nvr:Tilltradesforbud_ogc_fid_seq" OWNED BY postgis."nvr: CREATE TABLE postgis."nvr:Varldsarv" ( ogc_fid bigint NOT NULL, "NAMN" character varying(64) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1762,7 +1762,7 @@ CREATE TABLE postgis."nvr:Vattenskyddsomrade" ( "DIARIENR" character varying(62), "LAGRUM" character varying(16), "BESLMYND" character varying(32) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1811,7 +1811,7 @@ CREATE TABLE postgis."ren:omr_riks" ( "AKTUALITET" date NOT NULL, "SIGNATUR" character varying(25), "GlobalID" uuid NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1856,7 +1856,7 @@ CREATE TABLE postgis."ren:riks_ren" ( "SIGNATUR" character varying(25) NOT NULL, "LANK" character varying(254), "GlobalID" uuid NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1909,7 +1909,7 @@ CREATE TABLE postgis."ri:friluftsliv" ( "NATURTYP" character varying(127), "AREA_LAND_" double precision, "AREA_VATTE" double precision, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -1957,7 +1957,7 @@ CREATE TABLE postgis."ri:naturvard" ( "BESLUTSDAT" date NOT NULL, "ORGINALID" character varying(16) NOT NULL, "RIKSID" integer NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2007,7 +2007,7 @@ CREATE TABLE postgis."ri:obruten_kust" ( "OBJTYP" character varying(254), "OBJEKTLANK" character varying(254), "REFERENS" character varying(254), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2055,7 +2055,7 @@ CREATE TABLE postgis."ri:obrutet_fjall" ( "REVDATUM" date, "OBJEKTLANK" character varying(254), "REFERENS" character varying(254), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2104,7 +2104,7 @@ CREATE TABLE postgis."ri:rorligt_friluftsliv" ( "ANM" character varying(254), "OBJEKTLANK" character varying(254), "REFERENS" character varying(254), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2154,7 +2154,7 @@ CREATE TABLE postgis."ri:skyddade_vattendrag" ( "DIG_SKALA" integer, "OBJEKTLANK" character varying(254), "REFERENS" character varying(254), - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2202,7 +2202,7 @@ CREATE TABLE postgis."sametinget:betesomraden" ( "BY_OMR" boolean NOT NULL, "OVR_OMR" boolean NOT NULL, "AKTUALITET" date NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2258,7 +2258,7 @@ CREATE TABLE postgis."sametinget:flyttled" ( "FAST_LED" boolean NOT NULL, "AKTUALITET" character varying(10), "GlobalID" uuid NOT NULL, - wkb_geometry postgis.geometry(MultiLineString,3006) + wkb_geometry postgis.geometry(MultiLineString,3006) NOT NULL ); @@ -2312,7 +2312,7 @@ CREATE TABLE postgis."sks:AvverkAnm" ( "AvvSasong" character varying(254) NOT NULL, "AvvHa" real, "AvverkningsanmalanKlass" character varying(254) NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2366,7 +2366,7 @@ CREATE TABLE postgis."sks:UtfordAvverk" ( "Forebild" character varying(62), "Efterbild" character varying(62), "ArealHa" real NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2408,7 +2408,7 @@ CREATE TABLE postgis."svk:ledningar" ( ogc_fid bigint NOT NULL, "Placement" character varying(32), "Voltage" integer, - wkb_geometry postgis.geometry(MultiLineString,3006) + wkb_geometry postgis.geometry(MultiLineString,3006) NOT NULL ); @@ -2448,7 +2448,7 @@ ALTER SEQUENCE postgis."svk:ledningar_ogc_fid_seq" OWNED BY postgis."svk:ledning CREATE TABLE postgis."svk:stationsomraden" ( ogc_fid bigint NOT NULL, - wkb_geometry postgis.geometry(MultiPolygonZ,3006) + wkb_geometry postgis.geometry(MultiPolygonZ,3006) NOT NULL ); @@ -2488,7 +2488,7 @@ ALTER SEQUENCE postgis."svk:stationsomraden_ogc_fid_seq" OWNED BY postgis."svk:s CREATE TABLE postgis."svk:stolpar" ( ogc_fid bigint NOT NULL, - wkb_geometry postgis.geometry(PointZ,3006) + wkb_geometry postgis.geometry(PointZ,3006) NOT NULL ); @@ -2531,7 +2531,7 @@ CREATE TABLE postgis."svk:transmissionsnatsprojekt" ( "Name" character varying(254), "Voltage" integer, "Url" character varying(254), - wkb_geometry postgis.geometry(MultiLineString,3006) + wkb_geometry postgis.geometry(MultiLineString,3006) NOT NULL ); @@ -2604,7 +2604,7 @@ CREATE TABLE postgis."vbk:havsbaserad_vindkraft" ( "ElNamn" character varying(62) NOT NULL, "SenasteUppdaterat" date NOT NULL, "Raderat" boolean NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2659,7 +2659,7 @@ CREATE TABLE postgis."vbk:projekteringsomraden" ( "SenasteUppdaterat" date, "EjAktuell" boolean NOT NULL, "Raderat" boolean NOT NULL, - wkb_geometry postgis.geometry(MultiPolygon,3006) + wkb_geometry postgis.geometry(MultiPolygon,3006) NOT NULL ); @@ -2721,7 +2721,7 @@ CREATE TABLE postgis."vbk:vindkraftverk" ( "SenasteUppdaterat" date, "EjAktuell" boolean, "Raderat" boolean NOT NULL, - wkb_geometry postgis.geometry(Point,3006) + wkb_geometry postgis.geometry(Point,3006) NOT NULL ); diff --git a/webmap-import b/webmap-import index 00c4756..8311c26 100755 --- a/webmap-import +++ b/webmap-import @@ -593,6 +593,9 @@ def createOutputLayer(ds, layername, srs=None, options=None): lyr = dso.CreateLayer(layername, **kwargs) if lyr is None: raise Exception(f'Could not create destination layer "{layername}"') + # TODO use CreateLayerFromGeomFieldDefn() from ≥v3.9 as it's not + # possible to toggle the geomfield's nullable property after fact + # otherwise fields = options['fields'] if len(fields) > 0 and not lyr.TestCapability(ogr.OLCCreateField): -- cgit v1.2.3