Spielereien
Kanu
Geopolitik
Business
TopoDB
POI
Klettersteigen
History
TransPool
Thermal Baden
Brokenstuben
Goldwaschen
Blog
Contact
Um die Wohnorte der IGOC Mitglieder kartografisch darstellen zu können braucht es einerseits die Adressen der Mitglieder, anderseits die Koordinaten der Wohnorte. Die erste Information erhalten wir durch die Adressliste der Mitglieder und wie wir an die Koordinaten der Wohnorte gelange beschreibt dieser Artikel.
Die Hüterin des Schatzes der Schweizerischen Koordinaten ist das Bundesamt für Landestopografie (swisstopo). Dort gibt es eine Seite “Amtliches Ortschaftenverzeichnis” wo man das Schweizer Ortschaftsverzeichnis herunterladen kann (Download).
Die Datei CSV Datei ortschaftenverzeichnis_plz_2056.csv.zip enthält die Koordinaten mit den Schweizerischen Landeskoordinaten, die CSV Datei ortschaftenverzeichnis_plz_4326.csv.zip enthält die Koordinaten entsprechend dem Geographic Coordinate System (GCS). Für unsere Zwecke brauchen wir die zweite Datei.
Nachdem die Datei heruntergeladen und enpackt ist, sieht sie wie folgt aus:
$ unzip ortschaftenverzeichnis_plz_4326.csv.zip
$ head AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv
Ortschaftsname;PLZ4;Zusatzziffer;ZIP_ID;Gemeindename;BFS-Nr;Kantonskürzel;Adressenanteil;E;N;Sprache;Validity
Lausanne 25;1000;25;125;Lausanne;5586;VD;100%;6.682196168325835;46.55956189112911;fr;01.07.2008
Lausanne 26;1000;26;126;Lausanne;5586;VD;100%;6.695945346653699;46.554578943900474;fr;01.07.2008
Lausanne 27;1000;27;127;Lausanne;5586;VD;100%;6.681736656357112;46.542097581525205;fr;01.07.2008
Nachdem die Daten entpackt sind, kann man sie wie folgt in eine MariaDB/MySQL Datenbank laden:
SQL> DROP TABLE IF EXISTS wgs84;
SQL> CREATE TABLE wgs84 (
ortschaftsname VARCHAR(32)
, plz4 SMALLINT
, zusatzziffer SMALLINT
, zip_id SMALLINT
, gemeindename VARCHAR(32)
, bfs_nr SMALLINT
, kantonskuerzel CHAR(2)
, adressenanteil varchar(6)
, e DOUBLE
, n DOUBLE
, sprache VARCHAR(8)
, validity VARCHAR(12)
);
SQL> -- TRUNCATE TABLE wgs84;
SQL> LOAD DATA LOCAL INFILE 'AMTOVZ_CSV_WGS84.csv'
INTO TABLE wgs84
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
;
Query OK, 5712 rows affected, 2 warnings (0.086 sec)
Records: 5712 Deleted: 0 Skipped: 0 Warnings: 2
SQL> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column `test`.`wgs84`.`zip_id` at row 4799 |
| Warning | 1366 | Incorrect integer value: '' for column `test`.`wgs84`.`zip_id` at row 4800 |
+---------+------+----------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Die beiden Fehlerhaften Zeilen sind diese hier (fehlende ZIP_ID):
Ortschaftsname;PLZ4;Zusatzziffer;ZIP_ID;Gemeindename;BFS-Nr;Kantonskürzel;Adressenanteil;E;N;Sprache;Validity
Unterohringen;8472;01;;Seuzach;227;ZH;100%;8.711327036301487;47.53586157633128;de;01.01.2026
Oberohringen;8472;02;;Seuzach;227;ZH;100%;8.709665546;47.52703435069271;de;01.01.2026
Anschliessend kann man die Daten in der Datenbank abfragen:
SQL> SELECT * FROM wgs84 LIMIT 10;
+------------------+------+--------------+--------+------------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
| ortschaftsname | plz4 | zusatzziffer | zip_id | gemeindename | bfs_nr | kantonskuerzel | adressenanteil | e | n | sprache | validity |
+------------------+------+--------------+--------+------------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
| Lausanne 25 | 1000 | 25 | 125 | Lausanne | 5586 | VD | 100% | 6.682196168325835 | 46.55956189112911 | fr | 01.07.2008 |
| Lausanne 26 | 1000 | 26 | 126 | Lausanne | 5586 | VD | 100% | 6.695945346653699 | 46.554578943900474 | fr | 01.07.2008 |
| Lausanne 27 | 1000 | 27 | 127 | Lausanne | 5586 | VD | 100% | 6.681736656357112 | 46.542097581525205 | fr | 01.07.2008 |
| Lausanne | 1003 | 0 | 150 | Lausanne | 5586 | VD | 100% | 6.631889939736285 | 46.520568420793055 | fr | 01.07.2008 |
| Lausanne | 1004 | 0 | 151 | Lausanne | 5586 | VD | 100% | 6.618662406135503 | 46.52828218906587 | fr | 01.07.2008 |
| Lausanne | 1005 | 0 | 152 | Lausanne | 5586 | VD | 100% | 6.642767210311616 | 46.520888147719276 | fr | 01.07.2008 |
| Lausanne | 1006 | 0 | 153 | Lausanne | 5586 | VD | 100% | 6.633777063120928 | 46.512863192411494 | fr | 01.07.2008 |
| Lausanne | 1007 | 0 | 154 | Lausanne | 5586 | VD | 100% | 6.613581143143201 | 46.51789984112268 | fr | 01.07.2008 |
| Prilly | 1008 | 0 | 156 | Prilly | 5589 | VD | 100% | 6.604480669356251 | 46.536761096406835 | fr | 01.07.2008 |
| Jouxtens-Mézery | 1008 | 2 | 5852 | Jouxtens-Mézery | 5585 | VD | 100% | 6.595168883379037 | 46.54953871377771 | fr | 01.07.2008 |
+------------------+------+--------------+--------+------------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
10 rows in set (0.001 sec)
Bzw. etwas präziser:
SQL> SELECT ortschaftsname AS city, plz4 AS city_code, e AS lon, n AS lat
FROM wgs84 WHERE plz4 IN (8280, 4663, 6043);
+-------------+-----------+-------------------+--------------------+
| city | city_code | lon | lat |
+-------------+-----------+-------------------+--------------------+
| Aarburg | 4663 | 7.904271716719409 | 47.321443418782955 |
| Aarburg | 4663 | 7.889249714098425 | 47.313536073562474 |
| Aarburg | 4663 | 7.880309179095798 | 47.31255194439023 |
| Adligenswil | 6043 | 8.364849060491428 | 47.07037816052481 |
| Kreuzlingen | 8280 | 9.173740257895282 | 47.64491046067056 |
| Kreuzlingen | 8280 | 9.159171428030783 | 47.654149879509134 |
| Kreuzlingen | 8280 | 9.204470741840725 | 47.639949130372145 |
+-------------+-----------+-------------------+--------------------+
7 rows in set (0.003 sec)
Das ausmisten der Duplikate überlasse ich der geneigten Lesering und dem geneigten Leser… :-)