The AST-PostGIS is an extension for PostgreSQL/PostGIS that incorporates advanced spatial data types and implements spatial integrity constraints. The extension reduces the distance between the conceptual and the physical designs of spatial databases, by providing richer representations for geo-object and geo-field geometries. It also offers procedures to assert the consistency of spatial relationships during data updates. Such procedures can also be used before enforcing spatial integrity constraints for the first time.
Geometric primitives defined by OGC and ISO standards, implemented in most modern spatially-enabled database management systems (DBMS), are unable to capture the semantics of richer representation types, as found in current geographic data models. Moreover, relational DBMSs do not extend referential integrity mechanisms to cover spatial relationships and to support spatial integrity constraints. Rather, they usually assume that all spatial integrity checking will be carried out by the application, during the data entry process. This is not practical if the DBMS supports many applications, and can lead to redundant work.
This module has been tested on:
- Postgres 15
- PostGIS 2.2
And requires the extensions:
- postgis
If you aren't using the pg_config on your path (or don't have it on your path), specify the correct one to build against:
PG_CONFIG=/Library/PostgreSQL/9.5/bin/pg_config make
Or to build with what's on your path, just:
make
Then install:
sudo make install
After you've built and installed the artifacts, fire up psql:
postgres=# CREATE EXTENSION ast_postgis;
Use the pre-builded docker with:
docker run -v ${HOME}/pgdata:/var/lib/postgresql/data --net=host gabrielmcf/ast_postgis:9.5-2.2-1.0
Build the docker using:
docker build --rm -f Dockerfile -t ast_postgis:9.5-2.2-1.0 .
Run the server using:
docker run -v ${HOME}/pgdata:/var/lib/postgresql/data --net=host ast_postgis:9.5-2.2-1.0
Execute install-ast_postgis.bat as administrator.
You'll be prompted for your username, password and database name.
Here is explained how the extension works.
Advanced Spatial Types are essentially the primitive geometric types of PostGIS together with a set of spatial integrity constraints to control their behavior. These new spatial data types can be handled in the same way the primitive types are, as they can be employed as column definition of tables, as variables in PL/pgSQL scripts or as arguments of functions or stored procedures. They can also be stored, retrieved and updated with the geometry processing functions of PostGIS.
The following table shows the eleven advanced spatial data types implemented by the extension and how they are mapped to the PostGIS types. These types are derived from the concepts of geo-objects and geo-fields classes of the OMT-G data model.
| Spatial Class | Advanced spatial datatypes | PostGIS Type |
|---|---|---|
| Polygon | ast_polygon |
geometry(multipolygon) |
| Line | ast_line |
geometry(multilinestring) |
| Point | ast_point |
geometry(point) |
| Node | ast_node |
geometry(point) |
| Isoline | ast_isoline |
geometry(linestring) |
| Planar subdivision | ast_planarsubdivision |
geometry(multipolygon) |
| Triangular Irregular Network (TIN) | ast_tin |
geometry(polygon) |
| Tesselation | ast_tesselation |
raster |
| Sample | ast_sample |
geometry(point) |
| Unidirectional line | ast_uniline |
geometry(linestring) |
| Bidirectional line | ast_biline |
geometry(linestring) |
The following procedures can be called by triggers to assert the consistency of spatial relationships, like topological relationship, arc-node and arc-arc networks or spatial aggregation.
| Spatial Relationship | Trigger Procedure |
|---|---|
| Topological Relationship | ast_spatialrelationship(a_tbl, a_geom, b_tbl, b_geom, spatial_relation) |
| Topological Relationship (distant, near) | ast_spatialrelationship(a_tbl, a_geom, b_tbl, b_geom, spatial_relation, distance) |
| Arc-Node Network | ast_arcnodenetwork(arc_tbl, arc_geom, node_tbl, node_geom) |
| Arc-Arc Network | ast_arcnodenetwork(arc_tbl, arc_geom) |
| Spatial Aggregation | ast_aggregation(part_tbl, part_geom, whole_tbl, whole_geom) |
The spatial_relation argument, which are passed as an argument to the topological relationship procedure, can be one of the following:
- contains
- containsproperly
- covers
- coveredby
- crosses
- disjoint
- distant
- intersects
- near
- overlaps
- touches
- within
The SQL functions listed in this section can be called to analyze the consistency of the spatial database before the initial enforcement of constraints. These functions return the state of the database (true = valid, false = invalid) and register, in the ast_validation_log table, the details of each inconsistency encountered.
| Spatial Relationship | Check functions |
|---|---|
| Topological Relationship | ast_isTopologicalRelationshipValid(a_tbl text, a_geom text, b_tbl text, b_geom text, relation text) |
| Topological Relationship (near) | ast_isTopologicalRelationshipValid(a_tbl text, a_geom text, b_tbl text, b_geom text, dist real) |
| Arc-Node Network | ast_isNetworkValid(arc_tbl text, arc_geom text, node_tbl text, node_geom text) |
| Arc-Arc Network | ast_isNetworkValid(arc_tbl text, arc_geom text) |
| Spatial Aggregation | ast_isSpatialAggregationValid(part_tbl text, part_geom text, whole_tbl text, whole_geom text) |
This section shows an use case example (also available in the examples folder) intended to clarify the use of this extension.
The following figure shows a schema fragment for a bus transportation network (nodes at bus stops and unidirectional arcs corresponding to route segments) that serves a set of school districts. A conventional class holds the attributes for the bus line. The schema embeds spatial integrity constraints for (1) the network relationship (each route segment must be related to two bus stops), (2) a “contains” relationship (school district cannot exists without a bus stop), and (3) the geometry of route segments and school districts (lines and polygons must be simple, i.e., with no self-intersections).
The implementation of this schema that uses the ast_postgis extension and considers all the spatial constraints is as follows:
create table bus_line (
line_number integer primary key,
description varchar(50),
operator varchar(50)
);
create table school_district (
district_name varchar(50) primary key,
school_capacity integer,
geom ast_polygon
);
create table bus_stop (
stop_id integer primary key,
shelter_type varchar(50),
geom ast_point
);
create table bus_route_segment (
traverse_time real,
segment_number integer,
busline integer references bus_line (line_number),
geom ast_uniline
);
-- school_district and bus_stop topological relationship constraints:
create trigger school_district_contains_trigger
after insert or update on school_district
for each statement
execute procedure ast_spatialrelationship('school_district', 'geom', 'bus_stop', 'geom', 'contains');
-- bus_route_segment and bus_stop arc-node network constraints:
create trigger busroute_insert_update_trigger
after insert or update on bus_route_segment
for each statement
execute procedure ast_arcnodenetwork('bus_route_segment', 'geom', 'bus_stop', 'geom');
AST-PostGIS is released under a MIT license.
Copyright (c) 2016 Luís Eduardo Oliveira Lizardo.
[1] K. A. V. Borges, C. A. Davis Jr., and A. H. F. Laender. OMT-G: an object-oriented data model for geographic applications. GeoInformatica, 5(3):221–260, 2001.
[2] L. E. O. Lizardo and C. A. Davis Jr. OMT-G Designer: a web tool for modeling geographic databases in OMT-G. In Advances in Conceptual Modeling: 33rd International Conference on Conceptual Modeling, ER 2014, volume 8823 of Lecture Notes in Computer Science, pages 228–233. Springer International Publishing, 2014.
[3] Lizardo, L. E. O., & Davis Jr, C. A. (2017, November). A PostGIS extension to support advanced spatial data types and integrity constraints. In Proceedings of the 25th ACM SIGSPATIAL International Conference on Advances in Geographic Information Systems (pp. 1-10).