Loading geospatial data

Now let`s load some geospatial data into our database so that we can familiarise ourselves with the tools and processes used to retrieve that data.

Natural Earth provides a great source of basic data for the whole world on various scales. And the best thing is that this data is in the public domain:

  1. Download the data

    $ mkdir nedata
    $ cd !$
    cd nedata
    $ wget https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/110m/cultural/ne_110m_admin_0_countries.zip
    
  2. Unzip the file

    $ sudo apt install unzip
    $ unzip ne_110m_admin_0_countries.zip
    Archive:  ne_110m_admin_0_countries.zip
      inflating: ne_110m_admin_0_countries.README.html
     extracting: ne_110m_admin_0_countries.VERSION.txt
     extracting: ne_110m_admin_0_countries.cpg
      inflating: ne_110m_admin_0_countries.dbf
      inflating: ne_110m_admin_0_countries.prj
      inflating: ne_110m_admin_0_countries.shp
      inflating: ne_110m_admin_0_countries.shx
    
  3. Load into our postgis_db database

    The files .dbf, .prj, .shp and .shp form a so-called ShapeFile, a popular geospatial data format that is used by GIS software. To load this into our database, we also need GDAL, the Geospatial Data Abstraction Library. When we install GDAL we also get OGR, OpenGIS Simple Features Reference Implementation, a vector data translation library that we can use to translate the shapefile into data.

    1. GDAL can be easily installed with the package manager:

      $ sudo apt install gdal-bin
      
    2. Then we switch to the postgresql user:

      $ sudo -i -u postgres
      
    3. Now we convert the shapefile with ogr2ogr and import it into our database:

      $ ogr2ogr -f PostgreSQL PG:dbname=postgis_db -progress \
          -nlt PROMOTE_TO_MULTI \
          /srv/jupyter/nedata/ne_110m_admin_0_countries.shp
      0...10...20...30...40...50...60...70...80...90...100 - done.
      
      -f PostgreSQL

      indicates that the target is a PostgreSQL database

      PG:dbname=postgis_db

      specifies the PostgreSQL database name. In addition to the name, other options can also be specified, in general:

      PG:"dbname='db_ename' host='addr' port='5432' user='x' password='y'"
      
      -progress

      outputs a progress bar

      -nlt PROMOTE_TO_MULTI

      indicates that all object types should be loaded into the database as multipolygons

      /home/veit/nedata/ne_110m_admin_0_countries.shp

      specifies the path to the input file

      See also

    4. Check the import with ogrinfo

      $ ogrinfo -so PG:dbname=postgis_db ne_110m_admin_0_countries
      Output
      INFO: Open of `PG:dbname=postgis_db'
            using driver `PostgreSQL' successful.
      
      Layer name: ne_110m_admin_0_countries
      Geometry: Multi Polygon
      Feature Count: 177
      
      
    5. Alternatively, we can also list individual tables:

      $ psql -d postgis_db
      postgis_db=# \dt
                         List of relations
       Schema |           Name            | Type  |  Owner
      --------+---------------------------+-------+----------
       public | ne_110m_admin_0_countries | table | postgres
       public | spatial_ref_sys           | table | postgres
      (2 rows)
      
    6. Finally, we can log out of the database with

      psql> \q