Is good database design less important for spatial databases?

Question

I have a strong feeling that database design and normalisation often comes in second hand when dealing with spatial data.

With software costing a fortune and databases with over 100 fields tables I have to ask:

Is there good reasons for taking other considerations than normalisation when designing a spatial database?

I guess people will ask for examples, but that I can not give here, so my question is maybe more aimed for those who means that 100 fields is no problem and easier to maintain than a proper normalized design.

What are the arguments?

Answer 1

I feel that spatial databases should be treated no differently to traditional databases. They are essentially doing the same thing, storing large amounts of data for fast retrieval. As an example, in PostgreSQL / PostGIS, the geometry is just another datatype. Just like text, or integer. Same in SQL Server 2008. Same in Oracle. If the "spatial" part is just another field type in the database, then is it really that different from the original database? Does this mean we should throw out all the rules of traditional database design?

Obviously normalization can be taken too far, just as with traditional databases, so it's a trade off to find the best design that suits your needs.

If you are planning on created a highly de-normalized structure with tables of 100 columns, then you have to ask yourself what is likely to change in the future? With a vast increase in rows, is this also going to affect querying performance? Is this going to affect maintainability in the future?

What's wrong with created a normalized structure and using views to expose all the data to the database client, be it GIS, or any other client?

All of these questions apply to both traditional databases and spatial databases. If you go through http://en.wikipedia.org/wiki/Database_normalization you will find that it applies to spatial databases as well.

If the software you are using on top of the database is forcing you to use highly de-normalized structures, then this is a different argument. You are constrained by the software and not the database, so you have no choices in the best database design.

So I think, the short answer is (in my opinion) database design is just as important with spatial databases as with traditional databases.

Answer 2

I see this a lot. I feel that it stems from the fact that traditionally GIS people come from surveying backgrounds, and don't have a background/understanding of databases. I am seeing this change though, as more and more organisations move the GIS infrastructure into the IT fold.

Answer 3

GIS Software Legacy

The previous high cost of ArcSDE and lack of a spatial datatype in SQL Server (until 2008), and Oracle until version 10, meant there was little choice but to store data in shapefiles for many organisations (and by tenderers to keep bid costs down).

The introduction of native spatial types in SQL Server meant almost instantly that ArcSDE went from a huge investment, to being included for free in ArcGIS, and the "bringing in to the fold" of spatial data in organisations.

Organisations using ArcGIS and SQL Server previously had three choices:

1. Pay the 20k+ fee to purchase ArcSDE and store spatial data in "proper" SQL Server databases.
2. Store spatial data in shapefiles / personal GDBs, and link to the rest of the organisational data in databases (or export these attributes to DBFs)
3. Switch GIS vendors and store spatial data in a single database but in a format only accessible by the new GIS software

Once SQL Server had a native spatial type most vendors used this instead of their proprietary formats, meaning spatial data could suddenly be accessed by other applications. ESRI had to either reduce the cost of ArcSDE (which they did by integrating it into ArcGIS) and/or allow spatial data to be stored in the native database format.

In addition queries performed in ArcIMS on shapefiles meant associated with DBFs had to include all required fields and duplication as there was no option to create spatial views, or easily linking features with a back end database.

Organisational Reasons

I agree with others that that until recently spatial data became a native database type it has long been ignored or kept separate by database administrators in organisations, and become the reponsibility of a GIS manager. The concepts of database design, normalisation, replication, security, and SQL views require an often very different, and specialised skillset and cannot easily be learned as you go along.

Cost Reasons

Explaining in a tender the requirement of large amount of time and effort to be spent on a data model, and cleaning / importing data into this model is often impossible. Often the project purchasers are coming from an analytical view of GIS and overlook the importance of structured data.

Answer 4

By 100-column-tables, I assume you mean the kinds of outputs you get from building "master coverage" overlays of multiple inputs. Yes, these are artifacts of the Arc/INFO workflow. But, in defence, you can also think of them as being deliberately de-normalized tables for OLAP. Since they are being used largely for query processing, not for data update, the de-normalized form makes some sense. Like a star schema, but without the, er, points. OK, weak tea, but still I think there's something there.

Answer 5

Yes, if starting a new GI project design is important and can save time=money in the future. http://www.amazon.com/Spatial-Database-Systems-Implementation-Management/dp/1402053916 is a good overview for why it is important.