[postgis-users] detabase design question

[Brent Wood]

--- Andre Schoonbee <andresch at iway.na> wrote:

> I do not have years of experience and am faced with a challenge:
> My client have lots of vector data. Some are from a few years ago and they
> want to load all data into postgis. The data covering a wide spectrum -
> basically all spatial data for the country. This is census data, regions and
> the subsequent changes to the regions, National rainfall and also regional
> rainfall. Mining, roads and the changing of the roads the past 10 years.
> Boreholes from 10 years ago, and subsequently replaced by pipelines.
> Veterinarian data, etc...
> 
> So some of the data is national data and some is regional data. But the
> regional data is not always related to the current region, because the
> regions have changed in the last couple of years. 
> 
> So my question:  Is there a basic concept design that will cater for these
> kind of scenarios? Any ideas might help


Hi Andre,

It sounds like it can all be done, but is far from trivial. I don't know how
much experience or what skills you have, but here's my 02c.

Good database design is a somewhat arcane art IMO, and to get the sort of data
you describe into suitable structures on your first go could be expecting a bit
much.

The relational model (used by PostGIS) uses database tables to both store the
actual data & also the relationships between the real world entities
represented by the data. As a model of real world entities, if you get it right
it works very well for both representing the real world objects and supporting
queries on these data, otherwise it is an ongoing source of problems.

You'll need to have a good grasp of normalisation for your initial model, then
an idea of what is to be done with the data (how it will be queried) to apply
any de-normalisation required for performance or other reasons. But I suggest
you start with the normalised model (identify the entities, relationships &
attributes), then tweak this to build a suitable structure, then the
implementation.  

Any book on "Beginning database design" will probably help, (try Beginning
Databases with PostgreSQL or Beginning Database Design) and there are some
useful web resources, from basic to advanced, & googling for data modelling or
database design will find others:

http://www.smart-it-consulting.com/database/progress-database-design-guide/
http://en.wikipedia.org/wiki/Database_design
http://databases.about.com/od/specificproducts/Database_Design.htm

Note that the vast majority of these books & sites will cover non-spatial
databases. The principles pretty much also apply to spatial databases, but
there are a few quirks that experience helps with.

Then, apart from the data modelling exercise, there is the Postgres setup; do
you have enough data to justify setting up tablespaces, enough static records
to justify partitioning tables & clustered indices, do have a few concurrent
users each allocated substantial memory, or many users with less, etc. Do you
need to work around hardware limitations, or tweak it to suit specific
applications.

I suggest it will be a challenging learning experience for you, and if a
robust, effective solution is really required, that you get some experienced
help in.   


Luck!!

  Brent Wood