Postgresql – How to design a database for continents, countries, regions, cities and POIs

database-designpostgresqlspatial

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

Best Answer

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

Related Question