https://wiki.app.uib.no/hbrgeo/index.php?title=Urbgeo/ft1900&feed=atom&action=history
Urbgeo/ft1900 - Revisjonshistorikk
2024-03-29T08:40:42Z
Revisjonshistorikk for denne siden
MediaWiki 1.35.6
https://wiki.app.uib.no/hbrgeo/index.php?title=Urbgeo/ft1900&diff=1618&oldid=prev
Hhiso: Ny side: =Lasting av 1900-tellingen= <pre class="code"> DROP TABLE IF EXISTS "ed1900"; CREATE TABLE "ed1900" ( "id" VARCHAR(25) NOT NULL, "kildeid" INTEGER NOT NULL, "kretsnr" VARCHAR(25...
2016-11-13T06:29:09Z
<p>Ny side: =Lasting av 1900-tellingen= <pre class="code"> DROP TABLE IF EXISTS "ed1900"; CREATE TABLE "ed1900" ( "id" VARCHAR(25) NOT NULL, "kildeid" INTEGER NOT NULL, "kretsnr" VARCHAR(25...</p>
<p><b>Ny side</b></p><div>=Lasting av 1900-tellingen=<br />
<br />
<pre class="code"><br />
<br />
DROP TABLE IF EXISTS "ed1900";<br />
<br />
CREATE TABLE "ed1900" (<br />
"id" VARCHAR(25) NOT NULL,<br />
"kildeid" INTEGER NOT NULL,<br />
"kretsnr" VARCHAR(25) NOT NULL,<br />
"tk_navn" VARCHAR(255),<br />
"sogn" VARCHAR(50),<br />
"prestegjeld" VARCHAR(50),<br />
"herred_by" VARCHAR(50),<br />
"merknader" VARCHAR(255),<br />
PRIMARY KEY (id)<br />
);<br />
<br />
COPY ed1900 FROM '/home/arne/urbgeo/data/ft1900hele_tellingskretser.csv' DELIMITER ';' CSV HEADER QUOTE '"' ENCODING 'utf-8' NULL '';<br />
<br />
<br />
Alter table ed1900 add column asciiname varchar(50);<br />
alter table ed1900 add column byland INTEGER;<br />
<br />
update ed1900 set asciiname = replace(herred_by,'å','aa');<br />
update ed1900 set asciiname = replace(asciiname,'Å','Aa');<br />
update ed1900 set asciiname = replace(asciiname,'æ','e');<br />
update ed1900 set asciiname = replace(asciiname,'ø','o');<br />
update ed1900 set asciiname = replace(asciiname,'Ø','O');<br />
update ed1900 set asciiname = replace(asciiname,'Æ','E');<br />
update ed1900 set asciiname = replace(asciiname,'Kristiania','Oslo');<br />
update ed1900 set asciiname = replace(asciiname,'Fredrikshald','Halden');<br />
update ed1900 set asciiname = replace(asciiname,'Trondhjem','Trondheim');<br />
update ed1900 set asciiname = replace(asciiname,'Porsgrund','Porsgrunn');<br />
update ed1900 set asciiname = replace(asciiname,'Stenkjer','Steinkjer');<br />
update ed1900 set asciiname = 'Sokndal' where prestegjeld is null and herred_by='Sogndal';<br />
<br />
-- update ed1900 set byland=1 from hallmykl where ed1900.asciiname=hallmykl.asciiname;<br />
update ed1900 set byland=1 where ed1900.herred_by like '%pstad';<br />
update ed1900 set byland=1 where ed1900.herred_by like '%ested';<br />
update ed1900 set byland=1 where herred_by like 'Gjøvik%' or herred_by like 'Hamar' or herred_by like 'Kongsvinger%';<br />
update ed1900 set byland=1 where herred_by like 'Drøbak%' or herred_by like 'Fredriksstad%' or herred_by like 'Sarpsborg%';<br />
update ed1900 set byland=1 where herred_by like 'Fredrikshald%' or herred_by like '%Hvidsten%' or herred_by like 'Sarpsborg%';<br />
update ed1900 set asciiname = replace(asciiname,' kjopstad','');<br />
update ed1900 set asciiname = replace(asciiname,' ladested','');<br />
update ed1900 set asciiname = replace(asciiname,'Drobaks','Drobak');<br />
update ed1900 set asciiname = replace(asciiname,'Ladestedet ','');<br />
update ed1900 set asciiname = replace(asciiname,'Soon Ladested','Son');<br />
update ed1900 set asciiname = replace(asciiname,'Hvidsten','Hvitsten');<br />
<br />
DROP TABLE IF EXISTS "by1900";<br />
<br />
CREATE TABLE "by1900" (<br />
"hmid" INTEGER NOT NULL,<br />
"bynamn" VARCHAR(25),<br />
"nsdknr" INTEGER,<br />
"asciiname" VARCHAR(25)<br />
<br />
);<br />
<br />
insert into by1900 (hmid,bynamn,nsdknr,asciiname) select count(*), herred_by, count(*), min(asciiname) from ed1900 where byland=1 group by herred_by;<br />
<br />
update by1900 set hmid=hallmykl.hmid from hallmykl where by1900.asciiname=hallmykl.asciiname;<br />
update by1900 set nsdknr=nsd1910a.nsdknr from nsd1910a where by1900.asciiname=nsd1910a.asciiname;<br />
<br />
delete from by1900 where bynamn='Levanger';<br />
delete from by1900 where bynamn='Holen';<br />
delete from by1900 where bynamn='Drøbaks';<br />
alter table by1900 add primary key (hmid);<br />
<br />
DROP TABLE IF EXISTS "kilde1900";<br />
<br />
CREATE TABLE "kilde1900" (<br />
"hmid" INTEGER,<br />
"kildeid" INTEGER NOT NULL,<br />
"asciiname" VARCHAR(25),<br />
PRIMARY KEY (kildeid)<br />
);<br />
<br />
insert into kilde1900 (hmid,kildeid,asciiname) select count(*), kildeid, min(asciiname) from ed1900 where byland=1 group by kildeid;<br />
update kilde1900 set hmid=hallmykl.hmid from hallmykl where kilde1900.asciiname=hallmykl.asciiname;<br />
<br />
</pre></div>
Hhiso