Spatial Time Table Design

Question

I'm interested in best practices for creating relational database tables for storing spatio-temporal data. In particular, the data that will be stored in such tables are custom geometries that have a specific validity period, a geometry definition, as well as a hierarchical aspect (some geometries will be children of other geometries).

I am wondering if anyone can point me to some good material on this, or can suggest a specific implementation.

Thanks.

Answer 1

I would use PostGIS ( http://postgis.refractions.net/ ) for the geometry type and make the table as follows:

CREATE TABLE data ( geometry geometry, valid_from timestamp, valid_till timestamp, check(valid_till >= valid_from) ); 

PostGIS can create spatial queries, so you can query the database for all geometries in a specific geometry (for example, a query for all geometries in a geometry representing a state or county).

To get the validity period, you must add additional conditions to this request to get only rows where (valid_from >= now() and valid_till <= now()) .

You will also need indexes on all three columns. The geometry column must have a spatial index.

All information about spatial queries and geometry types and geometries that you can find on the PostGIS website.