1

Lets use the Canadian census geography design as an example:

census geography

In my experience, a spatially enabled database for census geography can be created in two ways:

  1. Each geography contains a foreign key of the first order geography above it (FK-Province in EconomicRegion table must exist in Province table; FK-CensusDivision in CensusConsolidatedSubdivision must exist in CensusDivision table)
  2. Each geography contains a foreign key of every geography above it (FK-Province will be in every geography below it... e.g. FK-Provinces in DisseminationBlock must exist in Province table)

This is likely a battle between storage space and computation time:

  • Storing all related/nested geography FKs in every table increases database size
  • Storing all related/nested geography FKs allows us to extract data without having to create many joins

One could argue that PK/FK relationships are nonsensical and one should rely on spatial joins instead of creating mundane long-winded join statements, or redundantly storing FKs all over the place.

Can anyone shine any light as to how geography of this nature would be stored in a spatially enabled database?

3

Firstly, I want to clear up the fact that using proper Foreign keys doesn't impact database size. I know that you haven't said that in your question, but I still want to make that point.

If we are talking about database size, this has an negligible effect on the database size. The table structure and metadata is orders of magnitude size smaller than the contents of the table. It might have an impact on the processing speed, while doing inserts, but even that is worth it, for the data integrity that you get.

Once of the main tenets of Database design is that each piece of information should be stored in only one place in the database. Practically this would mean that you only store in each feature the foreign key of only the geography above it (i.e. The States would have only the Foreign key of the country; The Counties will have only the Foreign Key of the States, And the Cities will have only the Foreign key of the County and so on)

This has the benefit that the information in not repeated. There is one authoritative representation of the fact of which city lies in which state. Using an Hierarchical query you can find this information. Tomorrow if one of the state is divided into two states, you would only need to add the new state in the states table, and change the appropriate foreign keys in the County table. The Cities will automatically fall into the new states. This way your table is free from an update anomaly.

If Each geography contains a foreign key of every geography above it, then you will run into problems, because you will have to change this information in all lower tables. From a Database point of view, your table is not in third normal form.

You also say

One could argue that PK/FK relationships are nonsensical and one should rely on spatial joins instead of creating mundane long-winded join statements, or redundantly storing FKs all over the place

I'll like to stop you from doing this blunder by point out the huge problems with this.

  • Firstly Spatial operation are computationally much more intensive than simple PK-FK relationships. Databases have a lot of tricks to manage data when you have Relationships setup, and you should take advantage of those optimizations, instead of doing something that would grind your Database Server to a halt when you do a simple query.
  • Secondly you might run into problems of accuracy and scale. (for example, can you be certain about the location of village on the border? Are you certain that the Accuracy of the Countries border is enough at that scale?)

You raise an important point when you say that

Storing all related/nested geography FKs allows us to extract data without having to create many joins.

I agree that not having to do too many joins is a good thing, but there are other ways of achieving this.

What I have said so far, is about the storage of the data, and not the way in which it is presented to the user i.e its Display & Visualization.

You could have views, which give you the required information, and you could query against the views. You could also make materialized views which cache this information, if your data doesn't change much.

This was all in an ideal, theoretical situation. In most practical databases, the databases are almost always never fully normalized. This can be due to several reasons.

  • Firstly the real world situation might not be logical enough to make an 100% accurate model.
  • Secondly the trade off between normalization and performance might not be worth it. This has given rise to the adage: normalize until it hurts, de-normalize until it works.
  • And finally, and most relevant to us is that the Database and middleware might not be amiable to proper normalization (I'm talking of things like ArcSDE)

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.