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:

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?

  • This is an especially bad data schema: what you want is something hierarchical, and what you've got is crap: the Martin-Bulwers plot is a descendant of Martin Land, and Bulwers Land. It's especially hard to infer that because you have nothing that designates which nodes are root plots. Are the root plots all 2000-01-01? In your example above, how do we infer that Coveral Land is separate from all the other sets? Because it both COMES BEFORE, and has a ID AFTER? Jan 17 '17 at 19:44
  • That still doesn't make sense. How come Tom Land has a 86004 and not 86005 (greater than or equal to it's ancestors) and Mart-Bulwers has 86002 while it's two ancestors have 86001 and 86002. How do you link Tom Land to Small Tom Area when Small Tom Area has a newer id, but an older date? Jan 17 '17 at 20:09

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.

  • Thanks @MatheusOI ! I have not enough time to test your suggestions right now. I keep your post and come back to comment them as soon as I can.
    – wiltomap
    Sep 9 '16 at 6:53
  • This is perfect! I did not know infinity which is a very good option here.
    – wiltomap
    Sep 22 '16 at 14:16

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!


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:
  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.

  • Fisrt of all, thank you much for your answer! The problem is that I don't really understand your proposal. Martin-Bulwers and Tom Land don't have any code in your result set? They do have one, I have no choice about that as it is administrative changes I have to follow. Then, Martin-Bulwers has 86002 and Tom Land has 86004. How could I select the areas for a given year?
    – wiltomap
    Jan 18 '17 at 9:33
  • @wiltomap look forward to answering that question, and clearing this up but first I have to know it's possible and I'm not yet sure it is. My question on Tom Land must be answered above. My answer speaks specifically to your problem the other answer is generic. This problem specifically can't be solved as far as I can see with either answer. Which is convenient for the chosen answer which chose to abstract the problem. Jan 18 '17 at 18:18
  • .@Evan Carrol: thanks for being so dedicated to this post! Tom Land is the result of Big Tom (86004) and Small Tom (86005) areas merge. The administration decided to keep one of the parent codes for the new merged one (86004). I have no choice but taking it into account... So, I think this has to be treated without relying on gid or code. I guessed that relying on change dates only would allow to list the different areas of a given year. By the way, the solution is not necessarily a single table. Have another read to my initial post again, I can hardly make it more clear.
    – wiltomap
    Jan 19 '17 at 8:32
  • If we're not relying on GID or Code, the question then remains how do we differentiate between Coveral Land and Big Tom both of which have the same date? How do we know Coveral Land is not also in Tom Land? Jan 19 '17 at 8:34
  • They don't have the same code... :-) I mean that in the query, I need to list distinct codes for a given year (I obviously rely on codes to identify the areas). If one code is showing on multiple rows, then the stored dates should be different in order to know if the area existed on a given year or not.
    – wiltomap
    Jan 19 '17 at 8:35

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.