Ympäristöministeriö / Rakentaminen ja maankäyttö

PostgreSQL/PostGIS -tietokanta

Yleis- ja asemakaavan tietomallit -projektissa laadittiin loogisen tason Kaavatietomallin mukainen PostgreSQL-tietokanta Kaava-JSON-formaatissa olevien kaavojen testaamiseksi. Testauksessa käytettiin PostgreSQL-tietokannanhallintajärjestelmän versiota 13. Valittu tietokantarakenne perustui siihen, että kaavatiedot tallennetaan GeoJSON-formaatissa, ja siinä hyödynnettiin PostgreSQL:n PostGIS-geometriatukea ja JSON-tukea. Testauksessa käytetty aineisto on kuvattu omalla sivullaan.

Tietokantauluina mallinnettiin seuraavat loogisen tietomallin luokat:

Lisäksi kantaan luotiin taulu feature_collection, johon tallennettiin kokonainen kaava kaikkine kaavakohteineen ja -määräyksineen.

Tietokantaan ei mallinnettu seuraavia testauksen ulkopuolelle jätettyjä loogisen tietomallin luokkia:

Pois jätettyjen luokkien mallintamisessa ei nähdä mitään erityisiä haasteita, mutta niiden mukaisten testausaineistojen tuottaminen käsin käytössä olleista kaava-aineistoista ei ollut mahdollista projektin aikataulun puitteissa. Loogisen tietomallin tietotyypit (DataType-stereotyyppi) mallinnettiin osana luokkia, jota sisältävät niihin kohdistuvia assosiaatioita.

Tietokannan, taulujen ja näkymien luomiseen käytetyn luontiskriptin avulla voidaan alustaa PostgreSQL-tietokanta tauluineen ja näkymineen.

Luokkien mallinnus relaatioina

Kaikki neljä tietokantataulua mallinnettiin täsmälleen samalla tavalla: taulujen sarakkeiksi valittiin

Geometriatietoa lukuunottamatta kaikki loogisen tietomallin mukainen tietosisältö kuvattiin siis suoraan JSONB-saraketyypin avulla, jolloin kunkin kohteen tietorakennetta ja sisältötyyppejä ei tarvitse kuvata taulurakenteessa yksityiskohtaisesti, vaan JSON-sisältö tallennetaan sellaisenaan sarakkeen arvoksi. Tämä vastaa karkeasti JSON-tiedon tallentamista ns. NoSQL-tietokannoissa (esim. MongoDB), mutta tarjoaa PostGIS:n tehokkaan geometriakäsittelyn.

JSON-kyselyihin perustuvat näkymät

PostgreSQL:n JSON-kyselyfuntiot mahdollistavat tietokantakyselyt myös JSONB-sarakkeiden sisärakenteeseen, mitä hyödynnettiin nostamalla kunkin kohdetyypin Kaava-JSON -tietorakennettava vastaavat tiedot näkymien sarakkeiksi seuraavasti:

CREATE VIEW public.v_spatial_plan AS
        SELECT
            id,
            created_time AS "_createdTime",
            properties::jsonb->>'identityId' AS "identityId",
            properties::jsonb->>'versionId' AS "versionId",
            properties::jsonb->>'localId' AS "localId",
            properties::jsonb->>'latestChange' AS "latestChange",
            properties::jsonb->>'planId' AS "planId",
            properties::jsonb->'planType'->>'code' AS "planTypeCode",
            properties::jsonb->'planType'->'title'->>'fin' AS "planTypeTitle",
            properties::jsonb->'name'->>'fin' AS "name",
            properties::jsonb->'description'->>'fin' AS "description",
            properties::jsonb->'planObjects' AS "planObjects",
            properties::jsonb->'generalRegulations' AS "generalRegulations",
            properties::jsonb->'administrativeAreaIds' AS "administrativeAreaIds",
            properties::jsonb->'legalEffectiveness'->>'code' AS "legalEffectivenessCode",
            properties::jsonb->'legalEffectiveness'->'title'->>'fin' AS "legalEffectivenessTitle",
            properties::jsonb->'usedInputDatasets' AS "usedInputDatasets",
            properties::jsonb->'responsibleOrganisation'->>'linkedFeatureType' AS "responsibleOrganisationLinkedFeatureType",
            properties::jsonb->'responsibleOrganisation'->>'linkedFeatureId' AS "responsibleOrganisationLinkedFeatureId",
            properties::jsonb->'responsibleOrganisation'->>'href' AS "responsibleOrganisationHref",
            properties::jsonb->'responsibleOrganisation'->'title'->>'fin' AS "responsibleOrganisationTitle",
            properties::jsonb->'undergroundness'->>'code' AS "undergroundnessCode",
            properties::jsonb->'undergroundness'->'title'->>'fin' AS "undergroundnessTitle",
            properties::jsonb->'planners' AS "planners",
            properties::jsonb->'planCommentary'->>'linkedFeatureType' AS "planCommentaryLinkedFeatureType",
            properties::jsonb->'planCommentary'->>'linkedFeatureId' AS "planCommentaryLinkedFeatureId",
            properties::jsonb->'planCommentary'->>'href' AS "planCommentaryHref",
            properties::jsonb->'attachments' AS "attachments",
            properties::jsonb->'cancellations' AS "cancellations",
            properties::jsonb->'lifecycleStatus'->>'code' AS "lifecycleStatusCode",
            properties::jsonb->'lifecycleStatus'->'title'->>'fin' AS "lifecycleStatusTitle",
            properties::jsonb->'digitalOrigin'->>'code' AS "digitalOriginCode",
            properties::jsonb->'digitalOrigin'->'title'->>'fin' AS "digitalOriginTitle",
            properties::jsonb->>'metadata' AS "metadata",
            properties::jsonb->>'initiationTime' AS "initiationTime",
            properties::jsonb->>'approvalTime' AS "approvalTime",
            properties::jsonb->>'validFrom' AS "validFrom",
            geom
        FROM data.spatial_plan;
CREATE VIEW public.v_plan_regulation_object AS
        SELECT
            id,
            created_time AS "_createdTime",
            properties::jsonb->>'identityId' AS "identityId",
            properties::jsonb->>'versionId' AS "versionId",
            properties::jsonb->>'localId' AS "localId",
            properties::jsonb->>'latestChange' AS "latestChange",
            properties::jsonb->'spatialPlan'->>'linkedFeatureType' AS "spatialPlanLinkedFeatureType",
            properties::jsonb->'spatialPlan'->>'linkedFeatureId' AS "spatialPlanLinkedFeatureId",
            properties::jsonb->'spatialPlan'->>'href' AS "spatialPlanHref",
            properties::jsonb->'verticalLimits' AS "verticalLimits",
            properties::jsonb->'bindingnessOfLocation'->>'code' AS "bindingnessOfLocationCode",
            properties::jsonb->'bindingnessOfLocation'->'title'->>'fin' AS "bindingnessOfLocationTitle",
            properties::jsonb->'undergroundness'->>'code' AS "undergroundnessCode",
            properties::jsonb->'undergroundness'->'title'->>'fin' AS "undergroundnessTitle",
            properties::jsonb->'regulations' AS "regulations",
            properties::jsonb->'lifecycleStatus'->>'code' AS "lifecycleStatusCode",
            properties::jsonb->'lifecycleStatus'->'title'->>'fin' AS "lifecycleStatusTitle",
            properties::jsonb->>'validFrom' AS "validFrom",
            properties::jsonb->'groundRelativePosition'->>'code' AS "groundRelativePositionCode",
            properties::jsonb->'groundRelativePosition'->'title'->>'fin' AS "groundRelativePositionTitle",
            geom
        FROM data.plan_regulation_object;
CREATE VIEW public.v_plan_regulation AS
        SELECT
            id,
            created_time AS "_createdTime",
            properties::jsonb->>'identityId' AS "identityId",
            properties::jsonb->>'versionId' AS "versionId",
            properties::jsonb->>'localId' AS "localId",
            properties::jsonb->>'latestChange' AS "latestChange",
            properties::jsonb->'spatialPlan'->>'linkedFeatureType' AS "spatialPlanLinkedFeatureType",
            properties::jsonb->'spatialPlan'->>'linkedFeatureId' AS "spatialPlanLinkedFeatureId",
            properties::jsonb->'spatialPlan'->>'href' AS "spatialPlanHref",
            properties::jsonb->'targetObject'->>'linkedFeatureType' AS "targetObjectLinkedFeatureType",
            properties::jsonb->'targetObject'->>'linkedFeatureId' AS "targetObjectLinkedFeatureId",
            properties::jsonb->'targetObject'->>'href' AS "targetObjectHref",
            properties::jsonb->'type'->> 'code' AS "typeCode",
            properties::jsonb->'type'->'title'->> 'fin' AS "typeTitle",
            properties::jsonb->'themes' AS "themes",
            properties::jsonb->'values' AS "values",
            properties::jsonb->'lifecycleStatus'->>'code' AS "lifecycleStatusCode",
            properties::jsonb->'lifecycleStatus'->'title'->>'fin' AS "lifecycleStatusTitle",
            properties::jsonb->>'validFrom' AS "validFrom",
            geom
        FROM data.plan_regulation;