Spam, egg, Spam, Spam, bacon and Spam (og hvordan man importerer shapefiler til PostgreSQL og får dem vist i QGIS)
600
post-template-default,single,single-post,postid-600,single-format-standard,bridge-core-3.0.5,qode-page-transition-enabled,ajax_fade,page_not_loaded,,qode-title-hidden,qode-child-theme-ver-1.0.0,qode-theme-ver-29.2,qode-theme-bridge,disabled_footer_top,disabled_footer_bottom,wpb-js-composer js-comp-ver-6.10.0,vc_responsive

Spam, egg, Spam, Spam, bacon and Spam (og hvordan man importerer shapefiler til PostgreSQL og får dem vist i QGIS)

Af Bo Spam Thomsen, AestasGIS, bvt@aestas.dk

Dette blogindlæg er det tredje i rækken hvorledes man kan bruge spatiale databaser i forbindelse med QGIS (Tidligere blogindlæg: https://qgis.dk/spatial-is-not-special-2/, Monkey see, monkey do eller hvordan du installer PostgreSQL på din egen pc )

spam-570x349

I det sidste blogindlæg beskrev jeg hvordan du installerer PostgreSQL databasesystemet på din pc. Men du skal også have læst data ind i din database. Og selvfølgelig få vist og behandlet disse data i QGIS (ellers var der jo ikke noget formål med alle disse blogindlæg på QGIS.DK om PostgreSQL …)

Allerførst skal vi have lavet en database i PostgreSQL systemet. Dette gøres via administrationsprogram PgAdmin III, som fulgte med installationen af PostgreSQL systemet. Programmet er placeret i menu “Start” –> “Alle Programmer” –> “PostgreSQL 9.4” –> “PgAdminIII”:

  1. Når du starter programmet ser du følgende skærmbillede:
    shape_imp_eksp_crea
  2. I den venstre del af skærmbilledet med overskrift “Object browser”, valg-gren “Server Groups” –> “Servers” højreklikker du på server “Postgres 9.4 (localhost:5432)” – Det er din lokale databaseserver. Der vises en lille undermenu med en række valg.
  3. I undermenuen klikker du på “Connect”. Første gang, du forbinder dig til databasen, vises der en logon dialog hvori du skal indtaste et password til user “postgres”. Indtast det password, du oprettede under installationen af PostgreSQL systemet. Og hvis du er doven (som jeg) og ikke særlig sikkerheds-minded, sætter du et kryds i “Store Password”.
    Det røde kryds ud for servernavnet forsvinder nu og valg-grenen folder sig yderligere ud.
  4. Du højreklikker på gren “Databases” under din valgte server, og der vises en lille undermenu.
  5. I undermenuen klikker du på “New database…” (Jep, nu er du i gang med at oprette en database !!) . Der vises følgende menu:
    shape_imp_eksp_new
  6. I felt “Name” skriver du navnet på din database, f.eks. “firstdb”. Og klikker på knap “OK”. Dialogen lukkes og du ser igen på den originale skærmbillede.
  7. I grenen under “Databases” dukker der nu et nyt valg frem: Din database med navnet “firstdb”. Nu har du oprettet din første database !! Men du skal lige foretage en enkelt handling ekstra for at klargøre databasen til at indeholde spatiale data: Installere extension “postgis” i databasen.
  8. Dette gøres ved at højreklikke på database navnet “firstdb” og i undermenuen vælge “Create new object” –> “Create new extension”. Der vises følgende skærmbillede:
    shape_imp_eksp_ext
  9. I drop-down boksen “Name” vælger du “postgis” og klikker derefter på “OK”. Og nu er din database klar til at modtage spatiale data!!

Victorinox_Handyman_1

Der er mange måder og systemer til at få indlæst spatiale data i PostgreSQL/PostGIS. Min favoritmetode er at bruge OGR2OGR, et kommandolinie baseret system der kan sammenlignes med en sweitzerkniv – systemet kan importere, eksportere og behandle spatiale data i et utal af formater (og så er det open-source og gratis). Jeg laver et blogindlæg om OGR2OGR på et senere tidspunkt.

Men til vores umiddelbare formål er det at skyde kanoner af for at ramme en gråspurv. Ved installationen af PostGIS er der samtidig blevet installeret et hjælpeværktøj “PostGIS Shapefile and DBF loader 2.1“. Som navnet antyder, kan programmet benyttes til at importere shape ind i PostGIS. Dette er installeret, så det kan startes inde fra PgAdmin III:

  1. I PgAdmin III markerer du databasen, du vil arbejde med – i dette tilfælde “firstdb”.
  2. Dernæst klikker du på menupunkt “Plugins” –> “PostGIS Shapefile and DBF loader 2.1”. Der vises følgende dialog:
    shape_imp_eksp2
  3.  Tryk på knap: “View connection details…”. Der vises flg. dialog:
    shape_imp_eksp_conn
  4. Check at oplysningerne stemmer (primært database navnet) og tryk på “OK”. Programmet tester forbindelsen til databasen og resultatet vises i notat-felt “Log window” i hoveddialogen (“PostGIS Import/Export Shapefile Manager” ).
    Hvis der står “…. Connection Succeded” er systemet klar til at importere shapefiler.
    Hvis der står noget andet har du sandsynligs markeret den forkerte database i punkt 1.
  5. Tryk på knap “Add File” i hoveddialogen. Der vises en dialog “Select a shape file”, som giver dig mulighed for at vælge den/de shapefil(er) du vil importere til PostgreSQL
  6. Vælg en shape-fil og tryk på “Open”. “Select a shape file” dialoges lukkes, og hoveddialogen vises igen.
  7. Under faneblad “Import” vises der nu en ny række med kolonneværdier:
    – Shapefile: Indeholder navnet på den valgte shape-fil. Kan ikke ændres
    – Schema:
    Navnet på det database-schema, som shape-filen importeres ind i. “public” er standard. Lad være med at ændre dette pt.
    – Table:
    Navnet på den nye tabel. Navnet udledes af shape-fil navnet. Kan ændres, men lad det være i denne omgang.
    – Geo Column:
    Navnet på geometri kolonnen i den ny tabel. Standard er “geom”. Kan ændres, men lad det være i denne omgang.
    – SRID:
    EPSG – nummeret på projektionen for den nye tabel. Er sandsynligvis “0”. Skal ændres til den rigtige kode: 25832 for ETRS89/UTM 32, 25833 for ETRS89/UTM 33, 4326 for WGS84/longitude-latitude.
    – Mode:
    Sat til “create”. Må ikke ændres
    – RM:
    Intet kryds. Må ikke ændres
    Så basalt set skal du ændre SRID til den rigtige projektionskode og lade de andre kolonner være !
  8. Klik på “Import”. Importen går i gang. Når den er færdig, vises status (4-5 linier tekst) for import i notat feltet “Log Window”.

    – Hvis status afsluttes med “Shapefile import completed” er alt ål og rejer og importen gik godt.
    – Hvis status tekst afsluttes med “Shapefile import failed” har du et problem :-/ Hvis statusteksten også indeholder noget med “Illegal byte sequence” er du med stor sandsynlighed ramt ind i et dbf “encoding” problem (Det er basalt set noget med hvorledes æøå repræsenteres inde i shape-attribut filen). Dette kan rettes med følgende:

  9. Tryk på “Options…” i hoveddialogen. Der vises følgende dialog:
    shape_imp_eksp_opt
  10. I feltet “DBF file character encoding” rettes “UTF-8” til “LATIN1” og du trykker på “OK”
  11. Gentag punkt 8.

Whouuw !! Færdig med import af shapefil!  Hvis du nu ikke har en shape-fil at øve dig på, kan du downloade en her: KOMMUNE . Det er et kommunekort for Danmark i ETRS89/UTM32 og i encoding LATIN1. Resten overlades som en øvelse til læseren (Som der stod i min gamle – forhadte – matematikbog)

 

shape_imp_eksp_lyn

Og som rosinen i pølseenden. Hvordan får vi vores  postgreSQL data at se i QGIS ?

Dette er en to-skridts procedure. Først skal vi gøre QGIS bekendt med vores database. Dernæst forbinder vi os til vores postgreSQL tabel, så den vises som et lag i QGIS.

Opret en forbindelse til databasen:

  1. I QGIS klikker du på menupunkt “Layer” –> “Add Layer” –> “Add PostGIS Layers” (NB! Læg mærke til PostGIS – ikke PostgreSQL layers). Der vises følgende dialog:
    shape_imp_eksp_pgnew
  2. Klik på “New”. Der vises følgende dialog:
    shape_imp_eksp_pgconn
  3. I felt “Name” indtastes navnet på forbindelsen. Dette kan være et vilkårligt navn. Normal bruger jeg et navn på formen databasenavn servernavn”, f.eks: “firstdb på localhost”
    I felt “Host” udfyldes navnet på den pc, som PostgreSQL kører på. Dette er altid “localhost” for en lokal postgres server.
    Felt “Port” efterlades på standard værdien “5432”, Felt “Service” udfyldes ikke og felt “SSL mode” efterlades på “disable”
    I felt “Database” indtastes navnet på databasen som vi vil forbinde til, f.eks. “firstdb”.
    I felt “Username” indtastets “postgres” og i felt “Password” indtastes det valgte password for user “postgres”
    Felterne “Save Username” og “Save Password” afkrydses og der klikkes på “Test Connect”. Systemet bør svare med en meddelelse om at “Connection to database firstdb was successfull”.
    Der klikkes på “OK” for at afslutte. Der vises en advarsel om at du har valgt at gemme username og password i fritekst og at dette er en dårlig ide ud fra et sikkehedsmæssigt synspunkt. Det glemmer vi for øjeblikket og trykker på “OK”
  4. I den oprindelige dialog klikkes på “Close”. Du har nu etableret en forbindelse til din postgresql database.

Slutteligt skal du oprette en forbindelse til en tabel og vis den som et lag i QGIS:

  1.  I QGIS klikker du på menupunkt “Layer” –> “Add Layer” –> “Add PostGIS Layers” (Samme menupunkt, som da du oprettede forbindelsen til databasen). Der vises følgende dialog:
    shape_imp_eksp_tbconn
  2. I afsnit “Connections” vælger du “Firstdb på localhost” og klikker på “Connect”
  3. I afsnittet nedenunder, under kolonne “Schema”, vises der en valg-gren med navnet “public”. Denne folder du ud.
  4. Markér linien med “public .. kommune.. geom..” osv. Denne linie repræsenterer din nys indlagte tabel. Har du valgt at indlæse en anden shape-fil end den, jeg har refereret i bloggen, vil der selvfølgelig være et andet navn end “kommune”
  5. Tryk på “Add” og derefter på “Close”. og  Viola!! – dit kortvidue i QGIS viser nu et pænt danmarkskort med kommuneinddelinger (eller hvad du nu har valgt at indlæse)

 

Et lille “Post Scriptum”:  Den opmærsomme og postgresql vidende læser af disse blogindlæg kan have en trang til at varme mine usle rygstykker pga. den totale mangel på opsætning og brug af PostgreSQL sikkerhedsmekanismer i blog-indlæggene. De har fuldstændig ret ! Jeg har sk..t højt og flot på database-sikkerheden: Der er ingen database administrator som ved sine fulde fem ville tillade brugen at postgres superuser til almindeligt brug, endsige opsætte databaserne således at gud og hvermand kan oprette, rette og slette alle databaser og de forskellige tabeller.

Til mit forsvar må jeg sige: Det har på intet tidspunkt været min intention at man via en kort række blogindlæg kunne starte fra “scratch” og ende med et fuldt færdig, produktionsmodent database system. Det er umuligt.

Min kongstanke med indlæggene har derimod været at vise en vej væk fra uhåndterlige bjerge af shape og tab filer placeret mærkelige steder i filsystemer og hen i mod en overskuelig og struktureret metode til opbevaring af spatielle data i en lokal postgresql database. Jeg håber, at det er lykkedes for mig i større eller mindre grad.

Ønsker man større sikkerhed, kan man starte med at google “postgresql security best practices” og begynde at studere de ca. 650.000 søgeresultater, der kommer frem. Eller evt. hyre undertegnede til at opsætte deres Postgresql system, så dette arbejder bedst muligt under de givne forhold og med en praktisk og god sikkerhedsmodel. Jeg kender rumlen !

Næste blogindlæg vil beskrive metoder til at lave en “rough and ready” tuning af Postgres, således det kører rimeligt på den pc, systemet er installeret på. Og samtidigt åbner vi op for databasesystemet, således andre på netværket kan benytte sig af data i databasen. Og så sniger vi en lille smule sikkerhed ind – trods alt….

Bloody Vikings !