I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, provinces), cities and places in cities. Each of these elements will contain some text information and related stuff. As database we are going to use PostgreSQL with PostGIS.
My question is, how do I design the database for this system? I was thinking of 2 tables
points, but I'm not sure if it's good way of thinking.
Actually what we need is some hierarchical base for relationships between countries, cities, regions, etc. The 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 have a city Rome; we collect some info about this city into the database from couple sources. And we need to know that Rome is in the province Rome, sub-region Lazio in region Lazio, country Italy. So the system should be flexible to allow us to create such relations from the real world.
That's why I would choose two tables:
polygons- which can store countries, regions, sub-regions, provinces etc.
points- which can store cities and POIs