Karttur's GeoImagine Framework:
Set up the database (setup_db)

Introduction

In Karttur’s GeoImagine Framework both the definition of processes and the metadata for all spatial datasets and layers are stored in a postgreSQL (postgres) database. The database is built up with schemas associated with typical processes, data sources and projection systems. This post explains how to setup the complete database by using the special python package setup_db and prepared json files defining all the schemas and tables. All json command files are listed as web (html) documents and in the GitHub repo geoimagine03-setup_db.

This post is a step by step manual for separate downloading and then running the Framework python package setup_db. You can also import the complete Framework (using a range of different approaches) and then run setup_db. Even if you restore the database using postgres, as suggested in the next paragraph, you need to setup the setup_db package.

If you intend to use the Framework with predefined settings, you can build the complete database using one of the built-in postgres restore functions, explained in the post on Database backup and restore. With the latter alternative you can skip the rest of this post and move on to Set up custom system.

Prerequisites

You must have the complete Spatial Data Integrated Development Environment (SPIDE) installed as described in the blog Install and setup spatial data IDE.

Setup database

The blogpost Connect Python and PostgreSQL using psycopg2 contains a script for creating databases in postgres using Python. And the blogpost Postgres setup with Python & xml describes how to combine xml and python to create tables. More recent versions of the Framework instead use json objects for parameterisation. But if you want to understand the principles behind the framework package setup_db, the blog posts above explains just that.

db_setup python package

The framework v3.0 package setup_db is available on GitHub (click the link or see the figure below) and contains the complete structure for setting up the Framework postgres database.

The db_setup package contains five .py files, the standard modules __init__.py and version.py, plus one main module, one class module and one module that reads json files:

• setup_db_main.py
• setup_db_class.py
• paramjson_mini.py

The package also contains a subfolder called doc that contains 4 text files:

• db_karttur_setup_YYYYMMD.txt
• db_karttur_setup-easegrid_YYYYMMDD.txt
• db_karttur_dbusers_YYYMMDD.txt
• db_karttur_delete_YYYYMMDD.txt

The file db_karttur_setup_YYYYMMD.txt lists, in sequence, all the json objects (files) to execute for installing the basic postgres schemas and tables. Optionally you can install the tables that define the Equal-Area Scalable Earth (EASE) Grid version 2.0, schema and tables with the file db_karttur_setup-easegrid_YYYYMMDD.txt. The file db_karttur_dbusers_YYYMMDD.txt links to the installation of all the database roles (users). If you want to change the default users and passwords you need to edit the linked json file. The last file db_karttur_delete_YYYYMMDD.txt links to a json command file that empties all the content of the database and removes all tables and schemas.

Under the doc directory there is also a sub-directory, jsonsql, that contains all the json files listed in txt. The same json command files are also available as web (html) documents.

Download the package

Go to the GitHub repo for geoimagine03-setup_db. Click the Code button and then Download ZIP.

Create a hierarchical python package in eclipse

Start Eclipse. Create a project (call it what you want):

File : New : Project : PyDev project

Create a new PyDev Package and call it ‘geoimagine’:

File : New : PyDev Package

Create a new sub-package and call it ‘db_setup’:

File : New : PyDev Package

In the dialog window you should see the main package (‘geoimagine’) already filled (geoimagine). Use Python syntax and add a dot (.) followed by the name of the sub-package (‘db_setup’) (geoimagine.db_setup).

Use the Finder and navigate to the download/clone of the setup_db package, mark all of the content and drop them in the newly created sub-package in the navigation frame of Eclipse.

You should now have a sub-package with a file structure that looks similar to this:

.
├── LICENSE
├── README.md
├── __init__.py
├── paramjson_mini.py
├── setup_db_class.py
├── setup_db_main.py
├── version.py
└── doc
   ├── db_karttur_dbusers_20211018.txt
   ├── db_karttur_delete_20211202.txt
   ├── db_karttur_setup-easegrid_20211018.txt
   ├── db_karttur_setup_20211018.txt
   └── jsonsql
       ├── EASE2N_template_v090_sql.json
       ├── EASE2N_v090_sql.json
       ├── EASE2S_template_v090_sql.json
       ├── EASE2S_v090_sql.json
       ├── MODISpolar_template_v090_sql.json
       ├── MODISpolar_v090_sql.json
       ├── SMAP_products_v090_sql.json
       ├── SMAP_template_v091_sql.json
       ├── SMAP_v090_sql.json
       ├── ancillary_v090_sql.json
       ├── climateindexes_v090_sql.json
       ├── compositions_ancillary_v090_sql.json
       ├── compositions_ease2_v090_sql.json
       ├── compositions_export_v090_sql.json
       ├── compositions_landsat_v090_sql.json
       ├── compositions_modis_v090_sql.json
       ├── compositions_modispolar_v090_sql.json
       ├── compositions_regions_v090_sql.json
       ├── compositions_sentinel_v090_sql.json
       ├── compositions_smap_v090_sql.json
       ├── compositions_specimen_v090_sql.json
       ├── compositions_system_v090_sql.json
       ├── db-delete_v090.json
       ├── db-dump_v090.json
       ├── default_system_regions_v090_sql.json
       ├── ease2_schema_v090_sql.json
       ├── ease_tile_regions_v090_sql.json
       ├── ease_tilecoords_v090_sql.json
       ├── endmember_v090_sql.json
       ├── general_GDAL_v090_sql.json
       ├── general_GDALtypes_v090_sql.json
       ├── general_grant_karttur_v090_sql.json
       ├── general_processes_v090_sql.json
       ├── general_processeschain_v090_sql.json
       ├── general_records_v090_sql.json
       ├── general_schema_v090_sql.json
       ├── landsat_scenecoords_v090_sql.json
       ├── landsat_scenes_bands_v090_sql.json
       ├── landsat_templates_v090_sql.json
       ├── landsat_tilecoords_v090_sql.json
       ├── landsat_usgs_meta_v090_sql.json
       ├── layout_v090_sql.json
       ├── modis_daacdata_v090_sql.json
       ├── modis_scenes_bands_v090_sql.json
       ├── modis_template_v090_sql.json
       ├── modis_tile_regions_v090_sql.json
       ├── modis_tilecoords_v090_sql.json
       ├── modis_tiles_v090_sql.json
       ├── process_default-comp-naming_v090_sql.json
       ├── regions_v090_sql.json
       ├── sentinel_scenes_bands_v090_sql.json
       ├── sentinel_template_v090_sql.json
       ├── sentinel_tilecoords_v090_sql.json
       ├── soilmoisture_v090_sql.json
       ├── specimen_satdata_v090_sql.json
       ├── specimen_satdata_v80_sql.json
       ├── specimen_v090_sql.json
       ├── system_regions_v090_sql.json
       ├── system_v090_sql.json
       ├── topo_v090_sql.json
       ├── user_projects_v090_sql.json
       ├── user_super_v090_sql.json
       └── users_v090_sql.json

Package processes

The database setup is accomplished through a set of special processes:

• createschema
• createtable
• tableinsert
• tableupdate
• grant
• deletedatabase

These processes are not defined in the database itself and can only be run from the package setup_db.

setup_db_main.py

In setup_db_main.py there are predefined commands and links in the main section that are used to setup the entire database. You do not need to edit any of these files unless you want to change the default roles (users) as explained in the preparation post on Database connections.

Create main production db

If you accept all the default settings all you have to do is to open setup_db_main.py and then run the module from the Eclipse menu Run : run.

If you want to customise the setup, you need to edit the in the json command files and also under the “__main__” section of class=’module’>setup_db_main.py. If you want to check the database or change/add superuser(s) please refer to the post on Install PostgreSQL and postGIS. If you want to make more substantial changes, all the json command files are listed and linked further down.

if __name__ == "__main__":

    '''
    This module should only be run at the very startup of building the Karttur GeoImagine framework.
    To run, remove the comment "#prodDB" and set the name of your production DB ("YourProdDB")
    '''

    # Set the name of the productions db cluster
    # prodDB = 'YourProdDB' #'e.g. postgres or geoimagine
    prodDB = 'geoimagine'

    '''
    SetUpProdDb creates an empty Postgres database.
    '''
    SetUpProdDb(prodDB)

    '''
    SetupSchemasTables creates schemas and tables from json files, with the relative path to the
    json files given in the plain text file "projFPN".
    '''
    projFPN = path.join('doc','db_karttur_setup_20211018.txt')
    SetupSchemasTables(projFPN,prodDB)

    '''
    The command list file doc/db_karttur_setup-easegrid_20211018.txt links to
    a set of json commands that setup the three EASEGRID 2 projection systems, inlcuding tables for MODIS and SMAP data)
    '''
    projFPN = path.join('doc','db_karttur_setup-easegrid_20211018.txt')
    SetupSchemasTables(projFPN,prodDB)

    '''
    #db_karttur_dbusers_YYYYMMDDX.xml adds db users for handling connections to postgres db
    '''
    projFPN = path.join('doc','db_karttur_dbusers_20211018.txt')
    SetupSchemasTables(projFPN,prodDB)

    '''
    #db_karttur_delete_YYYYMMDDX.xml deletes the complete database
    projFPN = path.join('doc','db_karttur_delete_20211202.txt')
    # WARNING - running this command will delete the complete db #SetupSchemasTables(projFPN,prodDB)
    '''

Setup default schemas and tables

The json commands for setup of default schemas and tables are defined are linked via the text file db_karttur_setup_YYYYMMDD.txt. To inspect the content of the json command files, toggle the Hide/Show button and click on the json file names.

Hide/Show db_karttur_setup_YYYYMMDD.txt

  
# Install the default database schemas
general_schema_v090_sql.json

# Install tables for handling paths and processes
general_processes_v090_sql.json

# Install tables for default naming of subprocesses destination layers
process_default-comp-naming_v090_sql.json

# Add records for super users and the process for managing all other processes
general_records_v090_sql.json

# Install and fill the tables that define the different cell types and file types that the system can handle
general_GDALtypes_v090_sql.json

# Install the automated processing chains tables
general_processeschain_v090_sql.json

# Install the tables for defining ancillary compositions
compositions_ancillary_v090_sql.json

# Install the tables for defining export compositions
compositions_export_v090_sql.json

# Install the tables for defining landsat compositions
compositions_landsat_v090_sql.json

# Installs the tables for defining modis compositions
compositions_modis_v090_sql.json

# Installs the tables for defining modis-polar compositions
compositions_modispolar_v090_sql.json

# Installs the tables for defining region compositions
compositions_regions_v090_sql.json

# Install the tables for defining sentinel compositions
compositions_sentinel_v090_sql.json

# Installs and fills tables for DAAC SMAP data holdings
SMAP_v090_sql.json

# Installs the tables for defining smap compositions
compositions_smap_v090_sql.json

# Installs the tables for defining specimen compositions
compositions_specimen_v090_sql.json

# Install the tables for defining system compositions
compositions_system_v090_sql.json

# Install region tables for the default systems
default_system_regions_v090_sql.json

# Install the tables that defines ancillary data sources
ancillary_v090_sql.json

# Add coordinates for landsat tiles
landsat_scenecoords_v090_sql.json

# Install tables for landsat scenes, bands and masks
landsat_scenes_bands_v090_sql.json

# Install landsat template table
landsat_templates_v090_sql.json

# Add tables for layout
layout_v090_sql.json

# MODIS regional tiles for land and continents
modis_tile_regions_v090_sql.json

# Add tables for tiles both online datapool and locally downloaded and processed
modis_daacdata_v090_sql.json

# Add the table for holding all scenes available at the datapool and tables for local MODIS data holdings
modis_scenes_bands_v090_sql.json

# Install MODIS template table, and add records for the MODIS products in use
modis_template_v090_sql.json

# Add table for MDOIS tile coordinates
modis_tilecoords_v090_sql.json

# Install tables that hold system default regions and categories
system_regions_v090_sql.json

# Install tables that hold regions
regions_v090_sql.json

# User projects - defines users and user projects
user_projects_v090_sql.json

# Install tables that hold all the system users
users_v090_sql.json

# Add superusers of the system,
user_super_v090_sql.json

# Adds the schema and table for climate indexes
climateindexes_v090_sql.json

# Add tables for sentinel scenes, granules bands
sentinel_scenes_bands_v090_sql.json

# Add the coordinates for sentinel tiles
sentinel_tilecoords_v090_sql.json

# Add the sentinel tile templates
sentinel_template_v090_sql.json

# Create table for SMAP products and inserts records
SMAP_products_v090_sql.json

# Add the SMAP product templates
SMAP_template_v091_sql.json

# Add tables for MODIS polar datasets
MODISpolar_v090_sql.json

# MODISpolar templates (identical to SMAP templates)
MODISpolar_template_v090_sql.json

# Create soilmoisture tables
soilmoisture_v090_sql.json

Setup EASE-grid 2 schema and tables

The json commands for setup of the schema and tables for the EASE-grid 2.0 projection system are defined linked via the text file db_karttur_setup-easegrid_YYYYMMDD.txt. To inspect the content of the json command files, toggle the Hide/Show button and click on the json file names.

Hide/Show db_karttur_setup-easegrid_YYYYMMDD.txt

  
# Install schema for EASEGRID 2
ease2_schema_v090_sql.json

# Install the tables defining all EASEGRID compositions
compositions_ease2_v090_sql.json

# Table for EASEGRID 2 regional tiles
ease_tile_regions_v090_sql.json

# Table for EASEGRID 2 tile coordinates
ease_tilecoords_v090_sql.json

# Table for templates EASE GRID north
EASE2N_template_v090_sql.json

# Table for EASE GRID north
EASE2N_v090_sql.json

# Table for templates EASE GRID south
EASE2S_template_v090_sql.json

# Table for EASE GRID south
EASE2S_v090_sql.json

Add default users

The json commands for adding default users to the Framework are defined linked via the text file db_karttur_dbusers_20211018.txt. Only a single json is linked general_grant_karttur_v090_sql.json. The json command file contains all the database roles and rights, including passwords. You need to edit the passwords to match the .netrc file that is used for accessing the database at runtime.

To inspect the content of the single linked json command file, toggle the Hide/Show button and click on the json file names.

Hide/Show db_karttur_setup-easegrid_YYYYMMDD.txt

  
# Add db users for handling connections to postgres db
general_grant_karttur_v090_sql.json

Delete database

The last lines under the __main__” section link to the text file db_karttur_delete_YYYYMMDD.txt. If you run the commands in the linked json file, the complete content of the database will be erased. To inspect the content of the single linked json command file, toggle the Hide/Show button and click on the json file names.

Hide/Show db_karttur_delete_YYYYMMDD.txt

  
# Delete the complete database
db-delete_v090.json

Superusers and process for setting up all other processes

The package db_setup also installs the system superuser and two processes: addrootproc and addsubproc. Having access to the superuser and the two processes, all other processes can be installed using the package setup_processes.

Next step

The next step is Setup processes part 1.