Intro to Geodatabases
Ben Hickson
UA Libraries
bhickson@email.arizona.edu
The Database
- A structured collection of data organized for efficient retrieval
- e.g. Tables of data organized into rows and columns (for Relational Databases)
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
- Coded value domain
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