I work with American Community Survey data. ACS is now releasing new demographic data annually, and a new set of TIGER/Line files to go with it. But the vast majority of the geographies (states, counties, tracts, ZIP codes) do not change annually, and larger geographies (states and counties) are stable for decades.

In order to conserve server space, I'm thinking about trying to store these geometries with effective dates (start and end years), and then querying by the desired year. There is a shapefile like this for international boundaries created for the CShapes R package (http://nils.weidmann.ws/projects/cshapes). Polygons representing country boundaries over different years all overlap in the shapefile, but to get the countries for a specific year you just have to query:

WHERE 2010 BETWEEN cowsyear AND coweyear

where the fields cowsyear and coweyear represent the start and end years for each country.

This doesn't seem like it would be that difficult to implement in PostGIS, using a base table with all times and views that select time slices. I would also need to input all geometries for each new year and test for equality to eliminate redundant geometries and alter the time bounds. My question is whether anyone has done this and has recommendations for how to proceed or any code I can steal.

  • Can PostGIS be used to record boundary movement over time as events? It would be a tiny bit like having your shapefile created on the fly from the result of whatever changes to the area enclosing your area of inquiry as it happened over time. A lot of the completed shapes would be left on tap for easier access. All the shapefiles use points, and these points would move with wars, treaties, catastrophes, terraforming, changes in zoning or other categories applied to the place, etc. I have no earthly idea how to start thinking about this with the addition of dynamic segmentation or linear refere
    – Solstice
    May 25 '18 at 15:33
  • This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
    – neogeomat
    May 25 '18 at 17:23
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review
    – BERA
    May 25 '18 at 18:44

Your plan is fine. A traditional way to handing the "end date" is to leave it NULL, so every geometry has a start and end, and those that have been superseded have a non-NULL end point. Here's a very simple temporal model.


If you're going to be doing a lot of temporal querying, looking into the RANGE type in PostgreSQL is highly recommended. It's optimized for better index performance and has a number of useful query facilities to allow you to manipulate time (and other) ranges. It's very cool all around.

About the only tricky thing you're going to have to deal with is the annual update, figuring out what has changed and so on, but as long as the providers are always editing an existing copy, just doing relatively simple spatial comparisons should be sufficient.

A classic spatial similarity test for polygons is the ratio

ST_Area(ST_Intersection(A, B)) / ST_Area(ST_Union(A, B))

The closer it is to 1, the more similar the two polygons are.

  • Thanks, Paul. Is the similarity test more efficient than ST_Equals or ST_OrderingEquals? Since minor changes to the Census geographies probably are significant, I probably need to test for spatial equality or even exact equality, rather than just similarity. Dec 4 '13 at 23:53
  • If you want an exact test, go with ST_Equals. Dec 5 '13 at 5:28

Rather than think of each time-slice as a separate, complete actual layer of polygons, think of them as "virtual" layers. Instead of having the geometry as part of each region (being duplicated period after period)

id, name, beg_date, end_date, geometry, stat_1, stat_2, ... stat_M

factor out the gemetries into their own table and keep only references to them in each region, allowing many-to-one relationships.

id, name, beg_date, end_date, poly_id, stat_1, stat_2, ... stat_M

id, geometry

Spatial queries would require join between regions and polygons.

Let's call it a temporal-topology solution.

Ultimately, an efficient (but complicated) solution would combine spatial and temporal topology methodologies!

  • This seems like a logical method to me. This would allow you to "split" geographies (like tracts) over time and allow different polygons to belong to multiple years. Down the road you may find that a single tract will actually be the combination of 3 or more individual polygons, all linked by a single "region" id. Then you could just dissolve (using ST_Union...see page 21) the source polygons into distinct tracts by year. Dec 4 '13 at 19:11

I've not used them but I think you should think about using the topological capabilities of PostGIS if you want to eliminate redundant geometries. For a single time-slice, the non-topological method of PostGIS, with independent polygons, has redundant geometries. Adding multiple time-slices seems to increase the argument in favor of a topological, store-each-boundary-once structure.

  • Yes but without the time bounds, I would still need to store the entire topological dataset for each year of interest. In theory this could save me up to half of the storage (but with entries in node tables, arc tables, and polygon tables, savings would be somewhat less than half), but what I'm talking about is having 10 years worth of boundary files with extremely few changes, so if I store it once the storage savings would be 90%. Dec 4 '13 at 8:48
  • I was trying to be too ambitious without enough details. Because I don't know any details of the PostGIS topology methodology, I'll provide a non-spatial-topology suggestion as a separate answer.
    – Martin F
    Dec 4 '13 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.