I converted the file I exported from OSM to PostGIS using osm2pgsql. I have opened the table's outputs to QGIS and I saw data are not complete compare to OSM editor view. Most of the roads are broken and out of align.

Screenshot of broken and unaligned line

How do I complete them?


What is occuring appears to be a somewhat subtle bug with osm2pgsql where osm2pgsql requires id-sorted nodes, but the API does not guarantee that.

The easiest solution is to import with --slim --drop. The --slim flag causes osm2pgsql to use the database instead of memory for temporary storage, which gives an option for coping with unordered files. The --drop flag causes the temporary information to be dropped from the database when osm2pgsql is finished.

Another option would be to use osmosis and --sort the XML file, but I don't suggest this as it's more complicated and requires installing osmosis.

  • Strangely, it worked for me with osm2pgsql --create --database bboxdb --username osmuser --prefix planet -s --cache 3000 -S D:\Karten\OpenStreetMap\osm2pgsql\extended.style --hstore D:\Karten\osm\osm2pgsql\map.osm without loosing nodes. Maybe I just did not catch the same bbox as the asker.
    – AndreJ
    Jun 29 '14 at 7:32
  • -s is short for --slim. It is also worth noting that although the API provides no guarantee that the output is id-sorted, depending on details of the configuration, it may be. Jun 29 '14 at 7:37

The planet_osm_roads table in POSTGIS is only designed for rendering at lower zoom levels.

For higher zoom levels, take the planet_osm_line table and filter for features with highway.

If you still don't see all roads: How did you get the data from OSM? The export function might skip ways that are not completely inside the view. You better use the Overpass API or Geofabrik regional extracts instead.

For the alignment issue: In QGIS, Set CRS for Layer for the postgis layer to EPSG:3857, since EPSG:900913 is not in the CRS database of QGIS.

  • I exported the data from OSM and use osm2pgsql to convert .osm to PostGIS database Jun 28 '14 at 11:25
  • Can you add a screenshot to your question? Save As Image should work.
    – AndreJ
    Jun 28 '14 at 12:14
  • I have added the screenshot. Jun 28 '14 at 12:38
  • 1
    Are you using a recent version of osm2pgsql? Older versions do not support current node numbers. Alternatively, you can use the QGIS Openstreetmap plugin, step 2 and 3 (You have already downloaded the raw data).
    – AndreJ
    Jun 28 '14 at 17:34
  • 1
    Okay the problem solved by using the data from Geofabrik but how ofter they update their OSM raw file in the Philippines? If we edit the data from OSM, how many days it will take before we get their version? Thank you for your answer. :) Jun 28 '14 at 21:49

osm2pgsql imports OSM data in Mercator EPSG:900913. Ensure your Geometry Column in PostGis is set to EPSG:900913 using UpdateGeometrySRID

You may need to install the EPSG:900913 Proj in your PostGis database using the instructions here!

Ensure your QGis Layer Projection matches as well.

If you want to Reproject to EPSG:4326 (WGS84) Then you can do the following in PostGres

 ALTER TABLE mytable 
   TYPE Geometry(Point, 4326) 
   USING ST_Transform(geom, 4326);
  • Is it safe to alter even my final result will be posted in web using OpenLayer? Jun 28 '14 at 1:59
  • I'm sorry its a confusion for me. What is the purpose of updating geometry SRID to EPSG:900913 if I will reproject it again EPSG:4326 as my choice for web mapping display? It will display the same result? Right? Jun 28 '14 at 2:21
  • You do not update the Geometry to 900913, it probably already is if you used osm2pgsql. What projection is the geometery is in the database? If you did not change it, then it is probably 900913. You can use reprojection, but we choose not to as OSM and our data is all in 900913. IS your QGIS doing the proper reprojection to WGS84? If the data is stored in 900913, then QGIS has to have that set on the layer to display the layer properly. You can turn on automatic on the fly reprojection in QGis in the Settings Jun 28 '14 at 2:28
  • If you do not have thh 900913 SRID in your PostGIS database, you will have to install it as per the link I gave you Jun 28 '14 at 2:34
  • I'm working with lines and roads I have switched my projection for vector layer to 900913. It added some lines but the alignment and roads are not presented as perfect as OSM is. Jun 28 '14 at 4:08

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.