Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
1  
I think it would be prudent to ask for more detail on what your application is going to be for. You state below that your main goal is mining data for different regions. This is a bit ambiguous as it could be for "data mining", which could encompass a variety of different data types, and thus possible structure needs, or "mining data", related to pulling minerals, etc. from the ground, which is a whole different set of needs. What is your end-use for this data, as that should guide the storage structure? –  Get Spatial Apr 9 '12 at 17:33
    
@Russell Actually 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. –  Michal Kubenka Apr 9 '12 at 22:02
add comment

2 Answers

up vote 4 down vote accepted

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!

share|improve this answer
add comment

While it may be tempting to put all polygons in one table, you will have to think about what the data will look like and how it will be used.

  • Will the fields for continents be the same for cities? They will be if they are in the same table.
  • If the user will be trying to pull all cities in states/provinces with a poverty level greater then X, and in countries with monarchies, can you do it if they are all in the same table?

What will be the source for the polygon data? Can they be merged when they need to be?

share|improve this answer
    
In our model we can consider city as point not as polygon, so it won't be in same table. Our main goal is mining data about elements (from continents to POIs), but we need have it in some hierarchical tree which can handle most common situations of organization. –  Michal Kubenka Apr 9 '12 at 15:42
add comment

Your Answer

 
discard

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.