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.
How do I complete them?
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.
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.
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 ALTER COLUMN geom TYPE Geometry(Point, 4326) USING ST_Transform(geom, 4326);