Skip to content

Instantly share code, notes, and snippets.

@EpicKiwi

EpicKiwi/Importer-bdnb-postgis.md

Last active April 25, 2022 18:34
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save EpicKiwi/94d4c9be85300b9afcb6428c811b784d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save EpicKiwi/94d4c9be85300b9afcb6428c811b784d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. EpicKiwi revised this gist Apr 24, 2022. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Importer-bdnb-postgis.md
    View file
    Original file line number Diff line number Diff line change
    @@ -48,6 +48,7 @@ WHERE geombui && ST_Transform(ST_MakeEnvelope(7.329718, 43.771464, 7.107955, 43.

    ## Ressources

    * [psql](https://www.postgresql.org/docs/current/app-psql.html)
    * [ST_Centroid](https://postgis.net/docs/ST_Centroid.html)
    * [ST_AsGeoJSON](https://postgis.net/docs/ST_AsGeoJSON.html)
    * [ST_MakeEnvelope](https://postgis.net/docs/ST_MakeEnvelope.html)
  2. EpicKiwi renamed this gist Apr 24, 2022. 1 changed file with 0 additions and 0 deletions.
    0 README.md → Importer-bdnb-postgis.md
    View file
    File renamed without changes.
  3. EpicKiwi revised this gist Apr 24, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # Importer la bdnb dans Postgres en utilisant la ligne de commande
    # Importer la [bdnb](https://www.data.gouv.fr/fr/datasets/base-de-donnee-nationale-des-batiments-version-0-6/) dans Postgres en utilisant la ligne de commande

    1. Installer [PostgreSQL](https://www.postgresql.org/)
    2. Installer [PostGIS](https://postgis.net/) pour le support des fonction geographiques
  4. EpicKiwi revised this gist Apr 24, 2022. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -52,5 +52,5 @@ WHERE geombui && ST_Transform(ST_MakeEnvelope(7.329718, 43.771464, 7.107955, 43.
    * [ST_AsGeoJSON](https://postgis.net/docs/ST_AsGeoJSON.html)
    * [ST_MakeEnvelope](https://postgis.net/docs/ST_MakeEnvelope.html)
    * [ST_Transform](https://postgis.net/docs/ST_Transform.html)
    * [COPY](https://docs.postgresql.fr/11/sql-copy.html)
    * [&&](https://postgis.net/docs/overlaps_geometry_box2df.html)
    * [&&](https://postgis.net/docs/overlaps_geometry_box2df.html)
    * [COPY](https://docs.postgresql.fr/11/sql-copy.html)
  5. EpicKiwi revised this gist Apr 24, 2022. 1 changed file with 12 additions and 3 deletions.
    15 changes: 12 additions & 3 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    # Importer la bdnb dans Postgres en utilisant la ligne de commande

    1. Installer PostgreSQL
    2. Installer PostGIS pour le support des fonction geographiques
    1. Installer [PostgreSQL](https://www.postgresql.org/)
    2. Installer [PostGIS](https://postgis.net/) pour le support des fonction geographiques
    3. Télécharger les données en .sql

    ```sh
    @@ -44,4 +44,13 @@ COPY (SELECT
    cerffo2020_nb_log, adedpe202006_logtype_s_hab
    FROM batiment_opendata_v061
    WHERE geombui && ST_Transform(ST_MakeEnvelope(7.329718, 43.771464, 7.107955, 43.634429, 4326), 2154)) TO '/tmp/export.csv' WITH ( FORMAT csv );
    ```
    ```

    ## Ressources

    * [ST_Centroid](https://postgis.net/docs/ST_Centroid.html)
    * [ST_AsGeoJSON](https://postgis.net/docs/ST_AsGeoJSON.html)
    * [ST_MakeEnvelope](https://postgis.net/docs/ST_MakeEnvelope.html)
    * [ST_Transform](https://postgis.net/docs/ST_Transform.html)
    * [COPY](https://docs.postgresql.fr/11/sql-copy.html)
    * [&&](https://postgis.net/docs/overlaps_geometry_box2df.html)
  6. EpicKiwi created this gist Apr 24, 2022.
    47 changes: 47 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,47 @@
    # Importer la bdnb dans Postgres en utilisant la ligne de commande

    1. Installer PostgreSQL
    2. Installer PostGIS pour le support des fonction geographiques
    3. Télécharger les données en .sql

    ```sh
    curl --http1.0 -C - "https://bdnb-data.s3.fr-par.scw.cloud/bnb_export_metropole_sql_dump.zip" -o bnb_export_metropole_sql_dump.zip
    ```
    4. Extraire le zip
    5. Créer une base de données

    ```sh
    echo "CREATE DATABASE [nom de la bdd];" | psql -U postgres -W
    ```

    6. Importer les données dans postgres avec psql

    ```sh
    psql [nom de la bdd] -h localhost -U [nomUtilisateur] -W < dump_pg_v0.61.sql
    ```

    7. Créer la requete d'extraction des données

    Par exemple :

    ```sql
    SELECT
    bnb_id,
    ST_AsGeoJSON(ST_Transform(ST_Centroid(geombui), 4326)) as geombui_centroid,
    ST_AsGeoJSON(ST_Transform(geombui, 4326)) as geombui,
    cerffo2020_nb_log, adedpe202006_logtype_s_hab
    FROM batiment_opendata_v061
    WHERE geombui && ST_Transform(ST_MakeEnvelope(7.329718, 43.771464, 7.107955, 43.634429, 4326), 2154);
    ```

    8. Exporter les données en CSV avec COPY

    ```sql
    COPY (SELECT
    bnb_id,
    ST_AsGeoJSON(ST_Transform(ST_Centroid(geombui), 4326)) as geombui_centroid,
    ST_AsGeoJSON(ST_Transform(geombui, 4326)) as geombui,
    cerffo2020_nb_log, adedpe202006_logtype_s_hab
    FROM batiment_opendata_v061
    WHERE geombui && ST_Transform(ST_MakeEnvelope(7.329718, 43.771464, 7.107955, 43.634429, 4326), 2154)) TO '/tmp/export.csv' WITH ( FORMAT csv );
    ```