I have downloaded some OSM data from geofabrik and used this to create a PostgreSQL/PostGIS database using the osm2pgsql tool:
osm2pgsql -G -d <db> -U postgres myfile.osm
Examining the database in pgAdmin shows everything as I would expect: there are tables for planet_osm_line etc. which all have data including geometry column. However, when I connect to the database from QGIS and attempt to add layers, none of these tables appear in the Data Source Manager|PostGIS dialog.
Searching on this site (and comparing with a working PostGIS database that I created using QGIS and some random shape files) led me to suspect that I might need a primary key field in my tables. But adding one with:
ALTER TABLE planet_osm_line ADD COLUMN id SERIAL PRIMARY KEY;
has not helped. I have also tried running osm2pgsql without the -G flag, changing the name of the geometry column to 'geom' to match what QGIS produced in my working database and changing projection in QGIS to Spherical Mercator (3857) to match the data in the PostGIS database. And I've made sure to name the connection as per the solution here: Why can't I open postgis layers in QGIS?
As I'm quite new to all this I suspect I've overlooked something in my usage of either osm2pgsql or QGIS. Could someone point out the error of my ways?
In case it's useful, I'm using:
- osm2pgsql version 0.96.0
- QGIS 3.4.3-Madeira
- PostgreSQL 11.1
- postgis_full_version(): POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.3.2, released 2018/09/21" LIBXML="2.9.8" LIBJSON="0.13.1" LIBPROTOBUF="1.3.1" RASTER
