Table design for spatio-temporal data

I am interested in best practices for designing relational DB tables for holding spatio-temporal data. Specifically data that will be kept in such tables are custom geometries that have certain validity开发者_如何转开发 period, geometry definition as well as hierarchical aspect(certain geometries will be children of other geometries).

I curious if someone can point me to a good material on this subject or could suggest specific implementation.

Thank you.

I'd use PostGIS (http://postgis.refractions.net/) for geometry type and make a table like this:

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

PostGIS can make spatial queries, so you can query database for all geometries in a specific geometry (e.g. query for all geometries in a geometry representing a State, or a County).

To get the validity period, you should add to this query additional condition for getting only the rows where (valid_from >= now() and valid_till <= now()).

You will also need indexes on all three columns of course. On the geometry column there should be a spatial index.

All the information about the spatial queries and geometry type and geometry indexes you can find on the PostGIS site.