I'm brand new to GIS programming and I am designing a GIS application. Target is to create system with continents, countries, regions (including states, sub-regions, provinces), cities and places in cities. Each of this elements will contain some text information and related stuff. As database we are going to use PostgreSQL with PostGIS.
My question is how to design database for this system? I was thinking of 2 tables polygons and points, but I'm not sure if it's good way of thinking.
What we need is some hierarchical base for relationship between countries, cities, regions, etc.
Main goal of the application will be collecting tourist data from many sources about specific cities, regions, countries and so on, and store it in database. Let's say we will have city Rome, we collect some info about this city into database from couple sources. And we need to know that Rome is in province Rome, sub-region Lazio in region Lazio, country Italy. So system should be flexible to allow create such relation from real world.
Best Answer
Michal, Based on the additional information in your comment, there are a few things you could do. One thing to remember is that flexibility is key. If you put all of your polygon features in one table and all of your point features in another table, it makes it very difficult to compare different features. For example, if you have a polygon layer that contains both countries and counties within each country, it then becomes a challenge, and performance drag, to select all of the counties within a particular country. You are trying to run a query all on one layer, which doesn't take advantage of the flexibility of a relational database. It would be much better to let each type of feature reside in its own layer, as @mhoran_psprep said in his answer.
Also, since you have the flexibility to design the database, it might be worth having multiple geometry types for certain data types. For example, having a city represented as a polygon would be useful to perform spatial queries to determine all points of interest within that city. At the same time, it is oftentimes more desirable to represent a city as a point on a map. The database allows you to do this by separating the attribute data from the features into their own table, and linking them together with a Primary Key/Foreign Key structure. Briefly, a Primary Key is an identifier for a record in a table. A Foreign Key is a reference to that same identifier in a different table, thus linking them together.
Here is the structure I would use, part of which you actually described above.
Table
Continent - Fields - Geom (Polygon), ID, Name, Etc
Country - Fields - Geom (Polygon), ID, Name, Addtnl Attributes
Region - If there are different levels of regions within a country, then I would have them as different layers. So, if a country has regions called districts, and a number of districts combine to form a state, which then combine to form the country, you would have a layer for each type.
Reg_State - Fields - Geom (Polygon), ID (Primary Key), Name, Country ID, Etc
Reg_District - Fields - Geom (Polygon), ID, Name, Reg_State ID (Foreign Key), Etc
Cities - Have a table that contains attributes for each city, but no geometry. Have geometry tables with different geometries, like point or polygon, that link to the main city table.
City_Info - Fields - CityID (Primary key), Name, Population, Reg_District ID (Foreign Key), Etc, Etc
City_Pt - Fields - Geom (Point), ID, Name, CityID (Foreign Key)
City_Poly - Fields - Geom (Polygon), ID, Name, CityID (Foreign Key)
You can take these layers down to as fine a detail as you feel is necessary within the city, using neighborhoods, or addressing regions, whatever makes the most sense. Then, since this is for tourist information, you may want to include layers that contain points of interest.
PointofInterest - Fields - Geom (Point), ID, Name, CityID (Foreign Key), Description, Etc
The next set of tables will cover the tourist information that you are collecting. Again, this will depend on the amount and type of data. If you expect to have only a few entries about any particular regional level, it may make sense to have an overall table with fields to enter a region key, or country, or city. For example:
Tourist_Info - Fields - ID, Description, Country ID (Foreign Key), Reg_State ID (Foreign Key), Reg_District ID (Foreign Key), CityID (Foreign Key), Other_Info, Etc Etc
On the other hand, if you expect to have a large number of records for each region type, then it makes sense to have individual tourist info tables for each region. For example:
Tourist_Info_Country - Fields - ID, Description, Country ID (Foreign Key), Type, Etc, Etc
Tourist_Info_City - Fields - ID, Description, CityID (Foreign Key), Type, Etc, Etc
This is only one way of setting up the database, again, it depends on your specific needs for being able to link different pieces of data together. Once the table structure is determined, the next part will be to set up queries to retrieve the information you want, for inclusion in a report or some sort of viewing application.
I hope this provides at least an idea of where to start. Good luck!