How do I design a database for continents, countries, regions, cities and POIs?

Question

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 polygons and 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:

1. polygons - which can store countries, regions, sub-regions, provinces etc.
2. points - which can store cities and POIs

Answer 1

i suggest 2 options

Option 1: if you want a RBDMs, i think 2 tables will be sufficient

Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)

in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and maintenance is easier, then 5 separate tables

the other table is the table to collect the data

Table 2 (Master)
-Tourist
-LocationID
-ExtraFields
-...

so in this table you collect info about tourists and all you have to do is store the locationid, which is your granularity, this way the Locationid, has the Landmark,city,region,country,continent Since the Landmark is definetly in a city and the city is in a country and the country is in a region.. (no need for 5 tables to keep them separate, there will be a big chance of invalid inputs or data if not maintained properly)

---

Option 2:

i suggest graph database, check this post at SO , about using Neo4J for GIS and an implementation of GIS by Neo4J

Answer 2

How about 5 tables with a foreign key to each child:

continents, countries, regions, cities, landmarks.

That or one self-referencing table with a type column.

Answer 3

Late answer, but...

Assuming you can find the data for the geographic themes you've mentioned, why not store each geography in their own database table?

Cities: points
Landmarks: points
Regions: polygons
Countries: polygons
Continents: polygons

Then you can simply use spatial intersects in PostGIS (ST_Intersect) to give the correct attributes to the points based on the polygons they intersect.

You can create a view of the points that contains a calculated column for 'region' (or whatever polygon data you want attached to the point) which calls the ST_Intersect function on a join to the region (or country or continent) data...

Maybe something like:

 create view PointsWithGeography as (

select cityPoints.name

,countryPolys.name

from cityPoints join countryPolys 

on st_intersects(citiesPoints.geom, countryPolys.geom)

        )

Then for your reporting, etc. just use the view!

So your points contain the attribute information which is dynamically generated from the spatial information based on that join / st_intersects...you can even nest the 'spatial joins' and call as many columns from the intersecting polygon layers as you like...all dynamic! And each of the 5 tables can be edited / maintained separately...