5

Is it a good design idea to maintain separate tables for each layer in a PostGIS database?. Every layer is defined by users. A user creates his/her layer say ATM location. That's meant by defined by users (I don't have control over the table definition of the layers much) This makes it impossible to predict fields(attribute fields and type are not known initially) used in the table/layers. School vs. roads have different geometry and set of attributes.

The problem is traditional databases won't advise having dynamic tables very much. Does the same apply to spatial databases?

  • 1
    As far as spatial database design goes, it is a very good idea to follow design principles for relational databases in general. However, your question is unclear: what do you mean by "predict fields" and what do you mean by "defined by users"? – whuber Jan 4 '13 at 19:10
  • i had edited the question to clear your doubts – rkm Jan 5 '13 at 1:18
5

It is generally advisable to seperate layers by tables. It sounds like you have a need for a dynamic schema, you may want to look at Postgres's hstore extension, it can let you set up a key->value pair field that may suit your needs.

| improve this answer | |
0

It is not a good decission, tables should represent static entities inside your application that will remain the same across different environments (Think local, staging, development, production).

Like HeyOverThere points out, what you need is a dynamic schema.

I implemented a similar system, a user can create multiple layers so I have two entities:

Layer, which belongs to an user and has a schema

Feature, which belongs to a layer, and has a set of HStore key/values

When you retrieve the properties (key/values) of a feature you must first check the layer schema to take out only the relevant keys according to the defined schema in the Layer entity.

| improve this answer | |

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.