Intro to Geodatabases

 

Ben Hickson

UA Libraries

bhickson@email.arizona.edu

The Database

  • A structured collection of data organized for efficient retrieval

 

 

 If you understand spreadsheets, you can handle databases

https://www.xkcd.com/327/

DBMS

Database Management System

  • The database, plus software to make the information usable (rules, schemas, reports, query functionality, etc)
  • Gives meaning to the bits and bytes
  • Typically implements Structured Query Language (SQL)

 

Databases for Geo

A Geodatabase

  • A database, but with spatial capabilities added

    • Geometry and Geography

    • Spatial Indexing to speed up

    • Spatial Operations

      • Constructors

      • Measurements

      • Functions (intersections, buffers, clipping, etc)

      • Predicates (true/false relationships)

Why use a GDB

  • Shapefiles are inefficient, clunky, and cumbersome
  • GDBs aren't "lossy" - preservation of field names!
  • Support NULL values
  • Efficient geometry storage (typically 1/3 less than shapefiles)
  • Much, much faster on larger datasets
  • Single organizational structure (vector and raster, too)
  • Support for complex features (e.g. networks, topologies) and versioning
  • Support for rules
  • Large size limits on files & field lengths
  • Better data type support

 

Esri File Geodatabases

  • Function much like a folder of compressed files in windows
  • Can be explored from windows explorer, but is uninterpretable
  • 1 TB Size Limit on Files
  • Only allows one user at a time to access and change information
  • Good compression

 

Downsides: File GDBs are Esri Specific

TIP: Never use a "Personal Geodatabase"

ArcGIS Enterprise

  • A true relational database (RDBMS)
  • Can store datasets in several DBMS options (PostgreSQL, Microsoft SQL, Oracle, etc)
  • Allows more than one user at a time to make edits
  • Track changes (versioning)
  • Can contain relationships
  • Nearly unlimited in size and users
  • Secured by the DBMS
  • An extension for PostgreSQL databases enabling spatial support (types, indexes, and functions)
  • OpenSource
  • Widely used (preferred data store for QGIS)
  • Very fast spatial querying
  • WebServer functionality
  • SQL-like queries for spatial data
  • ArcGIS relational datastore are built on PostgreSQL
     
  • No native support in ArcMap/Pro
    • extensions available

Relationships

  • Act like a "Join" in Arc
  • A key power of the relational database
  • 3 Types
    1. One-to-one relationships
    2. One-to-many relationships
    3. Many-to-many relationships
  • Reduces redundancy
  • Improve efficiency of data

http://www.oocities.org/unifiedmodel/image001.jpg

Domains and Subtypes

  • Subtypes
    • Allows for easy assignment of attribute values
    • Assigned by a code or description identified in the attribute table
    • Codes are either integer or long data types
  • Domains
    • Coded value domain
      • Specify a valid set of values for an attribute
    • Range Domain
      • Specify a valid range of values in a numeric field

Successor the shapefile?

Perhaps GeoPackage

 

  • SQLLite with spatial functions added
  • Can contain multiple feature classes of vector or raster data
  • Many of the same upsides as a file GDB
  • A SINGLE FILE
  • Platform independent
  • Supports views
  • Officially recognized OGC standard

Sources

Intro to Geodatabases

By Benjamin Hickson

Intro to Geodatabases

  • 1,083