is_in:country bzw. Feststellen der Staatszugehörigkeit

jpuhr · 2012-08-04T12:32:03.000Z

Hallo,

ich beschäftige mich gerade mit einem kleinen Auftrag für den öffentlichen Dienst: eine Software, bei der auch eine Kartendarstellung eingebunden werden soll. Auch, wenn es gar nicht Teil des Auftrags ist, möchte ich Kacheldaten von Openstreetmap mit ausliefern (bislang wird weithin das Material von ESRI verwendet). Letztlich mit dem Gedanken, die Verwendung von Opensource im öffentl. Dienst “schmackhaft” zu machen.

Folgendes Problem: Zwei Drittel der Welt ist in kyrillischen, arabischen oder chinesischen Schriftzeichen dargestellt. Das ist natürlich zur Auslieferung inakzeptabel. Deswegen habe ich eine regionalisierte Karte erstellt, wie auf der OSM-Seite beschrieben über einen angepassten Datenbank-View, der den Tag “name” auf “name:de” abbildet. Daraus ergibt sich das nächste Problem: Danach ist zwar Asien auf Deutsch, aber der gößte Teil von Europa sieht aus wie eine historische Karte von 1914 (Teile Südfrankreichs, des Baltikums oder des Balkans gar wie eine mittelalterl. Karte). Das ist natürlich noch inakzeptabler, weil politisch inkorrekt.

Dann habe ich den Datenbank-View entsprechend erweitert: Wenn “irgendetwas” darauf hindeutet, dass es sich um einen Städtenamen in Europa handelt, dann nimm “name”, ansonsten “name:de” oder “name:en”. Das jedoch herauszufinden, ist ein ganz schönes Gefrickel: Mal steht der Landesname in Landessprache, mal in Englich in “is_in”, mal in “is_in:country”. Oft gibt es “is_in:*” gar nicht. Dann lässt sich aber danach suchen, ob die verwiesene Webseite z.B. “.lv” enthält (WHEN “website” LIKE ‘%.lv%’), oder ob im Tag “source” auf die slowenische Statistikbehörde verwiesen wird (WHEN “source” LIKE ‘%.sv%’) usw.usw.

Wenn man all diese Hinweise in der SQL-Abfrage ver-oder-t, dann bekommt man schon ein halbwegs brauchbares Ergebnis. Es gibt aber trotzdem noch etliche Orte, die durchfallen und auf der Karte deutsch erscheinen, weil einfach “nichts” darauf hindeutet, wo sich der Ort befindet.

Ist es denn nicht möglich, die Entscheidung, in welchem Land sich der Ort befindet (und dann auf Basis einer Entscheidungstabelle zur Sprachauswahl führt), auf Basis von Ländergrenzen zu automatisieren? Wahrscheinlich geht das aber nicht über den einfachen Weg “SQL-View”.

Viele Grüße

J. Puhr

Dennis_B · 2012-08-04T12:41:30.000Z

Europa kannste eigentlich relativ einfach mit einer Boundingbox abdecken.

wambacher · 2012-08-04T12:48:00.000Z

jpuhr:

Letztlich mit dem Gedanken, die Verwendung von Opensource im öffentl. Dienst “schmackhaft” zu machen.

Lob, Lob

… Deswegen habe ich eine regionalisierte Karte erstellt, wie auf der OSM-Seite beschrieben über einen angepassten Datenbank-View, der den Tag “name” auf “name:de” abbildet.

Welches Kochrezept hast du denn genau verwendet - ich verstehe zur Zeit nur Bahnhof. Einige zusätzliche Infos mit Screenshots wären schon hilfreich.
Datenbank ist klar - aber der Rest?

Gruss
walter

p.s. falls du selber rendern solltest, ist der Tip von SunCobalt weiter unten schon richtig.

SunCobalt · 2012-08-04T12:50:21.000Z

schau mal hier, links unten. Man kann anhand der Grenzen, die Du in Deiner DB hast, feststelle, wo man sich befindet
http://suncobalt.dyndns.org:82/polska.php

verschiebe die Karte mal in andere Länder.
realisiert mit
SELECT admin_level,name FROM osm_polygon WHERE way && expand(ST_GeometryFromText(‘POINT($x $y)’),40000) and (admin_level=‘2’ or
admin_level=‘4’ or admin_level=‘6’) AND ST_Contains(way,ST_GeometryFromText(‘POINT($x $y)’,4326)) AND name NOT LIKE ‘%Länderdreieck%’ AND name NOT LIKE ‘%Landmasse%’ ORDER BY CAST(admin_level as INT);"
(powered by Maxbe )

Du suchst admin_level=2 → Staatsgrenze

jpuhr · 2012-08-04T14:39:06.000Z

'Dennis[B:

']Europa kannste eigentlich relativ einfach mit einer Boundingbox abdecken.

Leider nicht so einfach. Griechenland, Ukraine, Weissrussland, Königsberger Gebiet und Kern-Russland müsste man ausklammern. Und das geht schlecht über eine Boundingbox.

fx99 · 2012-08-04T15:24:55.000Z

Kannst Du nicht einfach über die Daten in 'k=‘name’ v= ’ feststellen, um welche Sprache es sich handelt,
bzw. feststellst, dass da nicht lateinische Zeichen drin sind?

Klar mit Umlauten, oder diversen Sonderzeichen z.B. im Skandinavischen ist das ein ziemlich Programmiererei.

jpuhr · 2012-08-04T17:28:52.000Z

fx99:

Kannst Du nicht einfach über die Daten … feststellen, um welche Sprache es sich handelt…

Hab ich mir auch schon überlegt. Bei “lateinischen” Buchstaben im Tag “name” nehme ich diesen, ansonsten den Tag “name:de” oder “name:en”. Aber wie kann ich das in SQL feststellen?

fx99 · 2012-08-04T17:35:19.000Z

Wie man in sql Bit schubst, weiss ich nicht.
Aber die Abfrage auf nicht ASCII Zeichen ist ziemlich einfach: wenn das MSB in einem Byte gestzt ist, so ist es kein ASCII Zeichen, siehe http://de.wikipedia.org/wiki/UTF-8#Kodierung

wambacher · 2012-08-04T17:46:43.000Z

wollt ihr wirklich aufgrund der Schreibweise eines Namens rauskriegen, in welchem Land sich das Objekt befindet?

ich glaub, ich steh hier im Wald - da haben wir eine tolle Geo-DB mit Koordinaten und Grenzen und eine sql-Sprache, die GIS versteht, und dann sowas. Nee.

Gruss
walter

SunCobalt · 2012-08-04T17:59:02.000Z

wambacher:

wollt ihr wirklich aufgrund der Schreibweise eines Namens rauskriegen, in welchem Land sich das Objekt befindet?

ich glaub, ich steh hier im Wald - da haben wir eine tolle Geo-DB mit Koordinaten und Grenzen und eine sql-Sprache, die GIS versteht, und dann sowas. Nee.

Gruss
walter

verstehe ich auch nicht. Ich hab ihm doch oben sogar den SQL Befehl hingeschrieben, wie er das Land rauskriegt, in dem ein Punkt ist

wambacher · 2012-08-04T18:39:41.000Z

SunCobalt:

verstehe ich auch nicht. Ich hab ihm doch oben sogar den SQL Befehl hingeschrieben, wie er das Land rauskriegt, in dem ein Punkt ist

Ich glaube, er ist irgendwie gaaaaaanz tief in einer Sackgasse und weiss das blos noch nicht (oder will es nicht wissen?). Einerseits hab ich den Eindruck, dass er die ganze Welt rendern kann - warum sonst internationale Karten ? - und andererseits nicht weiss, was eine spatiale Abfrage ist.

Und solange er meine Frage nicht beantwortet hat (“Welches Kochrezept hast du denn genau verwendet - ich verstehe zur Zeit nur Bahnhof. Einige zusätzliche Infos mit Screenshots wären schon hilfreich.”) ist bei mir eh Sense.

Na ja, warten mer ab.

Gruss
walter

fx99 · 2012-08-05T05:19:31.000Z

wambacher:

wollt ihr wirklich aufgrund der Schreibweise eines Namens rauskriegen, in welchem Land sich das Objekt befindet?

ich glaub, ich steh hier im Wald - da haben wir eine tolle Geo-DB mit Koordinaten und Grenzen und eine sql-Sprache, die GIS versteht, und dann sowas. Nee.

Gruss
walter

Es geht ja nicht nicht darum, herauszufinden, in wlechem Land eine Stadt liegt, sondern was eine “sinnvolle” Schreibweise ist.
Der Weg über das Land ist da für mich eher die Krücke, wenn man nichts Besseres hat.

Es muss für jedes Land festgelegt sein, welche Schreibweise verwendet wird.
Schon das Beispiel Europa erweist sich bei der geographischen Betrachtung als nicht trivial.
Neben den og. genannten Ausnahmen ist in Serbien und Bulgarien die offizielle Schrift kyrillisch.
In Bosnien wird zumindest in OSM sowohl lat. als auch kyr. verwendet, http://m.osmtools.de/index.php?lon=18.183883666608&lat=44.732055680197&zoom=11&mlon=18.093933105088&mlat=44.731323980112&icon=4. Aber selbst OSM benutzt je nach AUflösung das eine oder andere Alphabet. http://m.osmtools.de/index.php?lon=18.106979369732&lat=44.734860444765&zoom=12&mlon=18.093933105088&mlat=44.731323980112&icon=4

Der Test auf nicht-lateinische Schriftzeichen im Namen ist dagegen universell einsetzbar und meiner Meinung nach vom Rechenaufwand drastisch einfacher.

SunCobalt · 2012-08-05T06:17:19.000Z

fx99:

Es muss für jedes Land festgelegt sein, welche Schreibweise verwendet wird.

Nein, es muss für ein paar Länder der name:de ausgeschlossen werden. Es sei denn captial=yes, da sollte es ein Exonym sein. Die Prüfung in welchen Land ein Punkt ist, müsste nur in einer großzügigen Europa BBox gemacht werden

fx99:

Schon das Beispiel Europa erweist sich bei der geographischen Betrachtung als nicht trivial.
Neben den og. genannten Ausnahmen ist in Serbien und Bulgarien die offizielle Schrift kyrillisch.

Yep, und genau dort sollte man name:de vermeiden.

fx99:

Der Test auf nicht-lateinische Schriftzeichen im Namen ist dagegen universell einsetzbar und meiner Meinung nach vom Rechenaufwand drastisch einfacher.

Kommst nur nicht weit, denn es löst nicht das Problem wo ein lesbarer Name herkommt. Je nach Land steckt in ein romanisierter/lateinischer Name in einem anderen Tag. Oft, wie in Griechenland ist es name:en, es kann aber auch bspw Korea name:ko_rm, name:ja_rm, Serbien name:sr-Latn sein. Die Info, das Бела Црква kyrillisch ist, sagt Dir noch nicht, dass Du name:de=Weißkirchen vermeiden solltest (politisch inkorrekt) und statt dessen name:sr-Latn=Bela Crkva verwenden solltest.

viw · 2012-08-05T07:00:37.000Z

So wir sollten hier nicht streiten, sondern mal nach vorne schauen. Immerhin geht es darum die OSM Daten für bestimmte Zwecke der öffentlichen Verwaltung, von welcher wir uns auch Daten erhoffen, nutzbar zu machen.
Ziel ist es, soweit ich das hier rauslese, eine Weltkarte zu erstellen wo alle Bezeichnungen lateinische Buchstaben besitzen. Der Weg dahin ist völlig frei.

Da aber nichts unversucht bleiben soll, wurden mehrere Dinge schon probiert:

name:de
name:en

Dies führte aber gerade in Europa dazu, das eher wenige Bezeichnungen zu sehen waren, da ja bereits unter name gepflegt.

Der zweite Ansatz war jetzt Länder mit offensichtlich anderer Schreibweise wie Ukrainie, Russland, Griecheland und weitere explizit anders zu behandeln (name:de oder name:en) und den Rest von Europa einfach mit name auszuwerten.

Der Umkehrschluss ist auch nicht ganz richtig, denn in Amerika werden auch in vielen Ländern lateinische Buchstaben verwendet. Die Frage ist also ob man ein universelles Konzept alleine über eine Liste der Länder aufbauen kann.

Dritter Ansatz war prüfen ob der name tag einen nicht lateinsichen Schriftsatz verwendet. Dann alternativen Namen (name:de name:en) suchen, sonst name ausgeben.

Nach den Aussagen von Thomas ist dieser Ansatz eigentlich der einzig mögliche, da man nur so viele weitere Möglichkeiten auf Objektebene hat (name:ja_rm etc.)

Mein Vorschlag wäre das Pferd von hinten aufzuzäumen. Als erstes prüfen ob das Objekt name:de führt falls nicht prüfen ob name lateinischen Buchstaben hat und erst bei negativem Ergebniss nach alternativen name:* tags suchen und ebenfalls auf lateinische Buchstaben testen.
Zu klären bleibt bei diesem Vorgehen die Frage wie kann man den Zeichensatz herausfinden.

jpuhr · 2012-08-05T10:23:07.000Z

SunCobalt:

verstehe ich auch nicht. Ich hab ihm doch oben sogar den SQL Befehl hingeschrieben, wie er das Land rauskriegt, in dem ein Punkt ist

Danke auf jeden Fall dafür. Das Problem ist, ist bin kein SQL-Guru, bei dem Projekt geht es auch um etwas ganz anderes, und die Openstreetmap-Kartendarstellung ist, wie gesagt, nur mein selbst gesetztes i-Tüpfelchen.

Ich habe mich zum Rendern der Karte mehr oder weniger strikt an folgende Anleitung gehalten:
http://wiki.openstreetmap.org/wiki/Regionalisedmap

Speiziell für den View auf planet_osm_point habe ich folgende Basis genommen:
CREATE VIEW planet_deosm_point AS
SELECT osm_id ,
access ,
[…]
CASE WHEN “name:de” IS NOT NULL THEN “name:de” ELSE name END AS name,
[…]
way
FROM planet_osm_point;

Mittlerweilw wird aber nicht mehr nur “name:de” auf NULL geprüft, sondern eine über mehrere Bildschirmseiten gehende CASE-Abfrage, die versucht, anhand der anderen Tags (“is_in”, “is_in:country”, “source”, etc.) zu erraten, wo der Point sein könnte, und in Abhängigkeit davon auswählt, welcher der Tags “name:*” als solcher zurückgegeben wird.

Wie spanne ich jetzt den Bogen aus diesem View (planet_osm_point) auf die Tabelle osm_polygon der angegebenen SQL-Abfrage?

Vielen Dank auf jeden Fall
jpuhr

jpuhr · 2012-08-05T10:33:49.000Z

viw:

Ziel ist es, soweit ich das hier rauslese, eine Weltkarte zu erstellen wo alle Bezeichnungen lateinische Buchstaben besitzen. Der Weg dahin ist völlig frei.

Mein Vorschlag wäre das Pferd von hinten aufzuzäumen. Als erstes prüfen ob das Objekt name:de führt falls nicht prüfen ob name lateinischen Buchstaben hat und erst bei negativem Ergebniss nach alternativen name:* tags suchen und ebenfalls auf lateinische Buchstaben testen.
Zu klären bleibt bei diesem Vorgehen die Frage wie kann man den Zeichensatz herausfinden.

Exakt. Um es nochmal zusammenzufassen: Gesucht ist eine Weltkarte, die

ausschließlich Namen mit lateinischen Buchstaben enthält
keine historischen Städtenamen von vor 1914 (z.B. Preußisch Eylau, Böhmisch Budweis, Bisanz, Sankt Veit am Flaum,…); hier ist in jedem Fall der aktuelle landessprachliche Städtename zu verwenden (Ausnahme evtl. Hauptstädte).

Eine Prüfung, ob ein UTF8-Namensstring lateinische oder nicht-lateinische Zeichen enthält, ist unter C wohl sehr schnell programmiert, und AFAIK kann man unter SQL auch C-Funktionen “irgendwie” aufrufen (bin kein SQLer). Wenn ich noch Zeit und Nerven habe, mache ich das vielleicht.

viw · 2012-08-05T11:42:49.000Z

jpuhr:

Mittlerweilw wird aber nicht mehr nur “name:de” auf NULL geprüft, sondern eine über mehrere Bildschirmseiten gehende CASE-Abfrage, die versucht, anhand der anderen Tags (“is_in”, “is_in:country”, “source”, etc.) zu erraten, wo der Point sein könnte, und in Abhängigkeit davon auswählt, welcher der Tags “name:*” als solcher zurückgegeben wird.

Das Problem warum dir die alten Hasen nicht so richtig helfen wollen, ist wahrscheinlich ein Mißverständnis. Dieses is_in ist nur noch ein Überbleisel aus einer Zeit wo es noch kein Postgre/Postgis in der OSM Welt gab. Inzwischen kann man das mit Postgisfunktionen sehr viel einfacher herauslesen, in welchem Land Kontinet oder welcher geografischen Grenze auch immer befindet. Daher wird dieser Tag einfach nicht mehr gepflegt und erst recht nur noch selten neu eingefügt.
Wichtig um diese Sache gut nutzen zu können wäre also ein Polygon, das alles einschließt was zur einen oder anderen Kategorie gehört. Danach kannst du mit einer einfachen SQL Abfrage herausfinden, ob dein Punkt nun in oder außerhalb des Gebietes liegt.
Eine solche Abfrage kann bei komplizierten Polygonen länger dauern, daher wurde vorgeschlagen zunächst eine Boundingbox(Rechteck) zu prüfen und nur wenn der Punkt darin liegt genauer nachzuschauen. Vielleicht wäre das ja ein gangbarer Weg. Bis OSM mal weltweit alle nametags “übersetzt” bekommen hat wird wohl noch einiges an Zeit vergehen. Wenn dies außerhalb der Touristenregionen überhaupt passieren wird.

wambacher · 2012-08-05T12:00:15.000Z

jpuhr:

Wie spanne ich jetzt den Bogen aus diesem View (planet_osm_point) auf die Tabelle osm_polygon der angegebenen SQL-Abfrage?

Hi,

indem du dir das mal reinziehst. Das kann deine DB nämlich auch - ohne dass du noch was machen musst.
Ist aber eventuell für das Gooddy, was du noch einbauen möchstest, etwas viel Aufwand.

Kurzfassung: Alle deine Lösungsansätze verwenden konkrete OSM-Objekte (Nodes, is-in und ähnliches) und berücksichtigen nicht deren Lage im Raum. Mit PostGis kannst du jederzeit feststellen wie die örtlichen Bezüge zwischen verschiedenen Daten sind. (Z.B. “in welchem Land befindet sich diese Stadt?”).

Dazu gibt es Abfragen wie diese:


osm=# select id, coalesce(tags->'name',tags->'note') "liegt in",tags->'admin_level' "adminlevel" from relations
where st_contains(geom,(select geom from nodes where id=240028377))         
order by tags->'admin_level' desc;
   id    |       liegt in             | adminlevel 
---------+----------------------------+------------
 1189602 | 65189 Wiesbaden            | 
   62496 | Wiesbaden                  | 6
  286590 | Regierungsbezirk Darmstadt | 5
   62650 | Hessen                     | 4
   51477 | Deutschland                | 2
 1111111 | Deutschland                | 2
(6 rows)

Hier wurde der gesucht, in welchen Grenzen sich der Node 240028377 (place-node von Wiesbaden) befindet.
Der Knackpunkt ist hier “where **st_contains(**geom,(select geom from nodes where id=240028377))”

bei der mit osm2pgsql erstellten DB sieht es ähnlich aus, nur hab ich derzeit kein Beispiel parat.

“st_contains(planet_osm_polygon.way, planet_osm_point.way)” ist sicher nicht ganz korrekt, gibt dir aber einen kleinen Eindruck.

Gruss
walter

jpuhr · 2012-08-06T07:26:32.000Z

Hallo,

dann möchte ich mich abschließend jetzt für jeden der hilfreichen Kommentare bedanken. Ich denke, mir ist klar geworden, wie die Vorgehensweise sein könnte.

Viele Grüße

J. Puhr