Skip to content

gis_guide

Jump to bottom
Ryan Hodges edited this page Mar 2, 2024 · 13 revisions

TEKDB GIS User Guide

This guide is intended to assist GIS Technicians, Analysts, and other professionals connect to the spatial data in TEKDB’s PosgreSQL+PostGIS database. It is not intended to function as a guide for how to interpret and use the data - it instead assumes users are familiar with the features of the tools they are using to connect to the database, and they know why they are accessing the data.

This document assumes the TEK DB tool was installed on a Linux server. If it was installed on a Windows server, GIS connectivity MIGHT not be possible. Ask your System Administrator if you do not know, though at the time of this writing all known deployments are on Linux.

Connecting to the PostgreSQL/PostGIS database with ArcMap is possible, but you will only have 'read only' access: You can pull data out, and do whatever you want with it, but you can't write to or edit records inside of the database. If you need this level of access, it is recommended you look into using another tool such as QGIS. Detailed instructions for connecting to both are included in this document.

Connecting ArcGIS to TEKDB’s Spatial Data

Based on steps from this guide for ArcMap 10.4

Adding the connection to your catalog

To connect to the database, a connection record will need to be added to the catalog, either via ArcMap or ArcCatalog. These steps walk you through this using ArcMap:

  • Collect the following information from your Systems Administrator or IT department (determined during Server Installation):
    • The TEK DB IP address
      • The format (IPv4) should be 4 numbers between 0 and 255 separated by a period, such as 192.168.0.1
    • The TEK PostgreSQL DB user name
    • The TEK PostgreSQL DB user password
    • The TEK PostgreSQL DB name
  • Open ArcMap
  • Open Catalog
    • This can be found under the 'Windows' menu in the top bar
  • Expand the selection menu in the location bar and select 'Database Connections'
  • Under the 'Database Connections' folder, select 'Add Database Connection'
  • Fill out the following fields in the resulting Dialog Box:
    • Database Platform: 'PostgreSQL'
    • Instance: The IP address of the server collected above
    • Authentication Type: 'Database authentication'
      • User name: the PostgreSQL user name collected above
      • Password: the PostgreSQL user password collected above
      • Save user name and password
    • Database: A database of the name collected above should be available in the drop-down list once ArcGIS has had a moment to connect after entering the user's password.
      • NOTE: ArcMap/ArcPro and other Esri products only support connecting to certain versions on PostGIS. If you are on an older version of ArcMap, you may not be able to connect to newer versions of PostGIS. Likewise newer versions of Esri may not be able to connect to older versions of PostGIS. In some cases, you can connect, though there will be certain error messages, such as an alert that ArcMap could not connect to the database, but still resulting in being able to load data in.
  • Click 'OK'

Adding The Layers To Your Map

Spatial data is stored in PostgreSQL/PostGIS differently than most spatial sources: features of all types may be stored in the same layer or table. TEK allows Points, Lines, and Polygons to all exist in it's primary spatial table 'places'. ArcMap requires that all features on a layer be of the same type, so to see all features, the layer will need to be added three times.

  • Under your connection to the TEK DB in your catalog you will see a great number of tables, all prefixed with the database name, followed by '.public.'.
  • Two spatial tables exist: 'locality' and 'places' - please consult with your TEK Application Administrator as to whether your instance uses 'locality'. This document only specifically covers 'places', but managing 'locality' would be much the same.
  • Add '(db name).public.places' to your 'Table Of Contents' three times.
  • Note which feature type (Point, Line, Polygon) is represented in all three.
  • Change the other two so that all three are represented:
    • Right Click on the layer name
    • Select 'Properties'
    • Under the 'Source' tab:
      • Click the ‘Change Query’ button
      • Under ‘List of Tables, select tekdb.public.places
      • Leave the ‘Query’ alone
      • Select ‘Let ArcGIS discover spatial properties for the layer’
        • You may define your own: the SRID is 3857
      • Click ‘Next’
      • ‘placeid’ should already be selected as the ‘Unique Identifier Field’
      • Change ‘Geometry Type’ to ‘Point’, ‘Line’ or ‘Polygon’
        • Depending on which feature type you wish this layer to present
      • Click ‘Finish’
      • Click ‘Apply’

Connecting QGIS to TEKDB’s Spatial Data

QGIS is an Open Source GIS desktop tool that is available for free. While it is not as well documented, supported, popular, or user-friendly as Esri's tools, it is rich with features, easier to connect to the ITK Database, and most importantly, supports both reading and writing to the database.

Connecting to your PostgreSQL/PostGIS

To connect to the database, you will need the following info:

  • The ITK DB URL or IP Address (most likely the same as your ITK DB application)
  • The ITK PostgreSQL Database Name
  • The ITK PostgreSQL Database Username
  • The ITK PostgreSQL Database Password
  • The ITK PostgreSQL Port (5432 by default)
  1. Open QGIS
  2. Open a new or existing project
  3. From the top menu, select Layer > Add Layer > Add PostGIS Layer
  4. If you have never connected to the database before:
    • click the 'New' button
    • Name: Provide a meaningful name for your database connection, like "ITK DB"
    • Service: leave this field blank
    • Host: your database's URL or IP Address
      • i.e. "itkdb.mysite.com" or "123.45.67.89"
    • Port: the port PostgreSQL is using (most likely 5432)
    • Database: your database's name
    • SSL mode: 'disable'
    • Authentication:
      • Click on the 'Basic' tab
      • User name: the username for connecting to your database
      • Password: the password for connecting to your database with the given username
    • You can now click on 'Test Connection' to see if everything is configured correctly.
      • If this fails, consult the 'Troubleshooting Connections' section below - you may need to coordinate with a System Administrator to sort the connection out.
    • You can safely ignore the remaining check boxes: they should be empty by default
    • Click 'OK'
  5. After creating or selecting your Database under 'Connections', click on 'Connect'
  6. Below in the big box you should see "> public" in the 'Schema' column
  7. Click on '> public' to expand it
  8. You should now see several rows, including at least 1 (possibly more) row with the 'places' in the 'Table' column
  9. If you see multiple 'places' rows with different values for 'Spatial Type':
    • Click on one row
    • Click the 'Add' button below
    • repeat for the remaining rows
  10. If instead you only see one 'places' row and it says 'Select...' for 'Spatial Type':
    • Click on 'select' to reveal a drop-down selector
    • Select 'MultiPoint' from the selector (or 'Point')
    • Click on the 'Add' button at the bottom
    • Click (again) on the 'Connect' button at the top to refresh your connection
    • Click (again) on '> public' to show all of your tables
    • Repeat the process to select and add 'MultiLineString' and 'MultiPolygon' (or 'LineString' and 'Polygon')
  11. Once you've added a '(Muli)Point', '(Multi)LineString', and '(Multi)Polygon' layer, click the 'Close button' to close the 'Connection' dialog box.
  12. You should now see all of the Points, Lines, and Polygons from your database on the map.

Information on how to use, edit, and manipulate the data you have loaded into your project is beyond the scope of this document, but please refer to the QGIS Documentation for more information.

NOTE: Unlike ArcGIS, QGIS offers Read/Write access to the database: if you change or delete any features and save those changes, you WILL BE CHANGING THE DATA IN THE ITK DB APPLICATION. To avoid this, it is recommended you export the data to a new file (such as a Shapefile or GeoPackage) and make your edits locally.

Layer Information

The 'places' table contains the following attributes. Notes on fields that require a join are included as well:

  • enteredbyname - the name of the user who first created the data in the database, if available
  • enteredbytribe - the name of the tribe that the user who first created the data in the database belongs to
  • enteredbytitle - the title of the user who first created the data in the database
  • enteredbydate - the date that this record was first created
  • modifiedbyname - the name of the last user to modify this record
  • modifiedbytitle - the title of the last user to modify this record
  • modifiedbytribe - the tribe of the last user to modify this record
  • modifiedbydate - the date this record was last edited
  • placeid - the primary key of the table
  • indigenousplacename - the place's traditional indigenous name
  • indigenouosplacenamemeaning - a translation of the place's indigenous name to English
  • englishplacename - the English name used for the place
  • islocked - a leftover remnant from the MTKEDB database (deprecated)
  • geometry - the spatial data field, possibly represented as the geometry type
  • source - the name of the source of this data
  • digitizedby - the name of the user who digitized this record
  • digitizeddate - the date this record was digitized
  • planningunitid - the ID of the planning unit this place is associated with
    • Join with '(db name).public.lookupplanningunit' to get the name
  • primaryhabitat - the ID of the habitat type
    • Join with '(db name).public.lookuphabitat' to get the name
  • tribeid - the ID of the tribe
    • Join with '(db name).public.lookuptribe' to get the name

For More information on what data is available in the remaining layers and how they are joined, please see the Layer Definitions documentation.

Other Ways To Connect

  • Web Browser - The TEK application displays spatial data on maps within most web browsers. Administrators even have the ability to create, edit, and delete this data.

Troubleshooting Connections

There are MANY barriers to connecting a GIS tool to the PostGIS database. Please double check each of these in order as we work our way from ensuring that the configurations on your end are correct first, and work our way back into double checking the difficult-to-access server settings that require a SysAdmin to change.

Issues on the User's computer

  1. Are you connected to the correct network?
    • Many instances of the ITKDB are installed on an internal network, and are not accessible to the broader internet. If you cannot open the ITK DB Application in a browser, it's unlikely you will be able to connect to the database without VPN tunneling into the network or plugging in to it physically. If your instance is available on the Internet, this same question holds: Are you connected to the Internet?
  2. Have you properly entered in the correct database web address, name, user, password, and port?
  3. Did you disable SSL (for QGIS) and follow the recommended settings in the instructions above?
  4. When you try to connect, do you get some sort of error message?
    • If so, copy it down exactly or take a screenshot. It may be very helpful to resolve more difficult problems
  5. Is your version of GIS software compatible with the version of PostgreSQL/PostGIS on the server?
    • This is more likely for ArcGIS users rather than QGIS users. Please refer to the docs and ask your SysAdmin to share which versions of PostgreSQL and PostGIS are running. You may need to install a different version of GIS software to continue

Issues on the Network

  1. Is the network running?
    • same as the previous section, you may have connected to your network, but it could have crashed. Test by seeing if you can open the application in a web browser.
  2. Is there a firewall blocking communication from your computer to the server?
    • This is worth reviewing with a SysAdmin to make sure that traffic from your computer's IP address is able to get through to the server using the specified port

Issues on the Server

From here on, there is a chance that system logs will tell your SysAdmin a bigger story. Those logs (for Linux servers) are most likely stored at /var/log/postgresql/postgresql-??-main.log where ?? is your PostgreSQL version number.

  1. Is traffic to PostgreSQL's port getting through, or being blocked?
    • This could be the result of a firewall on the server, NGINX, or some other application intercepting traffic
  2. Is the postgresql service running?
    • on Linux, test with sudo service postgresql status
  3. Is the permissions configuration file pg_hba.conf configured to allow:
    • Connecting from outside of the server?
      • The lines will start with host rather than local
    • Connecting to the correct database name?
      • The second word will be the database name or all
      • This might be case-sensitive
    • Connecting as the provided user name?
      • This is the 3rd item on the line
    • Connecting from the user's IP address?
      • The 4th item should be the user's IP address or an open mask like 0.0.0.0/0 for IPV4 or ::/0 for IPV6
      • The user can determine their IP address by visiting sites like https://whatismyipaddress.com/.
      • It's important to know whether a user's IP might change: are they on the network, or are they on their own consumer-grade ISP connection to the Network? Are they using a VPN service for any reason other than to connect TO the specified network? Etc...
    • Is the 5th item on the line md5?
      • md5 is probably what you want, but see next section on configuring postgresql.conf regarding testing other methods like scram-sha-256
      • If your database doesn't have anything sensitive yet, this value can be set to trust for the sakes of testing connectivity: THIS WILL ALLOW ANYONE ON THE NETWORK WHO HAS THE DATABASE NAME AND USER NAME TO HAVE FULL READ/WRITE ACCESS TO THE DATABASE REGARDLESS OF PASSWORD.
  4. Are the PostgreSQL settings postgresql.conf configured correctly?
    • listen_addresses: should be your network's IP address or '*' for allowing connections from anywhere on the internet.
    • port: is this the same port address that is specified by the user
    • password_encryption: This should match the 'method' provided in pg_hba.conf in the last section
    • ssl: When this is on some versions of ArcMap error out. This may be due to being incompatible with the version of PostgreSQL or PostGIS. Leaving this on (rather than off) does not seem to impact QGIS, which can specify disabling SSL for authentication.
  5. Double check some settings with the ITK DB Application's Django Framework
    • Django is not required to be configured correctly for GIS tools to connect to PostgreSQL, but if the web application is working and able to read the database, then it can easily confirm what the right settings are:
      • Open Django's Python management console, either by running dj shell from the command line, or by activating the Python Virtual Environment and running python manage.py shell in your project folder
      • from django.conf import settings
      • settings.DATABASES
      • You should see values for NAME, USER, PASSWORD, and PORT

Differences between TEKDB and MTKEDB (pre-2016)

The MTKEDB was an AccessDB where a separate spatially-enabled Access database was made available so both the MTKEDB Application users and the GIS crew on ArcGIS could enter info and sync data about the places in the database. Also, it is intended to be installed on Windows servers within a network and run via Microsoft Access.

The new TEKDB is a web application built on a PostgreSQL database. Spatially-enabled with PostGIS, all of the data can coincide in the same database at once without having to synchronize on an ID field. It is installed on a Linux server and run through a web-browser.

Connecting to the old tool was somewhat automated - install and run the add-in, connect to the spatial-database to the places and localities layers, point the application toolbar at the non-spatial database to define the joining ID between the two databases. It came pre-zoomed in to the Tolowa region with a number of helpful layers: NatGeo_World_Map, Populated Places, Water Features, Streams, Water Bodies, Township and Range, Counties, and Quads.

The convenience of the add-in is commendable, and it is recommended that if you are used to it, keep the template it created for MTKEDB projects, but going forward, you will want to replace the old Places and Localities layers with the new ones (by connecting to the PostgreSQL database, as described below).

Since the new DB is consolidated, the old styling on places will not carry over and will have to be redone. Also, a few fields (such as FeatType) now have new names (such as primaryhabitat) that match the non-spatial Access Database rather than the spatial one integer values, representing an ID in the lookuphabitat table.

Despite this inconvenience, there are a number of advantages to the new database. The largest of which is that data browsers can see spatial data within the tool (in fact, editors can even edit this spatial data), and in the meantime, GIS professionals can access all of the data directly through ArcGIS or QGIS. Another major difference, in terms of maintaining database integrity, is the consolidation of the Places and Localities tables - data no longer has to be duplicated between the spatial and non-spatial tables and kept in sync. All parties are looking at the same live data.

Clone this wiki locally