[opengeodb] Erfahrungen mit der Umgang mit der neue Version 'opengeodb-02513_2007-10-02.sql'
Mark Johnson
mj10777 at googlemail.com
Son Okt 7 14:10:19 CEST 2007
Als ich eine 'Fehlermeldung' zusammengestellen wollte und um dann eine
Lösungansatz vorzuschagen,
ist mir aufgefallen wie man die Abfragen mit der bestehede Struktur
vornehmen soll.
Daher stimmen ein Paar der 'Feststellungen' nicht, was sich später
herausgestellt wieder.
Ich habe daher die 'Vorarbeit' so wie geschrieben hier wiedergegeben, in
der Hoffnung das
andere die noch Probleme mit der 'neue' Struktur haben den Arbeitsweg
nachvollziehen kann.
Ein paar praktisch Sql-Beispiele und deren Ergebnise werden gezeigt ohne
die `geodb_hierarchies`
anzusprechen.
Mein Ziel ist es langfristig eine Database aufzubauen mit der jetzige (und
frühre)
Berliner Straßenamen mit Positionsangaben (Längengrand/Breitengrad) ggf.
mit Hausnummern um auf
eine Karte diese Punkt anzuzeigen (bei historische Addressen ggf. auf
historische Karten).
Daher war mein erste Ziel eine Liste von eine Ort und Postleitzahl mit
deren
eindeutige Positionangabe zubekommen - falls keine Adresse gefunden wird -
konnte den Gebiet angezeigt
werden wo voraussichlich der gesuchte Adresse sich befindet.
Praktische Beispiel in Goggle Maps :
'Fuggerstraße 2, 10777 Berlin'
- laut Google Maps gibt es diese Addresse nicht (was nicht stimmt, 1 (als
Haus) nicht - aber 2 schon)
- Google Maps geht in die mitte der Fuggerstraße als ob man nach
'Fuggerstraße, 10777 Berlin' gesucht hätte.
'Buggerstraße 2, 10777 Berlin'
- laut Google Maps gibt es diese Addresse nicht (was stimmt)
- Google Maps meldet eine Fehler und das wars.
- Ziel sollte sein '10777 Berlin' zu suchen, was bei Google Maps die
Zustellamt von 10777 ist.
Die 13.613 Datensätze (Eindutige Straßenname per Postleitzahl) für Berlin
werde ich in der nächste Woche einspielen und ausprobieren.
Manche Sql-Abfragen sehen zwar furchtbar aus (vorallen der letzte), aber
die reaktionszeiten sind hervorragend.
----------------------------------------------------
Nachdem ich gestern den ganzen Tag mit der neue 'opengeodb-02513_2007-
10-02.sql.gz'
beschäftigt habe, bin ich zur der Ansicht gekommen, das bei der Umstellung
eine
gravierende Fehler bei der Umstellung passiert ist.
Bei der Abfrage :
SELECT DISTINCT land.text_type AS "Type_Number"
FROM geodb_textdata land
bekommt man 12 Datensätze wie unter 'Type_Number' zu sehen ist.
Bei der Abfrage :
SELECT DISTINCT land.text_type AS "Type_Number", types.name AS
"Type_Names"
FROM geodb_textdata land, geodb_type_names
WHERE land.text_type = types.type_id
bekommt man 11 Datensätze wie unter 'Type_Number' und 'Type_Names' zu sehen
ist,
OHNE die Zeile '45.872 500100000 * Name'
Bei der Abfragen :
SELECT count(*)
FROM geodb_textdata land
WHERE (land.text_type = '400100000')
habe ich folgen Tabelle aufgebaut um eine Überblick zu bekommen:
Count(*) Type_Number Type_Names
41.806 400100000 Teil von
41.807 400200000 Ebene
38.937 400300000 Typ
45.872 500100000 * Name
41.795 500100002 Sortiername
13 500100003 ISO_3166_2
48.397 500300000 Postleitzahl
189 500300001 Postleitzahl Position
12.406 500400000 Telefonvorwahl
16.651 500500000 KFZ-Kennzeichen
38.121 500600000 Amtlicher Gemeindeschlüssel
12.120 500700000 Verwaltungszusammenschluss
= 338.114
Bei der Abfrage :
SELECT type_id, name
FROM geodb_type_names
ORDER BY type_id
stellt man fest :
- es gibt keine '500100000' [war in die 0.2.4 Version mit der name =
'Name']
- 45.872 Datensätze wurde mit diese (nicht existiernde nummer) eingefügt.
- folgende Nummer werden nicht mit Inhalte in 'geodb_textdata' benützt
[spätere Anmerkung : werden für die Einstellung von 'geodb_locations'
benützt]
0 100100000 Erdteil
0 100200000 Staat/Land
0 100300000 Kanton
0 100300000 Bundesland
0 100400000 Regierungsbezirk
0 100500000 Landkreis
0 100600000 Politische Gliederung
0 100700000 Ortschaft
0 100800000 Postleitzahlgebiet
Bei der Abfrage [500100002 = Sortierung] :
SELECT land.loc_id AS "id_Land",
land.text_type AS "Type_Land",
land.text_val AS "Land"
FROM geodb_textdata land
WHERE ((land.text_val = 'Berlin') OR (land.text_val = 'Stuttgart')) AND
(land.text_type <> 500100002)
ORDER BY Land;
id_Land Type_Land Land
109 500100000 Berlin
319 500100000 Berlin
14356 500100000 Berlin
162 500100000 Stuttgart
591 500100000 Stuttgart
24718 500100000 Stuttgart
Wünchenswert wäre folgende Ergebnis :
id_Land Type_Land Land
109 100300000 Berlin
319 100500000 Berlin
14356 100700000 Berlin
Den begriff '500100000' kommt 45.873 in 'opengeodb-02513_2007-10-02.sql'
mal vor
- create table geodb_textdata wird es einmal verwendet - Überbleibsel von
alte Version ?
[Hier habe ich bemerkt wie diese System funktioniert. Wichtig ist der
Verwendung von 'geodb_locations']
Bei der Abfrage;
SELECT stadt.loc_id AS "id_Stadt",
local.loc_type AS "Type_Location",
form.text_val AS "Text_Stadt",
stadt.text_val AS "Stadt"
FROM geodb_textdata stadt,
geodb_textdata form,
geodb_locations local
WHERE ((stadt.text_val='Berlin') OR (stadt.text_val = 'Stuttgart')) AND
(stadt.text_type <> 500100002) AND
(stadt.loc_id = local.loc_id) AND
((local.loc_type = 100300000) OR
(local.loc_type = 100400000) OR
(local.loc_type = 100500000) OR
(local.loc_type = 100600000)) AND
(stadt.loc_id = form.loc_id) AND
(form.text_type = 400300000)
ORDER BY Stadt;
id_Stadt Type_Location Text_Stadt Stadt
109 100300000 Bundesland Berlin
319 100500000 Stadt Berlin
14356 100600000 Stadt Berlin
162 100400000 Regierungsbezirk Stuttgart
591 100500000 Landeshauptstadt Stuttgart
24718 100600000 Landeshauptstadt Stuttgart
[Warum '100600000' und nicht '100700000']
Eine Abfrage mit dir dazu gehörige Postleitzahlen :
SELECT stadt.loc_id AS "id_Stadt",
local.loc_type AS "Type_Location",
form.text_val AS "Text_Stadt",
stadt.text_val AS "Stadt",
code.text_val AS "PLZ"
FROM geodb_textdata stadt,
geodb_textdata form,
geodb_locations local,
geodb_textdata code
WHERE ((stadt.text_val='Berlin') OR (stadt.text_val = 'Stuttgart')) AND
(stadt.text_type <> 500100002) AND
(stadt.loc_id = local.loc_id) AND
((local.loc_type = 100300000) OR
(local.loc_type = 100400000) OR
(local.loc_type = 100500000) OR
(local.loc_type = 100600000)) AND
(stadt.loc_id = form.loc_id) AND
(form.text_type = 400300000) AND
(code.loc_id = local.loc_id) AND
(code.text_type = 500300000)
ORDER BY Stadt;
- Ergebnis : 226 insgesamt, die Abfrage dauerte 0.0100 sek.
- da die Postleitzahlen erst ab der '100600000' ebende eingetragen sind
brauchen wir die ander 4 nicht.
SELECT stadt.loc_id AS "id_Stadt",
local.loc_type AS "Type_Location",
form.text_val AS "Text_Stadt",
stadt.text_val AS "Stadt",
code.text_val AS "PLZ"
FROM geodb_textdata stadt,
geodb_textdata form,
geodb_locations local,
geodb_textdata code
WHERE (stadt.text_val='Berlin') AND
(stadt.text_type <> 500100002) AND
(stadt.loc_id = local.loc_id) AND
(local.loc_type = 100600000) AND
(stadt.loc_id = form.loc_id) AND
(form.text_type = 400300000) AND
(code.loc_id = local.loc_id) AND
(code.text_type = 500300000)
ORDER BY Stadt;
- Ergebnis (nur Berlin) : 191 insgesamt, die Abfrage dauerte 0.0132 sek.
Eine Abfrage mit dir dazu gehörige Postleitzahlen und
Längengrad/Breitengrad :
SELECT stadt.loc_id AS "id_Stadt",
local.loc_type AS "Type_Location",
form.text_val AS "Text_Stadt",
stadt.text_val AS "Stadt",
code.text_val AS "PLZ",
coord.lat AS "Latitude",
coord.lon AS "Longitude"
FROM geodb_textdata stadt,
geodb_textdata form,
geodb_locations local,
geodb_textdata code,
geodb_coordinates coord
WHERE (stadt.text_val = 'Berlin') AND
(stadt.text_type <> 500100002) AND
(stadt.loc_id = local.loc_id) AND
(local.loc_type = 100600000) AND
(stadt.loc_id = form.loc_id) AND
(form.text_type = 400300000) AND
(code.loc_id = local.loc_id) AND
(code.text_type = 500300000) AND
(coord.loc_id = code.loc_id)
ORDER BY PLZ
LIMIT 0,200;
- Ergebnis (nur Berlin) : 191 insgesamt, die Abfrage dauerte 0.0227 sek
Diese Ergebnis ist eigenlich sinnlos, da die Längengrad, Breitengrade immer
gleich sind.
- daher habe folgende neue 'type_names' hinzugefügt
INSERT INTO geodb_type_names VALUES(500200000,'de','Street');
INSERT INTO geodb_type_names VALUES(500200001,'de','Street Position');
INSERT INTO geodb_type_names VALUES(500200010,'de','Street-Number');
INSERT INTO geodb_type_names VALUES(500200011,'de','Street-Number
Position');
INSERT INTO geodb_type_names VALUES(500300001,'de','Postleitzahl
Position');
und 189 Datensätze mit der Geocode Abfragen von 'Goggle Maps mit Berliner
Postleitzahlen gefüllt.
Eine Abfrage mit dir dazu gehörige Postleitzahlen und deren
Längengrad/Breitengrad :
SELECT stadt.loc_id AS "id_Stadt",
local.loc_type AS "Type_Location",
form.text_val AS "Text_Stadt",
stadt.text_val AS "Stadt",
code.text_val AS "PLZ",
coord.lat AS "Latitude",
coord.lon AS "Longitude"
FROM geodb_textdata stadt,
geodb_textdata form,
geodb_locations local,
geodb_textdata code,
geodb_textdata plz_code,
geodb_coordinates coord
WHERE (stadt.text_val='Berlin') AND
(stadt.text_type <> 500100002) AND
(stadt.loc_id = local.loc_id) AND
((local.loc_type = 100300000) OR
(local.loc_type = 100400000) OR
(local.loc_type = 100500000) OR
(local.loc_type = 100600000)) AND
(stadt.loc_id = form.loc_id) AND
(form.text_type = 400300000) AND
(code.loc_id = local.loc_id) AND
(code.text_type = 500300000) AND
((plz_code.text_val = code.text_val) AND
(plz_code.text_type = 500300001)) AND
(coord.loc_id = plz_code.loc_id)
ORDER BY PLZ
LIMIT 0,200;
- Ergebnis (nur Berlin) : 189 insgesamt, die Abfrage dauerte 0.0798 sek.)
- hier haben wir eine Abweichung von 2 Datensätze
- 12529
- ist Berlin Mahlsdorf Süd und laut 'Goggle Maps' liegt in Land
Brandenburg
- 12623
- ist Flughafen Berlin-Schönefeld und liegt eindeutig in Land
Brandenburg.
Hier die Abfrage um diese beide PLZ rauszubekommen - 2 insgesamt, die
Abfrage dauerte 0.0283 sek.)
SELECT stadt.loc_id AS "id_Stadt",
local.loc_type AS "Type_Location",
form.text_val AS "Text_Stadt",
stadt.text_val AS "Stadt",
code.text_val AS "PLZ",
coord.lat AS "Latitude",
coord.lon AS "Longitude"
FROM geodb_textdata stadt,
geodb_textdata form,
geodb_locations local,
geodb_textdata code,
geodb_coordinates coord
WHERE (stadt.text_val = 'Berlin') AND
(stadt.text_type <> 500100002) AND
(stadt.loc_id = local.loc_id) AND
(local.loc_type = 100600000) AND
(stadt.loc_id = form.loc_id) AND
(form.text_type = 400300000) AND
(code.loc_id = local.loc_id) AND
(code.text_type = 500300000) AND
(coord.loc_id = code.loc_id) AND
(code.text_val NOT IN (
SELECT plz_code.text_val
FROM geodb_textdata stadt,
geodb_textdata form,
geodb_locations local,
geodb_textdata code,
geodb_textdata plz_code,
geodb_coordinates coord
WHERE (stadt.text_val='Berlin') AND
(stadt.text_type <> 500100002) AND
(stadt.loc_id = local.loc_id) AND
(local.loc_type = 100600000) AND
(stadt.loc_id = form.loc_id) AND
(form.text_type = 400300000) AND
(code.loc_id = local.loc_id) AND
(code.text_type = 500300000) AND
((plz_code.text_val = code.text_val) AND
(plz_code.text_type = 500300001)) AND
(coord.loc_id = plz_code.loc_id)
)
)
ORDER BY PLZ;
Auch wen Sql-Abfragen furchtbar aussehen, die reaktionszeiten sind
hervorragend und bringen die gewünschte Ergebnisse.
Mark Johnson, 2007-10-07
-------------- nächster Teil --------------
Ein Dateianhang mit HTML-Daten wurde abgetrennt...
URL: http://lists.phpbar.de/pipermail/opengeodb/attachments/20071007/641b2f7d/attachment-0001.html