Forskjell mellom versjoner av «Urbgeo/gisdb ver 0.0»

Fra hbrgeo
 
(2 mellomliggende revisjoner av samme bruker vises ikke)
Linje 883: Linje 883:
 
</pre>
 
</pre>
  
Programmet pgsql2shp vil lage filene ub_point.shp, ub_point.dbf, ub_point.shx, ub_point.prj og ub_point.cfg. Alle fem filene må flyttast til ArcGIS-området for shapefiler.
+
Programmet pgsql2shp vil lage filene ub_point.shp, ub_point.dbf, ub_point.shx, ub_point.prj og ub_point.cfg. Programmet pgsql2shp vil skrive filene til lokalt filsystem. Ved bruk må alle fem filene må flyttast til ArcGIS-området for shapefiler. Se nedenfor for data og import av data
 
 
Last ned ub_pointz.zip [http://solli.h.uib.no/urbgeo/export/ub_pointz.zip ub_pointz.zip]
 
  
 
===Eksport ved database dump===
 
===Eksport ved database dump===
Linje 901: Linje 899:
 
=Import av data=
 
=Import av data=
 
==Import til ArcGIS==
 
==Import til ArcGIS==
 +
Urbgeo har fire typer dataobjekt:
 +
*Vektordata som beskriver geodata, punkt, linjer og polygon. Denne versjon har bare punktdata
 +
*Koplingsdata: Språklige referanser til geodata-objektene vanligvis adresser eller matrikkelnummer. De geografiske objektenes gyldighet på tidsaksen kan (og bør) representeres her
 +
*Analysedata: Analysedata på individnivå (bosteder, personer, hushold)
 +
*Rasterdata: Georefererte og rektifiserte historiske kart.
 
===Vektordata===
 
===Vektordata===
Last ned [http://solli.h.uib.no/urbgeo/export/ub_pointz.zip ub_pointz.zip] og pakk ut zip-filen  til ein katalog under ArcGIS Desktop eller QGIS desktop.
+
Vektordata (Geodata) består av punkt, linjer og polygon som beskriver og konkrete geografiske objekter som hus, tomter, gater
 +
Last ned [http://solli.h.uib.no/urbgeo/export/ub_pointz.zip ub_pointz.zip] og pakk ut zip-filen  til ein katalog under ArcGIS Desktop eller QGIS desktop. Vektordata har datum og projeksjon ''WGS84 UTM 33'' med [http://spatialreference.org/ref/epsg/wgs-84-utm-zone-33n/ EPSG 32633]  (Sjekk: UTM sone 33 basert på EUREF89).
 +
Primærnøkkel er hbrgeoid.  [http://solli.h.uib.no/urbgeo/export/ub_pointz.zip ub_point].
 +
 
 +
===Koplingsdata===
 +
Koplingsdata består av abstrakte referanser til de geografiske objektene, eksempelvis matrikkel til tomt, adresse til hus og navn. De abstrakte geografiske objektene er språklige referanser til de konkrete geografiske objektene (punkt, linje, polygon). Kopling mellom abstrakte og konkrete geografiske objekt er hbrgeoid. En annen viktig egenskap ved koplingsdata er at tid kan representeres i koplingsdata, dvs. de geografiske objektenes "levetid" langs tidsaksen.
  
Primærnøkkel er hbrgeoid
+
Tilgjengelige koplingsdata er:
 +
*Bosteder i folketellingen 1910 [http://solli.h.uib.no/urbgeo/export/bost1910.csv bost1910.csv] Bruk autoid eller kildeid for å kople til bostedsfila for folketellinga 1910
 +
*Lenkefilen med kopling til digitalarkivet: [http://solli.h.uib.no/urbgeo/export/lenktab.csv lenktab.csv]
  
Tilgjengelige analysedata er:
+
Det kan vere lurt å laste desse fila i MS-Excel og konvertere den til MS-Excel 2003-format (*.xls) før du importerer den i ArcGIS. Merk at filer i uft-8 må settes inn som tekst i et eksisterende ark slik at MS-Excel kan konverter filen korrekt
Bosteder i folketellingen 1910 [http://solli.h.uib.no/urbgeo/export/bost1910.csv bost1910.csv] Bruk autoid for å kople til bostedsfila for folketellinga 1910
+
===Analysedata===
 +
Urbgeo har egentlig ingen egne analysedata. Koplingsdata er referanser eller koplinger til individdata og andre analysedata. Koplingen mellom analysedata til de konkrete geografiske objekt er hbrgeoid. Merk at analysedata er koplet direkte til vektordata og ikke via koplingsdata. Dersom de geografiske objektene har tidshorisont må denne representeres gjennom en kopling via koplingsdata.
  
Døde i Trondheim 1840-1911 [Ta_kontakt_med_hilde trhbegr.csv]
+
Noen eksempler på analysedata:
 +
Døde i Trondheim 1840-1911 [Ta_kontakt_med_hilde trhbegr.csv] Analysedata inneheld nøkkelen hbrgeoid. Dette er ein framand-nøkkel hbrgeoid som må koplast til primærnøkkelen i geodata
  
Analysedata inneheld nøkkelen hbrgeoid. Dette er ein framand-nøkkel hbrgeoid som må koplast til primærnøkkelen i geodata [http://solli.h.uib.no/urbgeo/export/ub_pointz.zip ub_point].
+
Utdrag fra Hallstein Myklebost tabell (Tettsteder i Norge):
 +
[http://solli.h.uib.no/urbgeo/export/hallmykl.csv hallmykl.csv]
 +
Koples til geodata med hbrgeoid
  
 
===Rasterdata===
 
===Rasterdata===

Nåværende revisjon fra 23. nov. 2016 kl. 08:46

Etablering av UrbGeo GISdatabase

Versjon 0.0, 22. november 2016.

Dette dokumentet viser steg-for-steg dokumentasjon av GIS-databasen urbgeo med DBMS PostgreSQL og overføring til ArcGIS server og etablering av enkel web-applikasjon basert på data og geodata. Dersom du berre er interessert i å laste ned geodata gå til siste hovedpunkt i dokumentasjonen,"Import av data".

Forusetninger

Urbgeo er laget ved hjelp av Postgresql. Forutsetningen er at PostgreSQL 9.x med tilhørende GIS-ekstensjon postgis er installert på ein linux-maskin. Denne versjonen av dokumentasjonen er basert på Ubuntu Linux 14.04 LTS, postgreSQL 9.5 og postgis 2.2.2. I tillegg er det nødvendig å installere pg_similarity for å kunne bruke meir avanserte streng-samanlikning. Det er også en forutsetning at database-brukeren har privilegier til å opprette databaser. Videre blir alle filer lagret i katalogen /home/[brukernamn]/urbgeo med underkataloger.


Denne versjonen av urbgeo bruker også geonorge.no og BAAT-konto er nødvendig både for å laste ned adressedata og for å gjere oppslag mot adressedata hos geonorge. Det er mulig å lage opne alternativ til både nedlasting og adressesøk, f.eks. å bruke de opne tenestene hos geonorge eller andre leverandører av adressedata for Norge.

Lage database

Opprette database frå linux prompt
~$cd
~$mkdir urbgeo
~$cd urbgeo
~$createdb urbgeo

Logge på database urbgeo frå linux prompt
~$psql -d urbgeo

Prompt i psql er databasenamn og =#

Laste postgis ekstensjon og streng-komparasjon urbgeo=#create extension postgis; urbgeo=#\i /home/arne/STRM/pg_similarity/pg_similarity.sql

Alternativt til linux og :


DROP DATABASE IF EXISTS "urbgeo";
CREATE DATABASE "urbgeo";
\c urbgeo;
create extension postgis;
\i /home/arne/STRM/pg_similarity/pg_similarity.sql

For å sjekke at alt er i orden kan du logge på databasen med psql -d urbgeo vil prompten vere urbgeo=#

Logg av med \q

Lasting av hjelpetabellar

Første steg blir å laste databasen med hjelpetabellar som i all hovudsak inneheld data om den administrative inndelinga, namn på byar og kjeldeidentifikasjon. Utgangspunkt er data frå NSD (Norsk Samfunnsvitenskaplig Datatjenste), Arkivverket og Hallstein Myklebost, Norges tettbygde steder.

Nedanfor er script for å opprette databaser og laste inn data frå dei ulike formata

Opprette database

-- Om du må lage helt ny database, bruk de to neste kommandoene
--
--DROP DATABASE IF EXISTS "urbgeo";
--CREATE DATABASE "urbgeo";

\c "urbgeo";

Tettsted og by

Første hjelpetabell er definisjon og data for tettsteder. Data er henta frå Hallstein Myklebust, Tettsteder i Norge 1875-1950 og lagret i et csv-dokument med ";" som kolonne-skille. Myklebosts tettstadnummer er nytta som primærnøkkel for byane i urbgeo. HMID=Hallstein Myklebust ID. Merk at encoding er sett til Latin1 sidan csv-fila er laga med MS-Excel under Windows 10.


--
-- Table structure for table 'hallmykl'
--


DROP TABLE IF EXISTS "hallmykl";

CREATE TABLE "hallmykl" (
  "hmid" INTEGER NOT NULL, 
  "fylke" VARCHAR(25), 
  "tettstad" VARCHAR(25), 
  "kommune" VARCHAR(25), 
  "f1875" INTEGER, 
  "f1890" INTEGER, 
  "f1900" INTEGER, 
  "f1910" INTEGER, 
  "f1920" INTEGER, 
  "f1930" INTEGER, 
  "f1946" INTEGER, 
  "f1959" INTEGER, 
  "hmtype" VARCHAR(50), 
  "hmkode" VARCHAR(50), 
  "kommuner" VARCHAR(255), 
  "asciiname" VARCHAR(25), 
  currknr integer,
  PRIMARY KEY (hmid)
);

COPY hallmykl FROM '/home/arne/urbgeo/data/hmexp.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'LATIN1' NULL '';

update "hallmykl" set kommune = 'Mo i Rana' where asciiname='Mo' or currknr=1833;
update "hallmykl" set asciiname = 'Mo i Rana' where asciiname='Mo' or currknr=1833;
alter table hallmykl add column url varchar(255);

Merk at noen av tettstedene i perioder er del av en kommune og var ikke selvstendig bykommune. Noen av tettstedene hadde også så liten befolkning at de vil falle bort fra de fleste definisjoner av "by". Og så er det noen av tettstedene som fikk bystatus etter 1910.

Utdrag/dump av tabellen vis hallmykl

Kommuner og fylker

Hovedtabell er nsd1910a

Neste datafil er kommunenefila for 1910. Denne er trekt ut frå kommunedatabasen i NSD. Kommunedatabasen har muligheit for å lage kommunefiler for eit tidspunkt (år). Den aktuelle fila er for år 1910. Lista frå NSD-kommunedatabase som inneheld nsd-kommunenr (nsdknr) og nsd-kommunenamn (nsdknam) er overført (copy-paste) til MS-Excel. I MS-Excel er kommunenr brukt for å leggje til fylkesnummer. Fylkesnummer er danna ved funksjonen avrund.ned(nsdknr/100;0). I tillegg er det konstruert ein fjerde kolonne som viser om kommuna er by eller landsbygd. Dette feltet er konstruert med funksjonen: Hvis(nsdknr-(fylkenr*100)<10;1;0). Dvs. dersom nsdknr - fylkesnr*100 er mindre enn 10, så er kommuna ein bykommune.

-- Oppretter kommunefil for Norge i 1910

DROP TABLE IF EXISTS "nsd1910a";

CREATE TABLE "nsd1910a" (
  "nsdknr" INTEGER NOT NULL, 
  "nsdknamn" VARCHAR(25), 
  "fylke" INTEGER, 
  "byland" INTEGER,  
   PRIMARY KEY (nsdknr) 
);

COPY nsd1910a FROM '/home/arne/urbgeo/data/nsdexp10.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'LATIN1' NULL '';

Alter table nsd1910a add column asciiname varchar(25);
Alter table nsd1910a add column hmid integer;
alter table nsd1910a add column url varchar(255);

-- Genererer asciiname uten nordiske teikn og standardiserer namn til gjeldande namn (Kristiania til Oslo)

update "nsd1910a" set asciiname = replace(nsdknamn,'å','aa');
update "nsd1910a" set asciiname = replace(asciiname,'Å','Aa');
update "nsd1910a" set asciiname = replace(asciiname,'æ','e');
update "nsd1910a" set asciiname = replace(asciiname,'ø','o');
update "nsd1910a" set asciiname = replace(asciiname,'Ø','O');
update "nsd1910a" set asciiname = replace(asciiname,'Æ','E');
update "nsd1910a" set asciiname = replace(asciiname,'Kristiania','Oslo');
update "nsd1910a" set asciiname = replace(asciiname,'Fredrikshald','Halden');
update "nsd1910a" set asciiname = 'Mo i Rana' where nsdknr=1833;

-- Sjekk av koblingene og standardisering

select count(*) from nsd1910a, hallmykl where nsd1910a.nsdknamn=hallmykl.tettstad;
select count(*) from nsd1910a, hallmykl where nsd1910a.asciiname=hallmykl.asciiname;

-- Setter hmid i nsd1910a fila 
update nsd1910a set hmid=hallmykl.hmid from hallmykl where nsd1910a.asciiname=hallmykl.asciiname;

-- Kontroll av tettsteder som ikke bystatus i 1910

select hallmykl.hmid, hallmykl.tettstad, nsd1910a.nsdknamn from hallmykl left join nsd1910a on hallmykl.asciiname=nsd1910a.asciiname where nsd1910a.hmid is null;

Utdrag/dump av tabellen vis nsd1910a

Tellekretser og bytabell i 1910


-- Oppretter tabell for teljekrets (tk)

DROP TABLE IF EXISTS "tk1910";

CREATE TABLE "tk1910" (
  "id" VARCHAR(25) NOT NULL, 
  "kildeid" INTEGER NOT NULL, 
  "kretsnr" VARCHAR(25) NOT NULL, 
  "tk_navn" VARCHAR(255), 
  "sogn" VARCHAR(50), 
  "prestegjeld" VARCHAR(50), 
  "herred_by" VARCHAR(50), 
  "merknader" VARCHAR(255),
  PRIMARY KEY (kildeid, kretsnr)
);

COPY tk1910 FROM '/home/arne/urbgeo/data/ft1910heleKrets.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';

-- Legg til ekstra felt id ed

Alter table tk1910 add column asciiname varchar(25);
alter table tk1910 add column byland INTEGER;
alter table tk1910 add column url varchar(255);

-- Standardiserer asciiname

update tk1910 set asciiname = replace(herred_by,'å','aa');
update tk1910 set asciiname = replace(asciiname,'Å','Aa');
update tk1910 set asciiname = replace(asciiname,'æ','e');
update tk1910 set asciiname = replace(asciiname,'ø','o');
update tk1910 set asciiname = replace(asciiname,'Ø','O');
update tk1910 set asciiname = replace(asciiname,'Æ','E');
update tk1910 set asciiname = replace(asciiname,'Kristiania','Oslo');
update tk1910 set asciiname = replace(asciiname,'Fredrikshald','Halden');
update tk1910 set asciiname = replace(asciiname,'Trondhjem','Trondheim');
update tk1910 set asciiname = replace(asciiname,'Porsgrund','Porsgrunn');
update tk1910 set asciiname = replace(asciiname,'Stenkjer','Steinkjer');
update tk1910 set asciiname = 'Sokndal' where prestegjeld is null and herred_by='Sogndal';

-- Merker teljekretser som er i by

update tk1910 set byland=1 from hallmykl  where tk1910.asciiname=hallmykl.asciiname;

-- Oppretter bytabell for 1900-teljinga

DROP TABLE IF EXISTS "by1910";

CREATE TABLE "by1910" (
  "hmid" INTEGER NOT NULL, 
  "bynamn" VARCHAR(25), 
  "nsdknr" INTEGER, 
  "asciiname" VARCHAR(25),
  "tkid" varchar(25)
);


-- laster bytabellen frå teljekretsar

insert into by1910 (hmid,bynamn,nsdknr,asciiname,tkid) select count(*), herred_by, count(*), min(asciiname), min(id) from tk1910 where byland=1 group by herred_by;

update by1910 set hmid=hallmykl.hmid from hallmykl where by1910.asciiname=hallmykl.asciiname;
update by1910 set nsdknr=nsd1910a.nsdknr from nsd1910a where by1910.asciiname=nsd1910a.asciiname;

alter table by1910 add  primary key (hmid);
alter table by1910 add column url varchar(255);


-- oppretter kildetabell frå teljekretser

DROP TABLE IF EXISTS "kilde1910";

CREATE TABLE "kilde1910" (
  "hmid" INTEGER, 
  "kildeid" INTEGER NOT NULL, 
  "asciiname" VARCHAR(25),
  PRIMARY KEY (kildeid) 
);

insert into kilde1910 (hmid,kildeid,asciiname) select count(*), kildeid, min(asciiname) from tk1910 where byland=1 group by kildeid;

-- Merker aller kilder med tettstadid (hmid)

update kilde1910 set hmid=hallmykl.hmid from hallmykl where kilde1910.asciiname=hallmykl.asciiname;


Folketellingen 1900

Utgår i denne versjon

Metadata

For framtidig bruk er det opprettet en metatabell for kilder.

DROP TABLE IF EXISTS "ub_kilde";
create table "ub_kilde" (
  id varchar(6) not null,
  startaar integer,
  sluttaar integer,
  dekning integer,
  filnamn varchar(64),
  url varchar(255),
  primary key (id) 
);

-- id: FTxxxx = folketelling, BGxxxx=Gravlagde, DP=Dopte, VI=Vigde
-- dekning 1=Norge, 2=fylke, 3=kommune, 4=tettstad

insert into ub_kilde values ('FT1910',1910,1910,1,'ft1910heleby.csv', 'http://digitalarkivet.arkivverket.no/ft/sok/1910'); 

Bosted og gatetabeller

Bosted og gatetabeller blir lastet fra FT1910. Hovedtabellen er bost1910.


DROP TABLE IF EXISTS "tmptab";
CREATE TABLE "tmptab" (
  autoid integer not null,
  id varchar(25),
  overid varchar(25),
  kildeid integer,
  kretsnr varchar(25),
  bostnr varchar(25),
  gateognr varchar(255),
  bydel varchar(50),
  gaardnr varchar(50),
  gardeier varchar(255),
  ant_tilst varchar(50),
  ant_hj varchar(50),
  ant_pp varchar(50),
  hustype varchar(255),
  sideb varchar(255),
  ubebodd varchar(25),
  ant_et varchar(25),
  ant_leil varchar(255),
  ant_naer varchar(255),
  ant_verk varchar(255),
  ant_ledig varchar(50),
  ant_kvist varchar(50),
  ant_kjeller varchar(50),
  merknad varchar(255), 
  primary key (autoid)
);

COPY tmptab FROM '/home/arne/urbgeo/data/ft1910heleby.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';

drop table if exists bost1910;
create table bost1910 (
    autoid integer not null,
    bostid varchar(25),
    overid varchar(25),
    kildeid integer,
    hmid integer, 
    gateognr varchar(255),
    bydel varchar(50),
    gaardnr varchar(50),
    gatenamn varchar(50),
    gatenr varchar(50),
    gatenum integer,
    gatelitr varchar(50),
    url varchar(255),
    primary key (autoid)
);

INSERT INTO bost1910 (autoid, bostid, overid, kildeid, gateognr, bydel, gaardnr) select autoid, id, overid, kildeid, gateognr, bydel, gaardnr from tmptab where kildeid is not null;

update bost1910 set hmid=kilde1910.hmid from kilde1910 where kilde1910.kildeid=bost1910.kildeid;
select * from bost1910 where hmid is null;

update bost1910 set gatenr = trim((regexp_split_to_array (gateognr, E'\\D+'))[2]);
update bost1910 set gatenamn = trim((regexp_split_to_array (gateognr, gatenr))[1]) where gatenr is not null;
update bost1910 set gatenamn = gateognr where gatenr is null;
update bost1910 set gatenamn = gateognr where gatenr = '';
update bost1910 set gatenr=null where gatenr='';
update bost1910 set gatelitr = trim((regexp_split_to_array (gateognr, gatenr))[2]) where gatenr is not null; 
update bost1910 set gatenum = to_number(gatenr,'999') where gatenr is not null;

select bost1910.hmid, hallmykl.tettstad, count(*) as antgrd from bost1910, hallmykl where ((gaardnr is not null) and (hallmykl.hmid=bost1910.hmid)) group by bost1910.hmid, tettstad ;


drop table if exists gatetab;
create table gatetab (
    hmid integer not null, 
    gatenamn varchar(50) not null,
    anthus integer,
    gateognr varchar(255),
    gatenr varchar(50),
    mingnum integer,
    maxgnum integer,
    aar integer,
    jwval double precision,
    std_gate varchar(50),
    primary key (hmid, gatenamn)
);

insert into gatetab (hmid, gatenamn, anthus, gateognr, gatenr, mingnum, maxgnum,aar) select hmid, gatenamn, count(*) as anthus, min(gateognr), min(gatenr), min(gatenum), max(gatenum), 1910 from bost1910 where gatenamn is not null group by hmid, gatenamn order by hmid, gatenamn;


Utdrag/dump av tabellen vis gtab1910

Geokoding

Geokoding av stedsdata skjer på to måter, a) Stedsnavn for byer, fylker og kommuner blir geokodet ved oppslag mot ein eldre rutine hos kartverket og b) Gateadresser blir geokodet mot data lasta ned frå geonorge.

Geokoding av steder

Landtabellen, fylkestabell og bytabellen geokoda for å få punkt for landa i Norden, dei norske fylka, kommunene i 1910 og tettstadane i urbgeo. Geokoding av land og fylker utgår fra denne versjonen. Geokoding av tettsteder (byene) og kommuner skjer ved bruk av ein enkel php-rutine med oppslag mot geonorge.

Tenesten dokumentert hos geonorge.no

Capabilities for tjenesten https://ws.geonorge.no/SKWS2/services/SSR?wsdl

For å transformere koordinatsystema brukt av kartverket er biblioteket proj4 benyttet. Implementasjonen for php er lastet ned fra github og installert under lokal apache2 server (/var/www/html/vendor/proj4)

Kartverkets dokumentsjon av koder for koordinatsystem brukt av rutinene: SOSI koordinatsystem

Geokoding mot SSR

Rutina geocodeSSR3.php blir køyrd lokalt dvs. http://localhost/geocodeSSR3.php?tabname=hallmykl&colno=2. Rutina generer sql-setningar som blir kopiert manuelt og lagra til insertp3.sql . Det er mogeleg å oppdatere tabellen direkte frå php, men det er valgt å gjere det på denne måten i test. For online bruk vil naturlegvis denne rutina kunne oppdatere tabeller i databasen direkte.

Rutina blir køyrd andre gang for å geokode kommunetabellen for 1910 og då med parameter: http://localhost/geocodeSSR3.php?tabname=nsd1910aa&colno=1. Output blir kopiert og lagra på insertp4.sql

<?php

$klient = new SOAPClient('https://ws.geonorge.no/SKWS2/services/SSR?wsdl');
$stedsnavn = "Hvitsten"; 
$koord="23";
$EPSG='EPSG:25833';

//Her treng du din BAAT-bruker og passord.
$data = array(
    'brukerid' => "[BAAT-BRUKER]",
    'passord' => "[PASSORD]",
    'aliasId' => "",
    'navn' => $stedsnavn,
    'fylkeKommuneListe' => "",
    'nordLL' => 0,
    'ostLL' => 0,
    'nordUR' => 0,
    'ostUR' => 0,
    'maxAnt' => 100,
    'sosiKoordSyst' => $koord
);

include("proj4php/vendor/autoload.php");

use proj4php\Proj4php;
use proj4php\Proj;
use proj4php\Point;

// Initialise Proj4
$proj4 = new Proj4php();

// Create two different projections.
$projutm    = new Proj($EPSG, $proj4);
$projWGS84  = new Proj('EPSG:4326', $proj4);

header("Content-Type: text/html;charset=utf-8");
$table="hallmykl";
$col=2;
$level=5;
if (isset($_GET['tabname'])) {
   $table = $_GET['tabname'];
}
if (isset($_GET['colno'])) {
   $col = $_GET['colno'];
}
if ($table == 'hallmykl') {
   $col=2;
   $level=4;
   $colnr=16; 
   $colk=3; 
} else {
   $col=1;
   $colk=1;
   $level=4;
   $colnr=0; 
}
include 'htmlhead.php';
print ("<body><pre>");
require('../config.php');
include 'db/opendb.php';

$strSQL="SELECT * FROM " . $table . ";";
//$strSQL="SELECT * FROM " . $table . ";";
$results = pg_query($con, $strSQL);
if ($results)  {
     while ($row = pg_fetch_row($results)) {
//        print ($row[$col] . "\t" . $row[$colnr] . "\t" . $row[$colk] . "\n");
        $data['navn']=$row[$col];
        $resultat = $klient->__soapCall('ssrSok', $data);
        if (is_null($resultat->ssrRecords[0])) {
//          print_r ($resultat); 
        } else {
         foreach ($resultat->ssrRecords as $adr) {
           if (intval($adr->for_komm) == intval($row[$colnr]) or strcmp(trim($adr->kom_navn),trim($row[$colk]))==0) {
   	      $svar=$adr->for_snavn; 
 	      $snord=$adr->for_nord1; 
 	      $sost=$adr->for_aust1; 
		// Create a point.
   	      $pointSrc = new Point($sost, $snord, $projutm);
              $pointDest = $proj4->transform($projWGS84, $pointSrc);
              $dlng=$pointDest->x;
              $dlat=$pointDest->y;
              $strISQL = "insert into ub_point_0 (loc_name,loc_type,nord,ost, lng,lat) values ('" . $row[$col] . "',$level," . $snord . "," . $sost . "," . $dlng . "," . $dlat .");\n";
              print   $strISQL;
              break;
           } else {
//              print ("\t" . $adr->for_komm .  "\t" . $adr->kom_navn . "\t" . $row[$col] . "\n");
           } 
         }
        } 
     }	
}
print ("<\/pre>");
include 'htmlend.php';
?>

Lasting av data fra SSR

Data frå SSR, jfr insertp3.sql og insertp4.sql dokumentert over blir satt inn i ein punkt-tabell ub_point_0.

drop table if exists ub_point_0;
create table ub_point_0 (
 gid serial,
"loc_name" varchar(150),
"loc_type" integer,
"hmid" integer,
"nord" double precision,
"ost" double precision,
lng double precision,
lat double precision,
hbrgeoid varchar(18));

\i insertp3.sql
update ub_point_0 set loc_type=4 from hallmykl where loc_type is null and loc_name=hallmykl.tettstad;

\i insertp4.sql
update ub_point_0 set loc_type=5 from nsd1910a where loc_type is null and loc_name=nsd1910a.nsdknamn;

update ub_point_0 set hbrgeoid='1' || trim(to_char(loc_type,'9')) || 'E' || trim(to_char(lng*100000,'0000000')) || 'N' || trim(to_char(lat*100000,'0000000')); 

Etter at stedene er geokodet blir steder med unike hbrgeoid satt inn i tabellen ub_point. Denne inneholder unike punkter

drop table if exists ub_point;
create table ub_point (
 gid serial,
"loc_name" varchar(150),
"loc_type" integer,
"hmid" integer,
"nord" double precision,
"ost" double precision,
"lng" double precision,
"lat" double precision,
hbrgeoid varchar(18));

ALTER TABLE "ub_point" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','ub_point','geom','0','POINT',2);

-- Setter inn unike hbrgeoid

insert into ub_point (loc_name, loc_type, hmid, nord, ost, hbrgeoid) select min(loc_name), min(loc_type), min (hmid), min(nord), min(ost), hbrgeoid from ub_point_0 where hbrgeoid is not null group by hbrgeoid order by hbrgeoid;

-- Koder til utm32
-- geometry ST_MakePoint(double precision x, double precision y);
-- Note x is longitude and y is latitude
-- Note x is east and y is north
-- UPDATE ub_point SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
-- 25833 UTM sone 33 basert på EUREF89
-- EPSG 32633 UTM Sone 33 basert på WGS84,2d (horisontal), 

UPDATE ub_point SET geom = ST_SetSRID(ST_MakePoint(ost, nord), 32633);
update ub_point set lng = ST_X(ST_TRANSFORM(geom,4326));
update ub_point set lat = ST_Y(ST_TRANSFORM(geom,4326));

Geokoding av gateadresser

Grunnlaget for goding av gateadresser er at de blir "vasket" mot dagens gateadresser. Dagens gateadresser er lastet ned fra geonorge via BAAT.

Gatetabellen bost1910 blir "vasket" mot data lastet ned fra geonorge.

Lasting av data fra geonorge

/* ADRESSEID	TYPE	KOMMUNENR	ADRESSEKODE	ADRESSENAVN	KORTADRESSENAVN	NR	BOKSTAV	GARDSNR	BRUKSNR	FESTENR	SEKSJONSNR	UNDERNR	KORTADRESSETILLEGGSNAVN	TILLEGGSNAVNKILDEKODE	TILLEGGSNAVNKILDENAVN	KOORDINATSYSTEMKODE	NORD	OST	GRUNNKRETSNR	GRUNNKRETSNAVN	KIRKESOGNNR	KIRKESOGNNAVN	TETTSTEDNR	TETTSTEDNAVN	VALGKRETSNR	VALGKRETSNAVN	POSTNUMMER	POSTNUMMEROMRADE */

DROP TABLE IF EXISTS "tmptab";           
CREATE TABLE "tmptab" ( 
ADRESSEID varchar(255),           /*                 Unik identifikasjon for adressen. Skal kun benyttes for kobling av data i CSV-filer.*/ 
TYPE varchar(255),           /*                      Kan være en av følgende verdier:
                              Vegadresse
                              Matrikkeladresse */ 
KOMMUNENR varchar(255),           /*                 Kommunenummer */ 
ADRESSEKODE varchar(255),           /*               Adressekode */ 
ADRESSENAVN varchar(255),           /*               Adressenavn */ 
KORTADRESSENAVN varchar(255),           /*           Kort adressenavn */ 
NR varchar(255),           /*                        Nummer, del av adressenummer */ 
BOKSTAV varchar(255),           /*                   Bokstav, del av adressenummer */ 
GARDSNR varchar(255),           /*                   Gårdsnummer */ 
BRUKSNR varchar(255),           /*                   Bruksnummer */ 
FESTENR varchar(255),           /*                   Festenummer */ 
SEKSJONSNR varchar(255),           /*                Seksjonsnummer */ 
UNDERNR varchar(255),           /*                   Undernummer */ 
KORTADRESSETILLEGGSNAVN varchar(255),           /*   Kort adressetilleggsnavn */ 
TILLEGGSNAVNKILDEKODE varchar(255),           /*     Kodeverdi for AdressetilleggsnavnKildeKode. Verdiene er:
                             (tom) (blank)
                              KE   Krevd av eier
                              AE   Anmodet av hjemmelshaver
                              TK   Tildelt av kommunen
                              MA   Matrikkeladressenavn */
TILLEGGSNAVNKILDENAVN varchar(255),           /*     Beskrivelse til AdressetilleggsnavnKildeKode, se over */ 
KOORDINATSYSTEMKODE varchar(255),           /*       Sosikoden for koordinatsystemet. Verdiene er:  
                              21   EUREF89 UTM Sone 31
                              22   EUREF89 UTM Sone 32
                              23   EUREF89 UTM Sone 33
                              24   EUREF89 UTM Sone 34
                              25   EUREF89 UTM Sone 35
                              26   EUREF89 UTM Sone 36    */ 
NORD varchar(255),           /*                      Koordinatverdien for nord*/ 
OST varchar(255),           /*                       Koordinatverdien for øst*/ 
GRUNNKRETSNR varchar(255),           /*              Kretsnummer for grunnkrets*/ 
GRUNNKRETSNAVN varchar(255),           /*            Navn til grunnkrets*/ 
KIRKESOGNNR varchar(255),           /*               Kretsnummer for kirkesogn, Består av bispedømme, prosti, prestegjeld og sogn, 8 tegn*/ 
KIRKESOGNNAVN varchar(255),           /*             Navn til kirkesogn*/ 
TETTSTEDNR varchar(255),           /*                Kretsnummer til tettsted*/ 
TETTSTEDNAVN varchar(255),           /*              Navn til tettsted*/ 
VALGKRETSNR varchar(255),           /*               Kretsnummer til valgkrets*/ 
VALGKRETSNAVN varchar(255),           /*             Navn til valgkrets*/ 
POSTNUMMER varchar(255),           /*                Postnummer*/ 
POSTNUMMEROMRADE varchar(255),
dummy varchar(255),           /*          Postnummerområde */
primary key(ADRESSEID)
);

-- COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_16_SOR-TRONDELAG_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_01_OSTFOLD_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_02_AKERSHUS_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_03_OSLO_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_04_HEDMARK_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_05_OPPLAND_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_06_BUSKERUD_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_07_VESTFOLD_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_08_TELEMARK_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_09_AUST-AGDER_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_10_VEST-AGDER_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_11_ROGALAND_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_12_HORDALAND_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_14_SOGN_OG_FJORDANE_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_15_MORE_OG_ROMSDAL_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_16_SOR-TRONDELAG_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_17_NORD-TRONDELAG_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_18_NORDLAND_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_19_TROMS_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
COPY tmptab FROM '/home/arne/urbgeo/data/geonorge/P13_20_FINNMARK_Adresse.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';

DROP TABLE IF EXISTS "gatematr";
create table "gatematr" (
lnr integer,
hmid integer,
gate varchar(255),
gatenr varchar(25),
gatenum integer,
gatelitr varchar(25),
aar integer,
kartref varchar(255),
stdgate varchar(255),
altname varchar(255),
tettsted varchar(255),
sosikoord varchar(25),
nord double precision,
ost double precision,
primary key(lnr, hmid)
);

-- insert into gatematr (lnr,hmid,gate,gatenr,gatelitr,aar,tettsted,sosikoord, nord,ost) select cast (ADRESSEID as int), 395, ADRESSENAVN, NR, BOKSTAV, 2016,  TETTSTEDNAVN,KOORDINATSYSTEMKODE, cast(nord as float), cast(ost as float) from tmptab where KOMMUNENR='1601' and tettstednavn='Trondheim';
-- insert into gatematr (lnr,hmid,gate,gatenr,gatelitr,aar,tettsted,sosikoord, nord,ost) select cast (ADRESSEID as int), 37, ADRESSENAVN, NR, BOKSTAV, 2016,TETTSTEDNAVN, KOORDINATSYSTEMKODE,cast(nord as float), cast(ost as float) from tmptab where KOMMUNENR='0301' and tettstednavn='Oslo';
-- insert into gatematr (lnr,hmid,gate,gatenr,gatelitr,aar,tettsted,sosikoord, nord,ost) select cast (ADRESSEID as int), 172, ADRESSENAVN, NR, BOKSTAV, 2016, TETTSTEDNAVN,KOORDINATSYSTEMKODE,cast(nord as float), cast(ost as float) from tmptab where tettstednavn='Sandefjord';
-- insert into gatematr (lnr,hmid,gate,gatenr,gatelitr,aar,tettsted,sosikoord, nord,ost) select cast (ADRESSEID as int), 177, ADRESSENAVN, NR, BOKSTAV, 2016, TETTSTEDNAVN,KOORDINATSYSTEMKODE,cast(nord as float), cast(ost as float) from tmptab where tettstednavn='Larvik';

insert into gatematr (lnr,hmid,gate,gatenr,gatelitr,aar,tettsted,altname, sosikoord, nord,ost) select distinct on (ADRESSEID) cast (ADRESSEID as int) as lnr, hmid, tmptab.ADRESSENAVN, tmptab.NR, tmptab.BOKSTAV, 2016, TETTSTEDNAVN,POSTNUMMEROMRADE, KOORDINATSYSTEMKODE,cast(nord as float), cast(ost as float) from tmptab inner join hallmykl on (tettstad=TETTSTEDNAVN or tmptab.POSTNUMMEROMRADE=upper(hallmykl.kommune));



update gatematr set gatenum=cast (gatenr as integer);

DROP TABLE IF EXISTS "gatemtab";
create table "gatemtab" (
lnr integer,
hmid integer,
gate varchar(255),
ant integer,
nord double precision,
ost double precision,
primary key(lnr, hmid));

insert into gatemtab (lnr, hmid, gate, ant, nord, ost) select min(lnr), min(hmid), gate, count(*), avg(nord), avg(ost) from gatematr group by gate order by gate; 

DROP TABLE IF EXISTS "bytab";
create table "bytab" (
lnr integer,
hmid integer,
tettsted varchar(255),
altname varchar(255),
ant integer,
nord double precision,
ost double precision,
primary key(lnr, hmid));

insert into bytab (lnr, hmid, tettsted, altname, ant, nord, ost) select min(lnr), hmid, min (tettsted) || '/' || max(tettsted), min(altname) || '/' || max(altname), count(*), avg(nord), avg(ost) from gatematr group by hmid order by hmid; 

-- select hallmykl.hmid, tettstad, tettsted from hallmykl left outer join bytab on (hallmykl.tettstad=bytab.tettsted or bytab.altname=upper(hallmykl.tettstad));

-- select hallmykl.hmid, tettstad, tettsted,altname from hallmykl left outer join bytab on hallmykl.hmid=bytab.hmid;

Vasking mot data fra geonorge


Drop table if exists "ub_loc";
create table "ub_loc" (
   id serial,
   loc varchar(255),
   loc_type integer, 
   hmid integer,
   minyear integer,
   maxyear integer,
   ant integer,
	gatenamn varchar(255),
	gatenr varchar(255),
	gatelitr varchar(255),
	gatenum integer,
	gt_adr varchar(255),
	std_adr varchar(255),
	jwval double precision,
   lnr integer,
   primary key(id)
);

-- \i /home/arne/STRM/pg_similarity/pg_similarity.sql

insert into ub_loc (loc, loc_type, hmid, minyear, maxyear, ant) select gateognr, 1, hmid, 1910, 1910, count(*) as ant from bost1910 where gateognr is not null group by hmid, gateognr;

update ub_loc set jwval = 0.0;

update ub_loc set gatenr = trim((regexp_split_to_array (loc, E'\\D+'))[2]);
update ub_loc set gatenamn = trim((regexp_split_to_array (loc, gatenr))[1]) where gatenr is not null;
update ub_loc set gatenamn = loc where gatenr is null;
update ub_loc set gatenamn = loc where gatenr = '';
update ub_loc set gatenr=null where gatenr='';
update ub_loc set gatelitr = trim((regexp_split_to_array (loc, gatenr))[2]) where gatenr is not null; 
update ub_loc set gatenum = to_number(gatenr,'999') where gatenr is not null;

update ub_loc set std_adr=gate, jwval=jarowinkler(gatenamn,gate) from gatemtab where ub_loc.hmid=gatemtab.hmid and (jarowinkler(gatenamn,gate)>ub_loc.jwval) and (jarowinkler(gatenamn,gate)>0.9);
update ub_loc set std_adr=gate, jwval=jarowinkler(gatenamn,gate) from gatemtab where ub_loc.hmid=gatemtab.hmid and (jarowinkler(gatenamn,gate)>ub_loc.jwval) and (jarowinkler(gatenamn,gate)>0.93);
update ub_loc set std_adr=gate, jwval=jarowinkler(gatenamn,gate) from gatemtab where ub_loc.hmid=gatemtab.hmid and (jarowinkler(gatenamn,gate)>ub_loc.jwval) and (jarowinkler(gatenamn,gate)>0.99);

-- update ub_loc set std_adr=gatemtab.gate, jwval=jarowinkler(gatenamn,gatemtab.gate) from ub_loc inner join gatemtab on ub_loc.hmid=gatemtab.hmid where (jarowinkler(ub_loc.gatenamn,gatemtab.gate)>ub_loc.jwval) and (jarowinkler(ub_loc.gatenamn,gatemtab.gate)>0.9);

update ub_loc set gatelitr = null where gatelitr not in ('a','b','c','d','e','f','g','h','i','j','k');

update ub_loc set gt_adr = gatenamn || ' ' || trim(to_char(gatenum,'999')) || trim(gatelitr) where gatelitr is not null and std_adr is null;
update ub_loc set gt_adr = gatenamn || ' ' || trim(to_char(gatenum,'999')) where gatelitr is null  and std_adr is null;

update ub_loc set gt_adr = std_adr || ' ' || trim(to_char(gatenum,'999')) || trim(gatelitr) where gatelitr is not null and std_adr is not null;
update ub_loc set gt_adr = std_adr || ' ' || trim(to_char(gatenum,'999')) where gatelitr is null  and std_adr is not null;


update ub_loc set gt_adr = gatenamn where gt_adr is null;

update ub_loc set lnr = gatematr.lnr from gatematr where std_adr = gatematr.gate and ub_loc.hmid=gatematr.hmid and ub_loc.gatenum=gatematr.gatenum and lower(ub_loc.gatelitr)=lower(gatematr.gatelitr) and ub_loc.gatelitr is not null;
update ub_loc set lnr = gatematr.lnr from gatematr where std_adr = gatematr.gate and ub_loc.hmid=gatematr.hmid and ub_loc.gatenum=gatematr.gatenum and ub_loc.gatelitr is null;

update gatetab set jwval=jarowinkler(gatenamn,gate) from gatemtab where gatetab.hmid=gatemtab.hmid and (jarowinkler(gatenamn,gate)>gatetab.jwval) and (jarowinkler(gatenamn,gate)>0.9);

-- select jarowinkler(gatenamn,gate),gatenamn,gate,gatetab.hmid from gatetab, gatemtab where gatetab.hmid=gatemtab.hmid and (jarowinkler(gatenamn,gate)>gatetab.jwval) and (jarowinkler(gatenamn,gate)>0.9);

-- select jarowinkler(gatenamn,gate),gatenamn,gate,ub_loc.hmid from ub_loc, gatemtab where ub_loc.hmid=gatemtab.hmid and (jarowinkler(gatenamn,gate)>ub_loc.jwval) and (jarowinkler(gatenamn,gate)>0.9);

Etablering av unike punkt

Initiering av tabell med unike lokasjoner for hbgrgeoid Eksekveringsfil: loadlok2b.sql


-- Temporær tabell ub_point_0

drop table if exists ub_point_0;
create table ub_point_0 (
 gid serial,
 lnr integer,
"loc_name" varchar(150),
"loc_type" integer,
"hmid" integer,
"nord" double precision,
"ost" double precision,
lng double precision,
lat double precision,
hbrgeoid varchar(18));

ALTER TABLE "ub_point_0" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','ub_point_0','geom','0','POINT',2);

insert into ub_point_0 (loc_name, lnr, loc_type, hmid, nord, ost) select loc, ub_loc.lnr, 1, ub_loc.hmid, nord, ost from ub_loc inner join gatematr on gatematr.lnr=ub_loc.lnr;

UPDATE ub_point_0 SET geom = ST_SetSRID(ST_MakePoint(ost, nord), 32633);
update ub_point_0 set lng = ST_X(ST_TRANSFORM(geom,4326));
update ub_point_0 set lat = ST_Y(ST_TRANSFORM(geom,4326));


update ub_point_0 set hbrgeoid='1' || trim(to_char(loc_type,'9')) || 'E' || trim(to_char(lng*100000,'0000000')) || 'N' || trim(to_char(lat*100000,'0000000')); 

-- Setter inn unike hbrgeoid

insert into ub_point (loc_name, loc_type, hmid, nord, ost, hbrgeoid) select min(loc_name), min(loc_type), min (hmid), min(nord), min(ost), hbrgeoid from ub_point_0 where hbrgeoid is not null group by hbrgeoid order by hbrgeoid;

-- Koder til utm33
-- geometry ST_MakePoint(double precision x, double precision y);
-- Note x is longitude and y is latitude
-- Note x is east and y is north
-- UPDATE ub_point SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
-- 25833 UTM sone 33 basert på EUREF89
-- EPSG 32633 UTM Sone 33 basert på WGS84,2d (horisontal), 

UPDATE ub_point SET geom = ST_SetSRID(ST_MakePoint(ost, nord), 32633);

Koding av GIS data

GIS data består av to ulike datasett. Beskrivande data koda med geoid og sjølve gisdata: Punkt, linjer og polygon. Koplinga mellom data og gisdata er gjort med geoid.

Koding av data med hbrgeoid

Eksekveringsfil: giskode.sql


-- Sette inn geokode i tabellen for bustader

Alter table bost1910 add column hbrgeoid varchar(18);
Alter table hallmykl add column hbrgeoid varchar(18);
alter table county add column hbrgeoid varchar(18);
alter table country add column hbrgeoid varchar(18);
alter table nsd1910a add column hbrgeoid varchar(18); 
alter table ub_loc add column hbrgeoid varchar(18);


update hallmykl set hbrgeoid = ub_point.hbrgeoid from ub_point where ub_point.loc_name=hallmykl.tettstad and ub_point.loc_type=4;
update nsd1910a set hbrgeoid = ub_point.hbrgeoid from ub_point where ub_point.loc_name=nsdknamn and ub_point.loc_type=5;
update country set hbrgeoid = ub_point.hbrgeoid from ub_point where ub_point.loc_name=country.match_addr and ub_point.loc_type=9;
update county set hbrgeoid = ub_point.hbrgeoid from ub_point where ub_point.loc_name=county.match_addr and ub_point.loc_type=7;

update bost1910 set hbrgeoid = ub_point_0.hbrgeoid from ub_point_0 where ub_point_0.hmid=bost1910.hmid and ub_point_0.loc_name=bost1910.gateognr;


Koding av lenkedata

Lenking fra beskrivende data (Individdata) til geodata (Punkt) blir sett i en egen lenketabell.


update bost1910 set url='http://digitalarkivet.arkivverket.no/ft/bosted_by/' || bostid;
update nsd1910a set url='https://lokalhistoriewiki.no/index.php/' || nsdknamn;
update hallmykl set url='https://lokalhistoriewiki.no/index.php/' || tettstad;

drop table if exists lenktab;
create table lenktab (
 lid serial,
url varchar(255),
lnk_type integer,
lnk_tekst varchar(255),
hbrgeoid varchar(18));

insert into lenktab (url,lnk_type,lnk_tekst, hbrgeoid) select url, 1, match_addr, hbrgeoid from country where hbrgeoid<>'';
insert into lenktab (url,lnk_type,lnk_tekst, hbrgeoid) select url, 1, match_addr, hbrgeoid from county where hbrgeoid<>'';
insert into lenktab (url,lnk_type,lnk_tekst, hbrgeoid) select url, 1, tettstad, hbrgeoid from hallmykl where hbrgeoid<>'';
insert into lenktab (url,lnk_type,lnk_tekst, hbrgeoid) select url, 1, gateognr, hbrgeoid from bost1910 where hbrgeoid<>'';
insert into lenktab (url,lnk_type,lnk_tekst, hbrgeoid) select url, 1, nsdknamn, hbrgeoid from nsd1910a where hbrgeoid<>'';

Export av Geo data

GIS data består av to ulike sett av data. Beskrivande data (Analysedata, individdata) koda med geoid (hbrgeoid) og sjølve gisdata: Punkt, linjer og polygon. Data koda med geoid (analysedata) og geodata blir eksportert kvar for seg.

Eksport av 1910 bosteder

For å kople bostedene i folketeljinga 1910 til geoid blir ein fil med hbrgeoid og primærnøkkelen for ft1910heleby exportert til en csv-fil. Feltet autoid er primærnøkkel for ft1910heleby.csv og gir koplinga til analysedata (individdata)

For å kunne lenke til individdata i Digitalarkivet er det laget en egen lenketabell tilsvarende analysedata.

-- Eksporter bosteder i 1910-tellinga, autoid, hbrgeoid og hmid.

COPY bost1910 (autoid, hbrgeoid, hmid) to '/home/arne/urbgeo/export/bost1910.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';

-- Eksporter lenke-tabell (utdrag) 

copy (select * from lenktab) to '/home/arne/urbgeo/export/lenktab.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';

Kommandoen copy eksporterer data til en csv-fil i det lokale filsystemet. Etter eksport må filene overførast til områder for nedlasting eller annen bruk. For import av filene se neste punkt.

Eksport av geodata

Punkt-data kan eksporterast enten som dump av tabellen ub_point eller som shapefile. Dump-fila kan importerast til ein annan postgresql (og andre SQL-databaser med postgis-ekstensjon.

Shapefila kan importerast både til desktop program som ArcGIS desktop eller QGIS. Den kan også importerast til ArcGIS Server

Eksport ved shapefile

Eksport av geodata med shapefile blir utført med konverteringsprogrammet pgsql2shp (Windows, Linux).

linux>pgsql2shp urbgeo ub_point
linux>zip -r ub_pointz.zip ub_point.* 

Programmet pgsql2shp vil lage filene ub_point.shp, ub_point.dbf, ub_point.shx, ub_point.prj og ub_point.cfg. Programmet pgsql2shp vil skrive filene til lokalt filsystem. Ved bruk må alle fem filene må flyttast til ArcGIS-området for shapefiler. Se nedenfor for data og import av data

Eksport ved database dump

Eksport av geodata (punkt, linje, polygon) for import i postgresql. ub_point har unike punkt og har primærnøkkel hbrgeoid

linux>pg_dump -t ub_point urbgeo -f urbgeo1.sql

Eksport av hjelpetabeller og datatabeller for import i postgresql

pg_dump -t hallmykl -t nsd1910a -t bost1910 urbgeo -f urbgeo2.sql

Import av data

Import til ArcGIS

Urbgeo har fire typer dataobjekt:

  • Vektordata som beskriver geodata, punkt, linjer og polygon. Denne versjon har bare punktdata
  • Koplingsdata: Språklige referanser til geodata-objektene vanligvis adresser eller matrikkelnummer. De geografiske objektenes gyldighet på tidsaksen kan (og bør) representeres her
  • Analysedata: Analysedata på individnivå (bosteder, personer, hushold)
  • Rasterdata: Georefererte og rektifiserte historiske kart.

Vektordata

Vektordata (Geodata) består av punkt, linjer og polygon som beskriver og konkrete geografiske objekter som hus, tomter, gater Last ned ub_pointz.zip og pakk ut zip-filen til ein katalog under ArcGIS Desktop eller QGIS desktop. Vektordata har datum og projeksjon WGS84 UTM 33 med EPSG 32633 (Sjekk: UTM sone 33 basert på EUREF89). Primærnøkkel er hbrgeoid. ub_point.

Koplingsdata

Koplingsdata består av abstrakte referanser til de geografiske objektene, eksempelvis matrikkel til tomt, adresse til hus og navn. De abstrakte geografiske objektene er språklige referanser til de konkrete geografiske objektene (punkt, linje, polygon). Kopling mellom abstrakte og konkrete geografiske objekt er hbrgeoid. En annen viktig egenskap ved koplingsdata er at tid kan representeres i koplingsdata, dvs. de geografiske objektenes "levetid" langs tidsaksen.

Tilgjengelige koplingsdata er:

  • Bosteder i folketellingen 1910 bost1910.csv Bruk autoid eller kildeid for å kople til bostedsfila for folketellinga 1910
  • Lenkefilen med kopling til digitalarkivet: lenktab.csv

Det kan vere lurt å laste desse fila i MS-Excel og konvertere den til MS-Excel 2003-format (*.xls) før du importerer den i ArcGIS. Merk at filer i uft-8 må settes inn som tekst i et eksisterende ark slik at MS-Excel kan konverter filen korrekt

Analysedata

Urbgeo har egentlig ingen egne analysedata. Koplingsdata er referanser eller koplinger til individdata og andre analysedata. Koplingen mellom analysedata til de konkrete geografiske objekt er hbrgeoid. Merk at analysedata er koplet direkte til vektordata og ikke via koplingsdata. Dersom de geografiske objektene har tidshorisont må denne representeres gjennom en kopling via koplingsdata.

Noen eksempler på analysedata: Døde i Trondheim 1840-1911 [Ta_kontakt_med_hilde trhbegr.csv] Analysedata inneheld nøkkelen hbrgeoid. Dette er ein framand-nøkkel hbrgeoid som må koplast til primærnøkkelen i geodata

Utdrag fra Hallstein Myklebost tabell (Tettsteder i Norge): hallmykl.csv Koples til geodata med hbrgeoid

Rasterdata

Last ned Trondheim 1868 trh1868r.jpg og trh1868r.jpw

Last ned Trondheim 1883 (Del 1) trh1883r.jpg og trh1883r.jpw Last ned Trondheim 1883 (Del 2) trh1883vr.jpg og trh1883vr.jpw

Andre bakgrunnsdata

Kartverket WMS server: Åpne WMS tjenester

Import til annen Postgres-database

Slett eksisterande tabellar

drop table hallmykl;
drop table nsd1910a;
drop table gtab1910;

\i urbgeo1.sql
\i urbgeo2.sql