3

I'm currently educating myself on spatial databases and have downloaded lots of open data from a regional authority.

I know the technicalities on how to get that data into my new PostGIS database, however I'm not sure on how it should actually be stored.

Let's say that the data I've downloaded is split into three categories: boundaries, reporting of crime incidents and planning application boundaries for new residential development.

Now should I be storing this information in separate databases?

Or different schemas in the same database?

Or should I even just store all the data in the one schema knowing that I'll have more categories in the future?

What is the best way of organising and managing spatial databases?

Improve this question
  • I'd go with "different schemas in the same database". But I close-voted because this is very opinion-based and also we don't have enough information (e.g. what you want to do with the data) to give a good answer. There is no such thing as "best method of organising". The answer will always be "it depends"... – pLumo Feb 1 '18 at 8:12
  • 3
    @RoVo / Polygeo could we make this a community post? I've recently been trying to get my head around the same issue & it's difficult to find answers/guidelines in help files. RoVo says the answer "depends". Can we have a post stating suggested practices based on the different scenarios this could depend on? i.e. per project, per user group etc.. The answer from Matee is already a useful post, but more details/examples would be great. Maybe also links to online documentation. I think it is a question a lot of people who are switching from desktop GIS and shp files/fgdb's to PostGIS/SDE have. – Dan_h_b Feb 1 '18 at 9:56
  • 2
    One thing to be aware of is that it is hard to query outside of the same database -- there is dblink and foreign data wrappers, but, they will only utilize an index if it is a built-in Postgres type, which spatial is not. So, if you want to do any spatial joins, same db. Using different schema is a good idea, though, so you don't mix system data/functions with your own. – John Powell Feb 1 '18 at 12:19
1

Depends on your later usecase, as always. It is also a classic topic that is discussed by all general database literature and has nothing to do with spatial databases. Things to consider are normalization, privileges, indices, foreign keys, constraints and stuff.

But there are two things to consider for the given example as it sounds that the db is just used for local data storage.

Try to not store data in different databases that are somehow related. Selections across multiple databases is possible but not as fast as within the same db. Also the selections are more complicated. Second thing is that you have to use seperate database connections in any program you want to use this data. That is not very user friendly.

Schemas are also used to seperate data that usally is not close related or to manage privileges and different versions. Most databases I know also store everything in public as some software does have problems with other schemas. In honor of Vince's comment I have to admit that this is not a good practice but an often used "quick and dirty" solution. If your project should become more than a local storage avoid this laziness.

Set all privileges in the beginning whenever the database should become more than a local solution on your machine. Keep in mind that the "public" schema and user is special in Postgresql and the it has its own privileges. Read the chapter in the documentation. Also set all table and column names to lower case letters. Some software does not handle upper case well as they do not use the selections properly. ("public"."Test" is antoher table as public.Test)

Improve this answer
  • 1
    Tossing everything in public is an anti-pattern. Folks do it, but it is not part of recommended procedure. – Vince Feb 1 '18 at 11:38
  • 1
    True, but it will be fine for a simple local data storage (as the questions suggests). If anyone starts to get problems because of creating own functions or only partialliy switching PostGIS versions the knowledge for a good db setup should be around. I do not prefer a "database lite" solution but its better than a bunch of files in different formats. – Matte Feb 1 '18 at 13:04
  • And yet the question asked for best practice, and you don't mention that using public is worst practice. Whether it's better than using non-database datafiles is a matter of opinion. Incompetent database administration is a threat to data and site security. – Vince Feb 1 '18 at 13:10

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