Sign up ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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. Thats meant by defined by users(I dont 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 wont advice for having dynamic tables very much. Does the same apply to spatial databases?

share|improve this question
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 – rashad Jan 5 '13 at 1:18

2 Answers 2

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.

share|improve this answer
having separate table for each layer and every user is free to create his/her own unique layers will be a problem in the view or RDBMS. see this -… – rashad Jan 5 '13 at 1:22
What you're trying to do sounds like what Open Street Map does. Check out how their database works and is set up. – HeyOverThere Jan 6 '13 at 15:29

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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