I have a diff.osc file with a changeset #40293631 in a private OSM server. This is apparently one of the successful updates sent to a tile server. In the part of the file I have following, specifically related to a node id 4269514246:
<create>
<node id="4269514246" version="1" timestamp="2018-07-13T03:40:33Z" uid="4170915" user="ABC" changeset="40293631" lat="43.1734836" lon="77.0276441"/>
</create>
<modify>
<way id="238131937" version="6" timestamp="2018-07-13T03:40:35Z" uid="4170915" user="ABC" changeset="40293631">
<nd ref="4269514246"/>
</way>
</modify>
<create>
<way id="428143996" version="1" timestamp="2018-07-13T03:40:34Z" uid="4170915" user="ABC" changeset="40293631">
<nd ref="4269514246"/>
<tag .../>
</create>
Now I would like to locate those updates in PostgreSQL database. In it I searched following:
gis=# select * from planet_osm_nodes where id=4269514246;
id | lat | lon | tags
------------+-----------+-----------+------
4269514246 | 533841521 | 857467812 | (1 row)
I found that the coordinates need to be converted to decimal format, so I did:
gis=# select id,ST_X(ST_AsText(st_transform(st_geomfromtext('POINT ('||lon/100||' '||lat/100||')',900913),4326))) as lon, ST_Y(ST_AsText(st_transform(st_geomfromtext('POINT ('||lon/100||' '||lat/100||')',900913),4326))) as lat from planet_osm_nodes where id=4269514246;
id | lon | lat
------------+------------------+------------------
4269514246 | 77.0276430380341 | 43.1734822268193 (1 row)
But I realised that the coordinates in the database and diff.osc file are not the same. The diff file's and the database's lon,lat respectively are
[43.1734836, 77.0276441]
[43.1734822268193, 77.0276430380341]
Could there be conversion mistake or something else is causing it? Please, also advise on better troubleshooting methods, if they exist? Is there a way to get the last update on the tile server, if it does not save timestamps?
asked 03 Aug '18, 11:21

khassen
11●1●1●3
accept rate: 0%