4

We have more than 700 shapefiles which we will import in a PostgreSQL/PostGIS database. We want to use a single database to make joins on our tables without using foreign database wrapper. Our data guy uses directories to classify shapefiles for different theme (water, energy...) so he is not comfortable with the idea of having all data in one database.

In PostgreSQL the only way I see is to use schemas but we'll have only one level of depth.

How do you handle so many tables in one database without losing too much time looking for it in your everyday routine? Do you know a tool to manage a catalog of tables in a "directory/file structure" way? How would you change your workflow from a lot of shapefiles to one big database?

Improve this question
3
  • 1
    You question isn't totally clear, what do you mean by "losing too much time". What do your shape files represent? If they are from a similar coverage, you could theoretically put them all in one table and differentiate between them with some attribute column. There are tools, such as shp2pgsql that you can very easily script to load arbitrary numbers of shapes into Postgres. But you probably need to add more information to get a better answer.
    – John Powell
    Commented Feb 18, 2015 at 13:57
  • 1
    What John said: ie, explain why your imported shape-files need to be kept in different organizing structures, rather than in a single scheme or even single table.
    – Martin F
    Commented Feb 18, 2015 at 17:20
  • 1
    You'd be interested to know that PostGIS was created by consultants for situations similar to yours: where there were hundreds of shapefiles.
    – Mike T
    Commented Feb 19, 2015 at 10:49

1 Answer 1

Reset to default
2

Here's one approach to designing your database tables:

Wherever you have shapes that have exactly the same combination of geometry type and set of attributes, put them in one single table. Probably, this will be based on those themes you mention. If that still appears to be too many tables, maybe think about combining those themes that are very similar (re. geometry and attributes) into one table and using an additional "type" field to distinguish among them.

It's difficult to go into much more detail without knowing further details of your situation.

Another thought: If your "data guy" has a hard time thinking beyond directories of files, maybe he should do further research/training on database concepts.

Improve this answer

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.