Vis kilden til Urbgeo/gisdbv-0.1

=Etablering av UrbGeo GISdatabase=
Dette dokumentet viser steg-for-steg dokumentasjon av GIS-databasen urbgeo med DBMS PostgreSQL.

Dersom du berre er interessert i å laste ned geodata gå til siste hovedpunkt i dokumentasjonen,"Import av data".
==Oppretting av databaser og hjelpedata==
Forutsetningen er at PostgreSQL 9.x med tilhørende GIS-ekstensjon postgis er installert på ein linux-maskin. Denne dokumentasjonen er basert på Ubuntu Linux 14.04 LTS, postgreSQL 9.5 og postgis 2.2.2. 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.


<pre class="code">
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

urbgeo=#create extension postgis;


</pre>

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

====Tettsted og by====
Første hjelpetabell er definisjon og data for tettstader. Data er henta frå Hallstein Myklebust, Tettsteder i Norge 1875-1950. 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.

<pre class="code">
--DROP DATABASE IF EXISTS "urbgeo";
--CREATE DATABASE "urbgeo";

\c "urbgeo";

--
-- 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),
  PRIMARY KEY (hmid)
);

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

update "hallmykl" set asciiname = 'Mo i Rana' where asciiname='Mo';

</pre>

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 [http://solli.h.uib.no/showtab.php?tabname=hallmykl vis hallmykl]

====Kommuner og fylker====

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.

<pre class="code">

-- 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;

-- 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');

-- 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;

</pre>

Utdrag/dump av tabellen [http://solli.h.uib.no/showtab.php?tabname=nsd1910a vis nsd1910a]

====Tellekretser og bytabell i 1910====

På grunnlag av tellekretsene i 1910-tellingen blir det dannet tre tabeller: ED-tabellen (Enumeration District), Bytabell og en kildetabell.

<pre class="code">

-- Oppretter tabell for teljekrets (ed)

DROP TABLE IF EXISTS "ed1910";

CREATE TABLE "ed1910" (
  "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 ed1910 FROM '/home/arne/urbgeo/data/ft1910heleKrets.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';

-- Legg til ekstra felt id ed

Alter table ed1910 add column asciiname varchar(25);
alter table ed1910 add column byland INTEGER;

-- Standardiserer asciiname

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

-- Merker teljekretser som er i by

update ed1910 set byland=1 from hallmykl  where ed1910.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)

);


-- laster bytabellen frå teljekretsar

insert into by1910 (hmid,bynamn,nsdknr,asciiname) select count(*), herred_by, count(*), min(asciiname) from ed1910 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);

</pre>
Kildetabell (Digitalarkivet)

<pre class="code">


-- 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 ed1910 where byland=1 group by kildeid;

-- Merker aller kilder med tettstadid (byid)

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

</pre>

====Folketellingen 1900====

På tilsvarende måte er det generert hjelpetabeller for [[Urbgeo/ft1900|1900-tellingen]]

===Gatetabeller===
<pre class="code">

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 gtab1910;
create table gtab1910 (
    autoid integer not null,
    kildeid integer,
    hmid integer,
    gateognr varchar(255),
    bydel varchar(50),
    gaardnr varchar(50),
    gatenamn varchar(50),
    gatenr varchar(50),
    gatenum integer,
    gatelitr varchar(50),
    primary key (autoid)
);

INSERT INTO gtab1910 (autoid, kildeid, gateognr, bydel, gaardnr) select autoid, kildeid, gateognr, bydel, gaardnr from tmptab where kildeid is not null;

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

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

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

drop table if exists gete1910;
drop table if exists gate1910;
create table gate1910 (
    hmid integer not null,
    gatenamn varchar(50) not null,
    anthus integer,
    gateognr varchar(255),
    gatenr varchar(50),
    mingnum integer,
    maxgnum integer,
    primary key (hmid, gatenamn)
);

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


</pre>


Utdrag/dump av tabellen [http://solli.h.uib.no/showtab.php?tabname=gtab1910 vis gtab1910]

==Geokoding==
Geokoding av gatetabellen skjer førebels manuelt ved hjelp av ArcGIS desktop. Gatetabellen gtab1910 må derfor eksporterast til csv-format for å geokodast i ArcGIS. Resultatet frå geokoding av gtab1910 blir lasta inn nedanfor i tabellen geo1910u. I tillegg blir ein landtabell, fylkestabell og bytabellen geokoda for å få punkt for landa i Norden, dei norske fylka og tettstadane i urbgeo.

Eksport av gatetabellen gtab1900 til csv-format med psql copy-commando. Merk at postgres-brukaren må ha skrivetilgang på export-katalogen, t.d. ved chown postgresl export

<pre class="code">
copy (select gtab1910.*, hallmykl.fylke, hallmykl.kommune, hallmykl.asciiname,'Norway' as country from gtab1910, hallmykl where gtab1910.hmid=hallmykl.hmid and gtab1910.gatenr is not null) to '/home/arne/urbgeo/export/gtab1910.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';
</pre>

Tabellen gtab1910.csv må leggjst til eit prosjekt i ArcGIS med utm33 som koordinatsystem. I rutina for geokoding er gateognr sett som adresse, kommune er sett som sub-region, fylke som region og country ("Norway") som country. Resultatet er lagt på ein shapefile med namn geo1910u.

For å importere dei geokoda data (shapefila geo1910u) tilbake i Postgresql må datafila konverterast frå shapefile-formatet til sql-load fil. Til dette formålet er linux-programmet shp2psql nytta:

<pre class="code">
linux>shp2pgsql county.shp county >county.sql
linux>shp2pgsql country.shp country >country.sql
linux>shp2pgsql geo1910u.shp geo1910u >geo1910u.sql
linux>shp2pgsql geokhm.shp geokhm >geokhm.sql</pre>

==Laste GIS data==

Etter at gatetabellen gtab1900 er geokoda (førre punkt) i ArcGIS desktop blir resultatet frå geokodinga lagra som ein shapefile (*.shp). Shape-formatet er standard filformatet for ArcGIS-produkta. Shapefila er konvertert med sh2spgsl til postgres import-format og blir lasta med sql-setningar, jf. førre punkt

Dei tre geodatafilene country (land), county (fylke) og geo1910 (gater) blir lasta i psql med import-kommandoen.

<pre class="code">
psql>\i country.sql
psql>\i county.sql
psql>\i geo1910u.sql
psql>\i geokhm.sql

===Land===
Punkt for land (country.sql) til country tabell

Første del av country.sql:

<pre class="code">
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "country" (gid serial,
"loc_name" varchar(23),
"match_addr" varchar(254),
"addr_type" varchar(50),
"url" varchar(200),
"country" varchar(30),
"x" numeric,
"y" numeric,
"__xmin" numeric,
"__xmax" numeric,
"ymin" numeric,
"ymax" numeric,
"code" float8,
"country_1" varchar(254));
ALTER TABLE "country" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','country','geom','0','POINT',2);
INSERT INTO "country" ("loc_name","match_addr","addr_type","url","country","x","y","__xmin","__xmax","ymin","ymax","code","country_1",geom) VALUES ('Country','Norway','Country','https://no.wikipedia.org/wiki/Norge','NOR','7.944010','60.498604','4.643080','31.148150','57.974370','71.183930','47.000000','Norge','010100000030F32293AAC61F40841B3545D23F4E40');

</pre>
Etter Norge følger poster for Sverige, Danmark og Tyskland

===Fylker===
Berre norske fylker, men her kunne også svenske len og danske amt ha blitt lastet

Første del av county.sql:

<pre class="code">
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "county" (gid serial,
"loc_name" varchar(23),
"match_addr" varchar(254),
"addr_type" varchar(50),
"url" varchar(200),
"region" varchar(120),
"country" varchar(30),
"x" numeric,
"y" numeric,
"__xmin" numeric,
"__xmax" numeric,
"ymin" numeric,
"ymax" numeric,
"nummer" float8,
"navn" varchar(254),
"admsenter" varchar(254),
"folketall" float8,
"areal" float8,
"antkomm" float8,
"målform" varchar(254),
"ordfører" varchar(254),
"parti" varchar(254),
"fylkesmann" varchar(254),
"country_1" varchar(254));
ALTER TABLE "county" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','county','geom','0','POINT',2);
INSERT INTO "county" ("loc_name","match_addr","addr_type","url","region","country","x","y","__xmin","__xmax","ymin","ymax","nummer","navn","admsenter","folketall","areal","antkomm","målform","ordfører","parti","fylkesmann","country_1",geom) VALUES ('NOR.AdminPlaces','Østfold','Admin','https://no.wikipedia.org/wiki/Østfold','Østfold','NOR','11.323242','59.376966','10.586242','12.060242','58.639966','60.113966','1.000000','Østfold','Sarpsborg','287198.000000','4180.680000','18.000000','bokmål','Ole Haabeth','Ap','Trond Rønningen','Norway','0101000000DEFED80021C51141B640139565215941');
</pre>
Og så videre de øvrige fylkene

===Gater===
Første del av geo1910u med insert av første record av totalt ca. 20000 postar.
<pre class="code">
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "geo1910u" (gid serial,
"loc_name" varchar(23),
"status" varchar(1),
"score" numeric,
"match_type" varchar(2),
"match_addr" varchar(254),
"addr_type" varchar(50),
"type" varchar(120),
"placename" varchar(120),
"place_addr" varchar(200),
"phone" varchar(20),
"url" varchar(200),
"rank" varchar(20),
"addbldg" varchar(100),
"addnum" varchar(80),
"addnumfrom" varchar(10),
"addnumto" varchar(10),
"side" varchar(1),
"stpredir" varchar(20),
"stpretype" varchar(50),
"stname" varchar(100),
"sttype" varchar(30),
"stdir" varchar(20),
"staddr" varchar(254),
"nbrhd" varchar(80),
"city" varchar(120),
"subregion" varchar(120),
"region" varchar(120),
"postal" varchar(20),
"postalext" varchar(70),
"country" varchar(30),
"langcode" varchar(6),
"distance" numeric,
"x" numeric,
"y" numeric,
"displayx" numeric,
"displayy" numeric,
"__xmin" numeric,
"__xmax" numeric,
"ymin" numeric,
"ymax" numeric,
"arc_addres" varchar(100),
"arc_neighb" varchar(50),
"arc_city" varchar(50),
"arc_subreg" varchar(50),
"arc_region" varchar(50),
"arc_postal" varchar(20),
"arc_post_1" varchar(20),
"arc_countr" varchar(100),
"autoid" numeric(10,0),
"kildeid" numeric(10,0),
"hmid" numeric(10,0),
"gateognr" varchar(254),
"bydel" varchar(254),
"gaardnr" varchar(254),
"gatenamn" varchar(254),
"gatenr" numeric(10,0),
"gatenum" numeric(10,0),
"gatelitr" varchar(254),
"fylke" varchar(254),
"kommune" varchar(254),
"asciiname" varchar(254),
"country_1" varchar(254),
"hbrgeoid" numeric(10,0));
ALTER TABLE "geo1910u" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','geo1910u','geom','32633','POINT',2);
INSERT INTO "geo1910u" ("loc_name","status","score","match_type","match_addr","addr_type","type","placename","place_addr","phone","url","rank","addbldg","addnum","addnumfrom","addnumto","side","stpredir","stpretype","stname","sttype","stdir","staddr","nbrhd","city","subregion","region","postal","postalext","country","langcode","distance","x","y","displayx","displayy","__xmin","__xmax","ymin","ymax","arc_addres","arc_neighb","arc_city","arc_subreg","arc_region","arc_postal","arc_post_1","arc_countr","autoid","kildeid","hmid","gateognr","bydel","gaardnr","gatenamn","gatenr","gatenum","gatelitr","fylke","kommune","asciiname","country_1","hbrgeoid",geom) VALUES ('NOR.StreetName','M','97.50','A','4, 1747 Sarpsborg, Østfold','StreetName',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'4',NULL,NULL,'4',NULL,NULL,'Sarpsborg','Østfold','1747',NULL,'NOR','NOR','0.00','11.163927','59.213067','11.163927','59.213067','11.161927','11.165927','59.211067','59.215067','D.4',NULL,'Sarpsborg',NULL,'Østfold',NULL,NULL,'Norway','955','36334','18','D.4',NULL,'867','D.','4','4',NULL,'Østfold','Sarpsborg','Sarpsborg','Norway','480491400','0101000020797F0000FAF8010567271141343E407417105941');

</pre>

Første del av geokhm.sql med første post.

<pre class="code">
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "geokhm" (gid serial,
"loc_name" varchar(23),
"match_addr" varchar(254),
"addr_type" varchar(50),
"url" varchar(200),
"city" varchar(120),
"subregion" varchar(120),
"region" varchar(120),
"country" varchar(30),
"langcode" varchar(6),
"x" numeric,
"y" numeric,
"hmid" float8,
"fylke" varchar(254),
"tettstad" varchar(254),
"kommune" varchar(254),
"asciiname" varchar(254),
"country_1" varchar(254));
ALTER TABLE "geokhm" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','geokhm','geom','0','POINT',2);
INSERT INTO "geokhm" ("loc_name","match_addr","addr_type","url","city","subregion","region","country","langcode","x","y","hmid","fylke","tettstad","kommune","asciiname","country_1",geom) VALUES ('NOR.AdminPlaces','Halden, Østfold','SubAdmin','https://no.wikipedia.org/wiki/Halden',NULL,'Halden','Østfold','NOR','NOR','11.384510','59.121500','5.000000','Østfold','Halden','Halden','Halden','Norway','01010000000AEB09105CE3114185621CD474055941');
</pre>

==Koding av data med geodata==
===Generering av punkt-tabell===

Oppretting av temporær tabell for punkt

<pre class="code">
drop table if exists ub_point_0;
create table ub_point_0 (gid serial,
"loc_name" varchar(50),
"loc_type" integer,
"x" numeric,
"y" numeric,
hbrgeoid varchar(18));

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


Import av punkt fra land, fylke og gateadresser fra geokoding
county - punkt for norske fylker
country - punkt for land i Norden
geo1910u - punkt for gateadresser
geokhm - punkt for tettstader

<pre class="code">
drop table county;
drop table country;
drop table geo1910u;
drop table geokhm;

\i utm33/country.sql;
\i utm33/county.sql;
\i utm33/geo1910u.sql;
\i utm33/geokhm.sql;
</pre>

Innsetting av punkt-data frå geo-koding

Type-koder;
0=ingen
1=land
2=fylke
3=kommune
4=tettstad (by)
5=
6=
7=gateadresse
8=hus
9=Anna

<pre class="code">

-- Setter inn fylke i temporær punkt-tabell med loc_type = 2 for fylke

insert into ub_point_0 (loc_name, loc_type, hmid, x, y, geom) select match_addr, 2, 0, x, y, geom from county;

-- Setter inn land i temporær punkt-tabell med loc_type = 1 for land

insert into ub_point_0 (loc_name, loc_type, hmid, x, y, geom) select match_addr, 1, 0, x, y, geom from country;

-- Setter inn tettstad i temporær punkt-tabell med loc_type = 4 for tettstad

insert into ub_point_0 (loc_name, loc_type, hmid, x, y, geom) select tettstad, 4, hmid, x, y, geom from geokhm;

-- Setter inn gater i temporær punkt-tabell med loc_type = 7 for gate

insert into ub_point_0 (loc_name, loc_type, hmid, x, y, geom) select gateognr, 7, hmid, x, y, geom from geo1910u;

</pre>

Tildeling av hbrgeo-id
<pre class="code">

-- oppdaterer hbrgeoid i ub_point_0 på formatet 'nnExxxxxxxNyyyyyyy'.
-- Merk at string concat i postgresql er ||

-- select trim(to_char(loc_type,'9')) || 'E' || trim(to_char(x*100000,'0000000')) || 'N' || trim(to_char(y*100000,'0000000')) from ub_point_0 where x<32 and y<80 and x>5 and y>58;

update ub_point_0 set hbrgeoid='1' || trim(to_char(loc_type,'9')) || 'E' || trim(to_char(x*100000,'0000000')) || 'N' || trim(to_char(y*100000,'0000000'))  where x<32 and y<80 and x>5 and y>58;
</pre>

Etablering av unik tabell for hbrgeoid med oppretting av tabell for unike punkt

<pre class="code">
drop table if exists ub_point;
create table ub_point (gid serial,
"loc_name" varchar(50),
"loc_type" integer,
"hmid" integer,
"x" numeric,
"y" numeric,
hbrgeoid varchar(18));

ALTER TABLE "ub_point" ADD PRIMARY KEY (hbrgeoid);
SELECT AddGeometryColumn('','ub_point','geom','0','POINT',2);
</pre>

====Etablering av unike punkt====

Initiering av tabell med unike lokasjoner for hbgrgeoid

<pre class="code">


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

</pre>

=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 geoid==
<pre class="code">

-- Sette inn geokode i tabellen for bustader

Alter table gtab1910 add column hbrgeoid varchar(18);

update gtab1910 set hbrgeoid = ub_point.hbrgeoid from ub_point where ub_point.hmid=gtab1910.hmid and ub_point.loc_name=gtab1910.gateognr;

</pre>

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

==Eksport av 1900 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

<pre class="code">
COPY gtab1910 (autoid, hbrgeoid, hmid) to '/home/arne/urbgeo/export/bost1910.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'Latin1' NULL '';
</pre>

Denne filen kan også lastast ned her: [http://solli.h.uib.no/urbgeo/export/bost1910.csv bost1910.csv]
Det kan vere lurt å laste denne fila i MS-Excel og konvertere den til MS-Excel 2003-format (*.xls) før du importerer den i ArcGIS.

==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).

<pre class="code">
linux>pgsql2shp urbgeo ub_point
linux>zip -r ub_pointz.zip ub_point.* 
</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.

Last ned ub_pointz.zip [http://solli.h.uib.no/urbgeo/export/ub_pointz.zip ub_pointz.zip]

===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
<pre class="code">
linux>pg_dump -t ub_point urbgeo -f urbgeo1.sql
</pre>

Eksport av hjelpetabeller og datatabeller for import i postgresql
<pre class="code">
pg_dump -t hallmykl -t nsd1910a -t gtab1910 urbgeo -f urbgeo2.sql
</pre>

=Import av data=
==Import til ArcGIS==
===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.

Primærnøkkel er hbrgeoid

Tilgjengelige analysedata er:
Bosteder i folketellingen 1910 [http://solli.h.uib.no/urbgeo/export/bost1910.csv bost1910.csv] Bruk autoid for å kople til bostedsfila for folketellinga 1910

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 [http://solli.h.uib.no/urbgeo/export/ub_pointz.zip ub_point].

===Rasterdata===
Last ned Trondheim 1868 [http://solli.h.uib.no/urbgeo/rect/trh1868r.jpg trh1868r.jpg] og [http://solli.h.uib.no/urbgeo/rect/trh1868r.jpg trh1868r.jpw]

Last ned Trondheim 1883 (Del 1) [http://solli.h.uib.no/urbgeo/rect/trh1883r.jpg trh1883r.jpg] og [http://solli.h.uib.no/urbgeo/rect/trh1883r.jpw trh1883r.jpw] 
Last ned Trondheim 1883 (Del 2) [http://solli.h.uib.no/urbgeo/rect/trh1883vr.jpg trh1883vr.jpg] og [http://solli.h.uib.no/urbgeo/rect/trh1883vr.jpw trh1883vr.jpw]

===Andre bakgrunnsdata===
Kartverket WMS server: [http://status.kartverket.no/tjenester/openwms.py Åpne WMS tjenester]

==Import til annen Postgres-database==

Slett eksisterande tabellar
<pre class="code">
drop table hallmykl;
drop table nsd1910a;
drop table gtab1910;

\i urbgeo1.sql
\i urbgeo2.sql

</pre>