stadtname, lon, lat, postleitzahl - nur aus deutschland

[Lucas Mengel]

Hallo Zusammen!

ich habe mir ein SQL-Statement gebastelt, um "Stadtname, lon, lat,
postleitzahl" auszugeben.
Das dient dem Zweck, dass ich mir eine eigene, einfacherer Architektur
erstellen möchte, die nur diejenigen Daten enthält,
die ich auch wirklich benötige.

Bevor ich das Problem beschreibe, hier mein Ansatz:

SELECT
    plz.text_val AS stadt,
    position.lon,
    position.lat,
FROM
    geodb_textdata plz,
    geodb_textdata ort,
    geodb_coordinates position
WHERE
    position.loc_id = plz.loc_id AND
    ort.loc_id = plz.loc_id AND
    plz.text_type = 500300000 AND
    ort.text_type = 500100000
ORDER BY 2


Nun zum Problemchen: Wie schaffe ich es, das Ergebnis auf Orte in
Deutschland zu beschränken?
Ich muss dazu noch anfügen: Ich tue mich ziehmlich schwer diese
Ebenen-Geschichte zu verstehen, weil sich
alles in mir gegen diese undurchsichtige Architektur von
geodb_hierarchies streubt.
Ich hatte zwischendurch mal ansatzweise die Funktionalität halbwegs
verstanden, bin dann aber 2 Minuten später
so derart durcheinander gekommen, dass ich alles wieder vergessen habe
und im Grunde jedesmal von Vorn
beginnen muss.

Es bringt deshalb nicht allzu viel mich darauf hinzuweisen, dass ich
alle Ebenen durcharbeiten muss. ich weiss nämlich
weder, womit ich anfangen soll, geschweige wo ich aufhören muss zu suchen

Hänge schon die ganze Nacht daran  und bin deshalb entsprechend Müde.
In sämtlichen Foren stoße ich auf ratlosigkeit weil keiner weiss wie man
geodb_hierarchies füllen muss.
Andere hatten selbiges Problem und haben nach 2 Wochen aufgegeben.

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

[Martin Trautmann]

> Nun zum Problemchen: Wie schaffe ich es, das Ergebnis auf Orte in  Deutschland zu beschränken?

Hallo Lucas,

wenn deine Probleme derart massiv sind, dann beschraenke dich am einfachsten auf
http://fa-technik.adfc.de/code/opengeodb/D.sql

Setze die http://fa-technik.adfc.de/code/opengeodb/opengeodb-begin.sql davor und die
http://fa-technik.adfc.de/code/opengeodb/opengeodb-end.sql dahinter, dann hast du alle aktuellen Daten fuer Deutschland alleine.

[Sven Neuhaus]

Hallo Lucas,

Lucas Mengel schrieb:

> ich habe mir ein SQL-Statement gebastelt, um "Stadtname, lon, lat,
> postleitzahl" auszugeben.
> Das dient dem Zweck, dass ich mir eine eigene, einfacherer Architektur
> erstellen möchte, die nur diejenigen Daten enthält,
> die ich auch wirklich benötige.
>
> Bevor ich das Problem beschreibe, hier mein Ansatz:
>
> SELECT
>     plz.text_val AS stadt,
>     position.lon,
>     position.lat,
> FROM
>     geodb_textdata plz,
>     geodb_textdata ort,
>     geodb_coordinates position
> WHERE
>     position.loc_id = plz.loc_id AND
>     ort.loc_id = plz.loc_id AND
>     plz.text_type = 500300000 AND
>     ort.text_type = 500100000
> ORDER BY 2
>
>
> Nun zum Problemchen: Wie schaffe ich es, das Ergebnis auf Orte in
> Deutschland zu beschränken?

Das Statement mit hinzugefügter Beschränkung auf Deutschland sieht so aus:

SELECT
    plz.text_val AS stadt,
    position.lon,
    position.lat
FROM
    geodb_textdata plz,
    geodb_textdata ort,
    geodb_textdata land,
    geodb_coordinates position,
    geodb_locations lo,
    geodb_hierarchies hi
WHERE
    lo.loc_type = 100200000 /* State */
AND lo.loc_id = land.loc_id
AND land.text_type = 500100001 /* ISO_3166_1_ALPHA_2 */
AND land.text_val = 'DE'
AND hi.id_lvl2 = lo.loc_id
AND hi.loc_id = plz.loc_id
AND position.loc_id = plz.loc_id
AND ort.loc_id = plz.loc_id
AND plz.text_type = 500300000
AND ort.text_type = 500100000
ORDER BY 2


Aber vielleicht solltest Du lieber das folgende Statement verwenden, das
liefert genauere Koordinaten (dafür aber weniger Einträge):

    SELECT DISTINCT text_val, lon, lat
    FROM geodb_coordinates co, geodb_textdata tx,
        geodb_locations lo, geodb_hierarchies hi
    WHERE lo.loc_id = tx.loc_id
    AND lo.loc_id = co.loc_id
    AND lo.loc_id = hi.loc_id
    AND loc_type = 100800000 /* LOC_AREA_CODE */
    AND text_type = 500100000  /* NAME */

Derlei Einträge existieren aber derzeit nur für Deutschland, für Österreich
und die Schweiz musst Du auf die Einträge vom Typ 100700000 (POPULATED AREA)
zurückgreifen, die weniger genau sind und für diese den Text des Typs
500300000 (AREA_CODE) heraussuchen:

        SELECT DISTINCT tx.text_val AS plz, hi.id_lvl2 AS staat, lon, lat
        FROM geodb_textdata tx, geodb_locations lo,
            geodb_coordinates co, geodb_hierarchies hi
        WHERE text_type = 500300000 /* AREA_CODE */
        AND lo.loc_id = tx.loc_id
        AND lo.loc_id = hi.loc_id
        AND lo.loc_id = co.loc_id
        AND lo.loc_type = 100700000 /* POPULATED AREA */
        AND id_lvl2 IN (
            SELECT DISTINCT tx.loc_id
            FROM geodb_textdata tx, geodb_locations lo
            WHERE text_val in ('AT', 'CH')
            AND text_type = 500100001 /* ISO_3166_1_ALPHA_2 */
            AND tx.loc_id = lo.loc_id
            AND lo.loc_type = 100200000 /* State */

Der Subselect liefert übrigens immer "106,107" als loc_id für AT und CH, das
wird sich vermutlich nicht mal so eben ändern.

Gruss,
-Sven Neuhaus
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)

[Lucas Mengel]

Sven Neuhaus schrieb:

> Hallo Lucas,
>
> Lucas Mengel schrieb:
>  
>> ich habe mir ein SQL-Statement gebastelt, um "Stadtname, lon, lat,
>> postleitzahl" auszugeben.
>> Das dient dem Zweck, dass ich mir eine eigene, einfacherer Architektur
>> erstellen möchte, die nur diejenigen Daten enthält,
>> die ich auch wirklich benötige.
>>
>> Bevor ich das Problem beschreibe, hier mein Ansatz:
>>
>> SELECT
>>     plz.text_val AS stadt,
>>     position.lon,
>>     position.lat,
>> FROM
>>     geodb_textdata plz,
>>     geodb_textdata ort,
>>     geodb_coordinates position
>> WHERE
>>     position.loc_id = plz.loc_id AND
>>     ort.loc_id = plz.loc_id AND
>>     plz.text_type = 500300000 AND
>>     ort.text_type = 500100000
>> ORDER BY 2
>>
>>
>> Nun zum Problemchen: Wie schaffe ich es, das Ergebnis auf Orte in
>> Deutschland zu beschränken?
>>    
>
> Das Statement mit hinzugefügter Beschränkung auf Deutschland sieht so aus:
>
> SELECT
>     plz.text_val AS stadt,
>     position.lon,
>     position.lat
> FROM
>     geodb_textdata plz,
>     geodb_textdata ort,
>     geodb_textdata land,
>     geodb_coordinates position,
>     geodb_locations lo,
>     geodb_hierarchies hi
> WHERE
>     lo.loc_type = 100200000 /* State */
> AND lo.loc_id = land.loc_id
> AND land.text_type = 500100001 /* ISO_3166_1_ALPHA_2 */
> AND land.text_val = 'DE'
> AND hi.id_lvl2 = lo.loc_id
> AND hi.loc_id = plz.loc_id
> AND position.loc_id = plz.loc_id
> AND ort.loc_id = plz.loc_id
> AND plz.text_type = 500300000
> AND ort.text_type = 500100000
> ORDER BY 2
>
>
> Aber vielleicht solltest Du lieber das folgende Statement verwenden, das
> liefert genauere Koordinaten (dafür aber weniger Einträge):
>
>     SELECT DISTINCT text_val, lon, lat
>     FROM geodb_coordinates co, geodb_textdata tx,
>         geodb_locations lo, geodb_hierarchies hi
>     WHERE lo.loc_id = tx.loc_id
>     AND lo.loc_id = co.loc_id
>     AND lo.loc_id = hi.loc_id
>     AND loc_type = 100800000 /* LOC_AREA_CODE */
>     AND text_type = 500100000  /* NAME */
>
> Derlei Einträge existieren aber derzeit nur für Deutschland, für Österreich
> und die Schweiz musst Du auf die Einträge vom Typ 100700000 (POPULATED AREA)
> zurückgreifen, die weniger genau sind und für diese den Text des Typs
> 500300000 (AREA_CODE) heraussuchen:
>
>         SELECT DISTINCT tx.text_val AS plz, hi.id_lvl2 AS staat, lon, lat
>         FROM geodb_textdata tx, geodb_locations lo,
>             geodb_coordinates co, geodb_hierarchies hi
>         WHERE text_type = 500300000 /* AREA_CODE */
>         AND lo.loc_id = tx.loc_id
>         AND lo.loc_id = hi.loc_id
>         AND lo.loc_id = co.loc_id
>         AND lo.loc_type = 100700000 /* POPULATED AREA */
>         AND id_lvl2 IN (
>             SELECT DISTINCT tx.loc_id
>             FROM geodb_textdata tx, geodb_locations lo
>             WHERE text_val in ('AT', 'CH')
>             AND text_type = 500100001 /* ISO_3166_1_ALPHA_2 */
>             AND tx.loc_id = lo.loc_id
>             AND lo.loc_type = 100200000 /* State */
>
> Der Subselect liefert übrigens immer "106,107" als loc_id für AT und CH, das
> wird sich vermutlich nicht mal so eben ändern.
>
> Gruss,
> -Sven Neuhaus
>  

Hallo Sven!
Danke für die Mühe! Allerdings nutze ich die aktuellste Version der
geodb und die wird ohne geodb_hierarchies ausgeliefert. Das ist ja genau
der Knackpunkt.
Ich weiss nicht, wie ich die füllen muss.

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

[Sven Neuhaus]

Lucas Mengel schrieb:
> Danke für die Mühe! Allerdings nutze ich die aktuellste Version der
> geodb und die wird ohne geodb_hierarchies ausgeliefert. Das ist ja genau
> der Knackpunkt.
> Ich weiss nicht, wie ich die füllen muss.
>

Der Select benötigt die hierarchies-Tabelle gar nicht, das war nur noch ein
Relikt:

    SELECT DISTINCT text_val, lon, lat
    FROM geodb_coordinates co, geodb_textdata tx,
        geodb_locations lo
    WHERE lo.loc_id = tx.loc_id
    AND lo.loc_id = co.loc_id
    AND loc_type = 100800000 /* LOC_AREA_CODE */
    AND text_type = 500100000  /* NAME */

Das klappt aber halt nur eher zufällig, weil es derzeit keine solchen
Einträge für andere Länder als Deutschland gibt (ich weiss nicht ob das für
die aktuelle Version der OpenGeoDB noch zutrifft).

Gruss,
-Sven Neuhaus
--
Mailingliste OpenGeoDB
Listenadresse: opengeodb@...
Informationen: http://opengeodb.de
Mit freundlicher Unterstütztung von php::bar (http://phpbar.de)