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?

  • In the case of ArcGIS, a normalized database with referential integrity is hard to accomplish, as you are limited to only the database features exposed to you and supported by ArcGIS. This is very frustrating as a relational database guy... playing a game of telephone, with ArcSDE in the middle.
    – nw1
    Feb 4, 2011 at 18:23

5 Answers 5


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.

  • 1
    +1 for key point of differentiating between software dictating db-structure versus "best" design for the nature of the data. Nov 13, 2010 at 23:00
  • Yes, both this answer and Matt's comment I agree. But what I hope is that someone can explain why this is so often not followed. I'll edit the question a little. Nov 14, 2010 at 10:10
  • I agree. One additional thing I found is that database performance may influence your decision to normalize or not. In some cases I see that two databases are used, one 'master' database containing normalized data, and one secondary database that is used just for display purposes. This one only contains whatever is needed to display (GIS) data, usually in a single table.
    – Berend
    May 28, 2015 at 8:09
  • To expand on Berends point, one of the contributing reasons for this denormalisation is that materialized views are often a bit difficult and DB-specific to implement, so it is normally better to just make your own table/database to store the denormalised data.
    – Alexander
    Mar 10, 2017 at 14:15

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.

  • 1
    this is my feeling too, but I hope in some way that the explanation is more like Pauls discussion, that it is a deliberate choice in some way. that would give more sence to the GIS buissness with so many fancy words, models an" techniques than finding out that the database in the bottom was misused because of ignorance. Nov 14, 2010 at 21:33
  • 1
    sorry, misused is wrong. if it is delibirate with a good reason it is not misuse. Nov 14, 2010 at 21:47

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.

  • I understand and agree with most what you write. But saying the SDE part is given for free after the renaming to ArcGIS server, isn't that like saying: If you buy the bautiful color of this car for 100000 dollars you will get the rest of the car for free. I don't know ArcGIS that well but what is ArcGIS server without the SDE part? and I have never heard anyone say that ArcGIS server is cheap. I don't really see how SQL Server spatial types has affected ArcGIS. But since Arc products is so widely spread I agree that the Arc road has an big inpact on how people think of their spatial data. Nov 15, 2010 at 10:37
  • Before ArcGIS Server, ArcSDE used to be completely separate from ArcMap and ArcIMS and had to be bought and licencsed separately. As ArcSDE was the only way to store spatial data in SQL Server (or Oracle at the time) it meant spatial data was stored elsewhere. Nov 15, 2010 at 11:16
  • ok, ArcIMS in package with SDE is the new consept. Arcmap is still in need of separate licenses per user or floating, right? offtopic, but I am a little curious. Nov 15, 2010 at 11:32
  • No accessing/storing spatial data in a relational database without paying large amounts of extra money was the new concept. esri.com/software/arcgis/arcsde/index.html Nov 15, 2010 at 11:55
  • isn't ArcGIS server large amount of money? As far s I know you can not use sqlserver fomat or postgis format (without ziggis) in arcmap without sde, sorry ArcGIS Server in between. Nov 15, 2010 at 13:20

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.

  • 1
    yes, Paul. I knew there would be some explanation out there including words I don't really understand :-). Very interesting that there is a deliberate history behind this. Great! Nov 14, 2010 at 21:21

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.

  • Mapperz, the question is negated, if it is less important. You mean it is not less important, right? Nov 13, 2010 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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