A spatial database stays useful over time only when it has a clear schema, correct data types and defined integrity rules. Setup begins with data modelling and continues through deployment, bulk loading, indexing and access configuration. At Payeshgaran Farasanjesh Tabiat Fars this service is applied where data volume, number of concurrent users or the need for history makes management of loose files impractical. Below we describe schema design, the choice between PostGIS and GeoPackage, access control, backup and restore, and how layers are published.
Schema Design and Normalisation
The starting point of any spatial database is identifying the entities: which phenomena must be stored, what relationships exist between them, and which keys keep those relationships stable. Spatial features live in tables holding geometry and attributes, while descriptive tables connect to them through primary and foreign keys.
Normalisation prevents duplication and inconsistency. In spatial projects the third normal form is usually sufficient, but excessive splitting of tables can hurt the performance of spatial queries. Reference tables for land-use codes, road classification or parcel status define repeated values only once.
The deliverable of this stage is a schema document together with a data dictionary that records the name, type, unit, permitted range and description of every field, so that different teams work from one shared vocabulary.
Spatial Types, CRS and Spatial Indexing
In spatial platforms geometry is held in typed columns and spatial functions such as area, buffer and validity checks run directly against those columns. Every layer must carry its coordinate reference system; inserting geometry with a mismatched CRS creates silent errors. For local work in Iran, UTM zone 39 (EPSG:32639) is common.
A spatial index is the difference between a fast query and a full table scan. Tree-based structures such as GiST in PostGIS store the bounding box of each feature and accelerate extent and proximity queries. After a bulk load the index must be rebuilt and its statistics refreshed.
- GiST index on the geometry column in PostgreSQL and PostGIS
- B-tree indexes on keys and on fields frequently used in filters
- Index rebuild and ANALYZE after a bulk data load
- Reading query plans to locate expensive sequential scans
- Partitioning large tables by extent or by date
Choosing a Platform: PostGIS or GeoPackage
PostGIS on PostgreSQL suits projects that need several concurrent users, high data volume, transactions and OGC service delivery. Role definition, restricted views and complex queries make this platform appropriate for organisational systems.
GeoPackage is a single, standards-based file suited to single-user projects, data handover and field work without a network connection. The absence of a server dependency and the simplicity of transfer make it convenient for survey and field review.
In many projects the two are not competitors: data is produced and reviewed in GeoPackage and, once approved, loaded into PostGIS, which then acts as the single source for publication.
Constraints, Domains and Data Integrity
A database without rules is only a data store. Mandatory fields, unique keys, domain constraints on permitted codes and valid foreign references prevent inconsistent data from entering. These rules are enforced at the database level so they remain effective regardless of the editing software.
Codes and classifications are better expressed as domains and permitted value lists than as free text. Numeric fields are bounded to a sensible range so that out-of-range values are rejected.
On the geometric side, geometry validity and CRS conformance are checked, and topology rules are defined: no overlap between parcels, no gaps in a continuous coverage, and complete connectivity of boundaries.
Versioning and Change History
For cadastral, infrastructure and natural-resource data, knowing when a feature changed, by whom and how is part of the data itself. History tables combined with database triggers retain the previous version on every edit.
Two common approaches exist: keeping a full snapshot of the state at defined intervals, and recording change events step by step. The first suits periodic reporting, the second precise tracking of corrections.
In urban base-map and parcel mapping projects this mechanism allows two points in time to be compared and a previous state to be restored where needed.
Roles, Permissions and Backup
Access to a spatial database is defined by role: a read-only role for consumers, an editor role for the survey team and an administrative role for schema changes. Publishing layers through restricted views keeps sensitive columns under control.
Periodic backup and restore testing are part of setup, not a post-incident task. A backup copy must be restored in a separate environment and checked, so that its integrity is known rather than assumed.
Network connections are protected with encryption and host allow-lists to reduce unauthorised access to spatial data.
Publishing Layers and Performance Tuning
Once the database is deployed, layers are usually published through standard services so they can be consumed by web systems and desktop software. The service type is chosen according to consumer needs, not technical preference.
- WMS and WMTS for rendered display and tiled delivery
- WFS for editing and exchange of vector features
- WCS for access to raster data at original values
- Materialised views for heavy, frequently repeated queries
For performance, views and materialised views simplify complex queries, while tiling and caching reduce server load on heavily visited systems. Routine maintenance covers clearing dead rows, rebuilding indexes and refreshing statistics.
Summary
Spatial database setup is a multi-stage project running from schema design and normalisation through indexing, rule definition, versioning, access control and service publication. The quality of these stages decides whether the data remains dependable over years or degrades into an inconsistent collection.
At Payeshgaran Farasanjesh Tabiat Fars, delivery includes the data structure, the dictionary, the access documentation and the maintenance routine, so that the client can operate the database independently. The right decisions about platform, keys and constraints at the outset significantly reduce the cost of later rework.