Urbgeo/ft1900

Lasting av 1900-tellingen

DROP TABLE IF EXISTS "ed1900";

CREATE TABLE "ed1900" (
  "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 (id)
);

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


Alter table ed1900 add column asciiname varchar(50);
alter table ed1900 add column byland INTEGER;

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

-- update ed1900 set byland=1 from hallmykl  where ed1900.asciiname=hallmykl.asciiname;
update ed1900 set byland=1 where ed1900.herred_by like '%pstad';
update ed1900 set byland=1 where ed1900.herred_by like '%ested';
update ed1900 set byland=1 where herred_by like 'Gjøvik%' or herred_by like 'Hamar' or herred_by like 'Kongsvinger%';
update ed1900 set byland=1 where herred_by like 'Drøbak%' or herred_by like 'Fredriksstad%' or herred_by like 'Sarpsborg%';
update ed1900 set byland=1 where herred_by like 'Fredrikshald%' or herred_by like '%Hvidsten%' or herred_by like 'Sarpsborg%';
update ed1900 set asciiname = replace(asciiname,' kjopstad','');
update ed1900 set asciiname = replace(asciiname,' ladested','');
update ed1900 set asciiname = replace(asciiname,'Drobaks','Drobak');
update ed1900 set asciiname = replace(asciiname,'Ladestedet ','');
update ed1900 set asciiname = replace(asciiname,'Soon Ladested','Son');
update ed1900 set asciiname = replace(asciiname,'Hvidsten','Hvitsten');

DROP TABLE IF EXISTS "by1900";

CREATE TABLE "by1900" (
  "hmid" INTEGER NOT NULL,
  "bynamn" VARCHAR(25),
  "nsdknr" INTEGER,
  "asciiname" VARCHAR(25)

);

insert into by1900 (hmid,bynamn,nsdknr,asciiname) select count(*), herred_by, count(*), min(asciiname) from ed1900 where byland=1 group by herred_by;

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

delete from by1900 where bynamn='Levanger';
delete from by1900 where bynamn='Holen';
delete from by1900 where bynamn='Drøbaks';
alter table by1900 add  primary key (hmid);

DROP TABLE IF EXISTS "kilde1900";

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

insert into kilde1900 (hmid,kildeid,asciiname) select count(*), kildeid, min(asciiname) from ed1900 where byland=1 group by kildeid;
update kilde1900 set hmid=hallmykl.hmid from hallmykl where kilde1900.asciiname=hallmykl.asciiname;