Erfahrungen mit der Umgang mit der neue Version 'opengeodb-02513_2007-10-02.sql'

 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

-- 
Mailingliste OpenGeoDB
Listenadresse: opengeodb-r1mDYR0DdAyzQB+pC5nmwQ@xxxxxxxxxxxxxxxx
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)