gem-hunter

README

Table of Contents

File structure

Project/
│
├── .devcontainer/
│   └── devcontainer.json
|   └── servers.json
│
├── SQL/
├── SQL_EXTENDED/
├── default.style
├── docker-compose.yml
├── Dockerfile
├── postgresql_postgis.ipynb
├── README.md
├── requirements.txt
└── ...

Run Docker containers (PostgreSQL and pgadmin)

# Check, whether the two Docker containers (PostgreSQL and pgadmin) are running
docker ps

# If the containers are running, you can go to the section 'Import OSM data' below

# If the containers are not running, start them using: 
VS Code -> left Menu -> search file 'docker-compose.yml' -> right click -> Compose Up

# If the containers are running (check via 'docker ps'), open a terminal and additionally run:
bash .devcontainer/start.sh

Database credentials

Host: db
Port: 5432
Maintenance database: postgres
Username: pgadmin
Password: geheim

Import OSM data

# Show running Docker containers
docker ps

# Open bash and run osm2pgsql commands to fill up OpenStreetMap tables
docker exec -it postgis_container bash

# Run the following code in bash (change user name and password if required)
PGPASSWORD=geheim osm2pgsql -c -d osm_switzerland -U pgadmin -H db -P 5432 -S /usr/bin/default.style /tmp/zurich-latest.osm.pbf

# Exit bash
exit

# Show available tables in the database 'osm_switzerland'
docker exec -it postgis_container psql -U pgadmin -d osm_switzerland -c "\dt;"

# quit psql
q

Make SQL Queries

SELECT
    p.osm_id,
    p."addr:street",
    p."addr:housenumber",
    p."addr:city",
    p."addr:postcode",
    p.building,
    st_transform(p.way, 4326) AS geom
FROM
    public.planet_osm_polygon AS p
WHERE 
    p."addr:street" IS NOT NULL
    AND p."addr:city" = 'Zürich'
    AND p."addr:postcode" IN ('8001')

Known Issues and How to fix them

1.) Maps are not displayed in Jupyter Notebooks.
- This is a known bug related to the rendering of the Jupyter Notebook content.
- You should save, close, and reopen your Jupyter Notebook, then it should work.

2.) SQL queries are empty.
- Make sure that the OpenStreetMap data has been imported into the database (see README file).
- Check that your SQL query is correct, e.g., using pgadmin4.