Storing historical records and extracting data from a given period

Question

This a question about database modeling and PostgreSQL. This post partially answers the question but I need advice on more technical stuff: columns and query to extract the data.

I need to store administrative boundaries history within a given territory in order to be able to track boundaries movements back over the passed years.

Here is an example on how boundaries move:

• Martin Land area has always existed
• Bulwers Land area has always existed too
• Those areas were merged together in 2015:
  • The new area was renamed as Martin-Bulwers
  • The new area kept Bulwers Land code (Martin Land code becoming deprecated)

Here is the way I store the data:

gid  | code   | name            | change_date
-----+--------+-----------------+-------------
1    | 86001  | Martin Land     | 2000-01-01
2    | 86002  | Bulwers Land    | 2000-01-01
3    | 86002  | Martin-Bulwers  | 2015-01-01

I have set a default change_date to 2000-01-01 for historical situation of an area, i.e. before the first changement occurs.

Then, I have many different cases: areas that have remained unmerged, areas that have merged with others at different dates. This can produce the following example:

CREATE TEMPORARY TABLE foo AS
SELECT gid,code,area,change_date::date FROM ( VALUES
  ( 1,86001,'Martin Land'   ,'2000-01-01' ),
  ( 2,86002,'Bulwers Land'  ,'2000-01-01' ),
  ( 3,86002,'Martin-Bulwers','2015-01-01' ),
  ( 4,86003,'Coveral Land'  ,'2000-01-01' ),
  ( 5,86004,'Big Tom Area'  ,'2000-01-01' ),
  ( 6,86005,'Small Tom Area','2000-01-01' ),
  ( 7,86004,'Tom Land'      ,'2016-01-01' )
) AS t(gid,code,area,change_date);

Then I have difficulties writing a query that could return the list of areas of a given year. I tried to rely on DISTINCT ON() clause but this does not the job I need...

For instance, the following query will return the following table:

SELECT DISTINCT ON (code) code, area, change_date

  FROM myTable WHERE change_date < '2016-01-01'

  ORDER BY code, change_date DESC ;

-- Result:

code   | area            | change_date
-------+-----------------+-------------
86001  | Martin Land     | 2000-01-01
86002  | Martin-Bulwers  | 2015-01-01
86003  | Coveral Land    | 2000-01-01
86004  | Big Tom Area    | 2000-01-01
86005  | Small Tom Area  | 2000-01-01

This is partially right as before 2016-01-01, Big Tom Area and Small Tom Area had not merged together but Martin Land and Bulwers Land had! There were 5 areas in 2014, then 4 areas in 2015 and 3 in 2016.

In fact, I would need the following result:

code   | area            | change_date
-------+-----------------+-------------
86002  | Martin-Bulwers  | 2015-01-01
86003  | Coveral Land    | 2000-01-01
86004  | Big Tom Area    | 2000-01-01
86005  | Small Tom Area  | 2000-01-01

Is there another way to store these informations or to write the query?

Answer 1

The problem is that when an area is merged and its code is reused, you have that information, but when an area is merged and its code is deprecated, you don't have it. In other words, you don't have the information that an area is deprecated.

Now, I see two solutions. First, keep the schemas as is and whenever an area is deprecated, add a new row as it was a new area and just states it is deprecated, e.g.

INSERT INTO myTable(code, area, change_date)
VALUES(86001, 'deprecated', '2015-01-01')

So in your query it will show as 'deprecated' or you filter it out.

A second, and possible best option, is to have a new column that states when (and if) an area was deprecated:

ALTER TABLE myTable ADD deprecated DATE;
UPDATE myTable SET deprecated = '2015-01-01' WHERE gid = 1;

So, on your query you can simple add the filter:

(deprecated IS NULL OR deprecated >= '2016-01-01')

The full code:

SELECT DISTINCT ON (code) code, area, change_date
FROM myTable
WHERE change_date < '2016-01-01'
    AND (deprecated IS NULL OR deprecated >= '2016-01-01')
ORDER BY code, change_date DESC;

As another option for non-deprecated areas, you can set them as 'infinity' instead of NULL, so the query does not need the deprecated IS NULL condition, as 'infinity' will always be higher or equal than any other non-null value.

Answer 2

Here is a solution I finally adopted and that prove to be right enough for my needs and that avoid using infinity date statement.

 gid  | code | name            | start_date | end_date   | parent
 -----+------+-----------------+------------+------------+-----------
 1    | 86001| Martin Land     | 2000-01-01 | 2014-12-31 |
 2    | 86002| Bulwers Land    | 2000-01-01 | 2014-12-31 |
 3    | 86002| Martin-Bulwers  | 2015-01-01 | 2100-01-01 | {86001,86002}

I added a parent column to store the codes of former areas that merged to produce the new area. I also use a start_date and end_date columns.

Then, the following queries enable to select the areas of a given year:

-- Year 2015
SELECT * FROM table WHERE start_date <= '2015-01-01' AND end_date >= '2015-01-01' ;

-- Result:

 gid  | code | name            | start_date | end_date   | parent
 -----+------+-----------------+------------+------------+-----------
 3    | 86002| Martin-Bulwers  | 2015-01-01 | 2100-01-01 | {86001,86002}


-- Year 2012
SELECT * FROM table WHERE start_date <= '2012-01-01' AND end_date >= '2012-01-01' ;

-- Result:

 gid  | code | name            | start_date | end_date   | parent
 -----+------+-----------------+------------+------------+-----------
 1    | 86001| Martin Land     | 2000-01-01 | 2014-12-31 |
 2    | 86002| Bulwers Land    | 2000-01-01 | 2014-12-31 |

Of course, I set a start_date equal to 2000-01-01 because I know that no areas merged before 2000. I can then consider that the origin of my areas story is year 2000. The other thing is about end_date that I set to 2100-01-01 to avoid using infinity.

This is certainly not the best option (sorry @Evan Carroll!) but it does the job perfectly for me and I don't have enough time to deeply re-organize my data (unfortunately...).

Anyway, thanks to all for help provided!

Answer 3

I'm going to suggest that you clean up the schema. What you want is something like this..

WITH RECURSIVE t(gid, code, area, change_date, parent, depth) AS (                                                                    SELECT gid, code, area, change_date, null::int, 0                                                                                   FROM foo                                                                                                                            WHERE change_date = '2000-01-01'::date                                                                                                                                                                                                                                  UNION ALL                                                                                                                         

  SELECT null, null, foo.area, foo.change_date, t.code, depth+1                                                                     
  FROM t                                                                                                                            
  JOIN foo ON (                                                                                                                     
    (                                                                                                                               
      ( foo.code BETWEEN t.code AND t.code+1 )                                                                                      
      AND foo.gid != t.gid                                                                                                          
    )                                                                                                                               
    AND foo.change_date >= t.change_date                                                                                            
    AND foo.change_date != '2000-01-01'                                                                                             
  )                                                                                                                                 
)                                                                                                                                   
SELECT * FROM t AS t1;  

Run that query, and you'll see a result set like this...

 gid | code  |      area      | change_date | parent | depth 
-----+-------+----------------+-------------+--------+-------
   1 | 86001 | Martin Land    | 2000-01-01  |        |     0
   2 | 86002 | Bulwers Land   | 2000-01-01  |        |     0
   4 | 86003 | Coveral Land   | 2000-01-01  |        |     0
   5 | 86004 | Big Tom Area   | 2000-01-01  |        |     0
   6 | 86005 | Small Tom Area | 2000-01-01  |        |     0
     |       | Martin-Bulwers | 2015-01-01  |  86001 |     1
     |       | Martin-Bulwers | 2015-01-01  |  86002 |     1
     |       | Tom Land       | 2016-01-01  |  86003 |     1
     |       | Tom Land       | 2016-01-01  |  86004 |     1

Simply add the WHERE clause to get what you want (or a better version).

Not there are some problems with this method and I believe them to be inherent in your data. Tom Land has code less than that of Small Tom Area and a gid one higher than Coveral Land with a newer date.

Here, I'm assuming that you're change_date of 2000-01-01 is a result of likely having a date-column that can't accept nulls. What I would do is update the table and

1. Store the parent id.

2. Run this

   UPDATE foo
   SET date = NULL
   WHERE dates = 2000-01-01
   

I would do this because

1. infinity is a bad idea:
   • It has no relation to the data.
   • It has limited third party support. I have retrospection.
   • It stored as a special-cased HIGHEST date under the hood
2. Your schema is less useful
   • You're describing your lower bounds AND your upper bounds with nonsense to ease querying.
   • You're still not storing parent even though this inherently a logical problem.

If you need you can turn the Recursive CTE into a view which is all the more better.

I think you need to make a better schema anyway, but that infinity is not the answer here. You need hierarchy.