Transformation

Hallo liebe Community!

Ich habe ein Problem und ich denke, dass ich hier richtig bin unter so vielen kompetenten PostGIS-Liebhabern. Ich habe folgenden Sachverhalt:

Ich möchte die Strassenbahn und Buslinien der Braunschweiger Innenstadt in eine Datenbank einlesen. Dazu habe ich mir bereits eine PostGIS-1.5.1-Datenbank mit PostgreSQL-Server 8.4 installiert und die Niedersachsen.osm aus der Geofabrik soweit importiert, dass eine BoundingBox über Braunschweig liegt. Dies habe ich mit der neuesten Version 0.69 von osm2pgsql getan, die ich aus dessen SVN-Repository habe.

Dabei ergibt sich nun folgendes Problem: Ich bekomme die Koordinaten für meine Haltestellen im EPSG 900913 System. Ich brauche sie aber im GoogleMaps tauglichen Format EPSG4236. Ich weiss jetzt nicht genau wie ich eine Abfrage gestalten muss, die mir meine Haltestellen exportiert und die Koordinaten direkt umwandelt.

Ich bin noch relativ neu in der Materie und kenne mich leider kaum mit PostgreSQL-Abfragen aus. Bisher habe ich mit folgender Abfrage gearbeitet:

SELECT id, lat, lon as lng, tags[4],'busstop' as type
FROM planet_osm_nodes
WHERE (tags[1] = 'railway' AND tags[2] = 'tram_stop' AND tags[3]='name') 
OR (tags[1] = 'highway' AND tags[2] = 'bus_stop' AND tags[3] ='name')
OR (tags[3] = 'highway' AND tags[4] = 'platform');

Hat jemand einen Tipp für mich??

Viele Grüße

Tobi

Hallo!

AFAIK verwenden Googlemaps und OSM die Projektion 900913, kann mich aber auch irren. 4326 stellt das WGS84-Sytem mit geographischen Koordinaten dar.
Anhand deines SQL stelle ich fest, das du noch gar keine Geometrie hast oder wird die nur nicht abgefragt?
Erstellen einer Geometrie und transformieren würdest du das Ganze in etwa so:

SELECT id, lat, lon as lng, st_transform(geomfromtext('POINT(||'lat||' '||lon||`)', 900913), 4326)as the_geom, tags[4],'busstop' as type
FROM planet_osm_nodes
WHERE (tags[1] = 'railway' AND tags[2] = 'tram_stop' AND tags[3]='name') 
OR (tags[1] = 'highway' AND tags[2] = 'bus_stop' AND tags[3] ='name')
OR (tags[3] = 'highway' AND tags[4] = 'platform');

ohne Kaffee und Gewähr :wink:

Falls du schon ein Geometriefeld hast, entfällt geomfromtext(…) und wird durch die Geometrie ersetzt.

Viele Grüsse
Uwe

Danke für die Antwort :slight_smile:
Ich komme leider erst wieder nächste Woche dazu das Ganze zu testen. Ich werde Dir dann Rückmeldung geben ob es geklappt hat bzw. falls noch weitere Fragen aufkommen.
Hatte noch kein Geometriefeld. Muss ich das vorher noch irgendwie anders erstellen oder reicht das wie in deinem SQL-Query?

Das sql erzeugt dir eine Geometrie. Man kann das ggf. in einen View verpacken, was aber unter Umständen Nachteile haben kann, z.B. bei der Darstellung in Desktop-GIS oder WMS bzw. fehlende Geometrie-Indizierung. Es hängt also etwas davon ab, was du damit tun willst. Da sich die Datenmenge in Grenzen hält, würde ich dir die Erstellung eines Geometriefelds empfehlen. Das erstellst du am besten mit ‘select addgeometrycolumn(…)’. Lies aber vorher noch mal im PostGIS-Handbuch nach, da hat sich zu Version 1.5 was geändert AFAIR. Befüllt wird das dann durch:

Update <meine.Tabelle> set
<meine_Geometriespalte> =  st_transform(geomfromtext('POINT(||'lat||' '||lon||`)', 900913), 4326)

Grüße
Uwe

Hey,

habe nun mal folgende Abfrage gestartet:


INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 94326, 'epsg', 4326, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs ', 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]');
SELECT AddGeometryColumn( 'planet_osm_nodes', 'my_geom', -1, 'GEOMETRY', 3 );
Update planet_osm_nodes set
my_geom =  st_transform(geomfromtext('POINT('||lat||' '||lon||')', 900913), 4326)

SELECT id, lat, lon as lng, st_transform(geomfromtext('POINT('||lat||' '||lon||')', 900913), 4326)as my_geom, tags[4],'busstop' as type
FROM planet_osm_nodes
WHERE (tags[1] = 'railway' AND tags[2] = 'tram_stop' AND tags[3]='name') 
OR (tags[1] = 'highway' AND tags[2] = 'bus_stop' AND tags[3] ='name')
OR (tags[3] = 'highway' AND tags[4] = 'platform');

Leider habe ich nun Werte im Feld my_geom, die folgende Form haben:

“0101000020E6100000BE5FB710A8C44E4091882286EFF82440”

Ich hätte ganz gerne die Koordinaten in der Form “52.2314, 10.1341” (wie sie GoogleMaps und OSM nutzen).

In meinen Feldern lat, lon sind nach wie vor Koordinaten der Form “6850052.03891606, 1171728.10994937”.

Wie bekomme ich das am besten hin?


Bei deinem letzten Code-Snippet bekomme ich folgende Fehlermeldung:

FEHLER: neue Zeile für Relation »planet_osm_nodes« verletzt Check-Constraint »enforce_dims_my_geom«
SQL Status:23514

Ich habe das nach Erstellen der Spalte getan, so wie oben geschildert…

Viele Grüße

Tobi

Hier noch meine Daten, welche ich in die PostGIS-DB importiert habe.

DOWNLOAD

Viel Spaß beim Spielen :wink:

push
Habe leider immer noch keine Lösung. Bin für jede Hilfe dankbar :slight_smile:

Das ist ja auch das Format für Geometrien in Postgis, Well-Known Binary (WKB). Wozu brauchst du denn die direkt lesbaren Koordinaten? Die kannst du aber auch direkt ermitteln mit ST_AsText(geom).

Übrigens benutzen sowohl Google Maps als auch OpenStreetMap die Google Mercator Projektion (900913), zeigen aber die Koordinaten als WGS84(4326) an …

Ich würde das so machen:
SELECT AddGeometryColumn( ‘planet_osm_nodes’, ‘my_geom’, 4326, ‘POINT’, 2 );

Du versuchst 2D-Geometrien in ein 3D-Feld zu schreiben. AFAIR geht das zwar, erzeugt aber Fehlermeldungen.
Anstatt -1 für keine SRID würde ich die bekannte und gewünschte reinschreiben, Und als Geometrie sollte da ‘POINT’ stehen.

Gruß Uwe