1

I am quite new to GIS and OSM and am trying to get my head around exporting custom OSM region and uploading them into PostgreSQL.

I have started with a very small map area to export with only two shapes - an island and a meadow - (http://www.openstreetmap.org/#map=19/43.86436/15.34266) which export the following .osm data:

<?xml version="1.0" encoding="UTF-8"?>
<osm version="0.6" generator="CGImap 0.3.3 (23630 thorn-02.openstreetmap.org)" copyright="OpenStreetMap and contributors" attribution="http://www.openstreetmap.org/copyright" license="http://opendatacommons.org/licenses/odbl/1-0/">
    <bounds minlat="43.8635800" minlon="15.3411900" maxlat="43.8650000" maxlon="15.3445500"/>
    <node id="63910084" visible="true" version="3" changeset="17709161" timestamp="2013-09-06T20:07:53Z" user="idelac" uid="183676" lat="43.8644519" lon="15.3432012"/>
    <node id="63930603" visible="true" version="3" changeset="17709161" timestamp="2013-09-06T20:07:54Z" user="idelac" uid="183676" lat="43.8642369" lon="15.3431567"/>
    <node id="63968158" visible="true" version="3" changeset="17709161" timestamp="2013-09-06T20:07:57Z" user="idelac" uid="183676" lat="43.8645365" lon="15.3424617"/>
    <node id="1122490202" visible="true" version="2" changeset="17709161" timestamp="2013-09-06T20:07:44Z" user="idelac" uid="183676" lat="43.8640377" lon="15.3426171"/>
    <node id="63879158" visible="true" version="3" changeset="17709161" timestamp="2013-09-06T20:07:52Z" user="idelac" uid="183676" lat="43.8639894" lon="15.3430113"/>
    <node id="2448417703" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8641440" lon="15.3427266"/>
    <node id="2448417704" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8641542" lon="15.3426165"/>
    <node id="2448417706" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8642112" lon="15.3428451"/>
    <node id="2448417707" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8642458" lon="15.3424979"/>
    <node id="2448417708" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8643394" lon="15.3428931"/>
    <node id="2448417709" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8644045" lon="15.3425008"/>
    <node id="2448417711" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8645022" lon="15.3428874"/>
    <node id="2448417712" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8645144" lon="15.3425403"/>
    <node id="2448417713" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8645937" lon="15.3427745"/>
    <node id="2448417714" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:06:52Z" user="idelac" uid="183676" lat="43.8645978" lon="15.3426673"/>
    <node id="1122490153" visible="true" version="2" changeset="17709161" timestamp="2013-09-06T20:07:43Z" user="idelac" uid="183676" lat="43.8645802" lon="15.3431461"/>
    <node id="1122490171" visible="true" version="2" changeset="17709161" timestamp="2013-09-06T20:07:43Z" user="idelac" uid="183676" lat="43.8646997" lon="15.3426923"/>
    <node id="1122490322" visible="true" version="2" changeset="17709161" timestamp="2013-09-06T20:07:44Z" user="idelac" uid="183676" lat="43.8647101" lon="15.3429354"/>
    <node id="1122490460" visible="true" version="2" changeset="17709161" timestamp="2013-09-06T20:07:45Z" user="idelac" uid="183676" lat="43.8641347" lon="15.3424630"/>
    <node id="1122490524" visible="true" version="2" changeset="17709161" timestamp="2013-09-06T20:07:45Z" user="idelac" uid="183676" lat="43.8642836" lon="15.3424149"/>
    <way id="8837893" visible="true" version="3" changeset="7098380" timestamp="2011-01-26T21:37:50Z" user="goranT" uid="217969">
        <nd ref="63879158"/>
        <nd ref="63930603"/>
        <nd ref="63910084"/>
        <nd ref="1122490153"/>
        <nd ref="1122490322"/>
        <nd ref="1122490171"/>
        <nd ref="63968158"/>
        <nd ref="1122490524"/>
        <nd ref="1122490460"/>
        <nd ref="1122490202"/>
        <nd ref="63879158"/>
        <tag k="natural" v="coastline"/>
        <tag k="place" v="island"/>
        <tag k="source" v="PGS"/>
    </way>
    <way id="236881851" visible="true" version="1" changeset="17709161" timestamp="2013-09-06T20:07:26Z" user="idelac" uid="183676">
        <nd ref="2448417709"/>
        <nd ref="2448417707"/>
        <nd ref="2448417704"/>
        <nd ref="2448417703"/>
        <nd ref="2448417706"/>
        <nd ref="2448417708"/>
        <nd ref="2448417711"/>
        <nd ref="2448417713"/>
        <nd ref="2448417714"/>
        <nd ref="2448417712"/>
        <nd ref="2448417709"/>
        <tag k="landuse" v="meadow"/>
    </way>
</osm>

Running osm2pgsql -S default.style -d gis -U postgres island.osm uploads the data into my gis database with one row in the planet_osm_line table (for the island) and one row in the planet_osm_polygon table (for the meadow).

Now, when issuing sql query SELECT ST_AsText(way) FROM planet_osm_line, pqsql returns

"LINESTRING(1707992.39 5444456.51,1707997.34 5444489.7,1707915.02 5444502.76,1707932.32 5444425.75)"

Here I don't understand two things-

Questions

  1. Why does the Linestring only contain four points when the original coastline of the island has 10 distinct nodes? Similarly when issuing the sql query on the planet_osm_polygon table to see the polygon for the meadow shape, the polygon has fewer coordinates than its original shape described by the nodes (7 instead of 11).
  2. (answered just below in update) How are the original lat,lng coordinates projected? I know osm2pgsql defaults to mercator projection. However, I don't understand how it computed the values in the Linestring above. Is the width of a tile (256px?) and some zoom (z=?) taken into account in the projection? Would LatLng[0,0] be projected as a Point[0,0]?

Update

The answer to my second question is that osm2pgsql projects from latitude and longitude coordinates to mercator in meters. LatLng[0,0] will indeed be projected as Point[0,0] where the point coordinates are the distance in meters from the origin on a mercator plane.

Here is the projection function in case someone stumbles upon this question and looks for a solution

var degrees2meters = function(lon,lat) {
        var x = lon * 20037508.34 / 180;
        var y = Math.log(Math.tan((90 + lat) * Math.PI / 360)) / (Math.PI / 180);
        y = y * 20037508.34 / 180;
        return [x, y]
}

I still don't know why some of the nodes are missing in the resulting shapes in the database. Has anyone experienced this?

1

It seems you encountered a strange bug inside the osm importer. It expects the nodes to be ordered, but they are not.

Using QGIS, I get the same strange 4-node polygon using Add Vector layer from GDAL , but the Openstreetmap plugin does it right (i.e. it self-orders the nodes).

It looks like osm2pgsql expects the nodes ordered as well, but the export function of OSM does not deliver it. This should be related to the bug tracker issue: https://github.com/openstreetmap/osm2pgsql/issues/151 and How to complete the roads output of PostGIS?

Try the Overpass API as an alternative data source, or -s for slim mode.

And: osm2pgsql skipps natural=coastline, unless you use --keep-coastlines explicitely

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.