[postgis-users] How to design a database for continents, countries, regions, cities and POIs?

[Michal Kubenka]

Actually what we need is some hierarchical base for relationship between
countries, cities, regions, etc. Main goal of the application will be
collecting 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.

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

Thanks.

Michal K.

On Mon, Apr 9, 2012 at 8:11 PM, <pcreso at pcreso.com> wrote:

> Are you planning to store multiple versions of these polygons, for zoom
> layers?
>
> Generally you need a high res version (eg: coastline) when zoomed in
> (large scale) and a lower resolution version when zoomed out (you can't see
> & don't need the detail.
>
> This may or may not have an impact on your eventual data model, but it is
> worth ensuring you take this into account during the data modeling process.
> You can have a model where each feature has multiple geometry columns
> associated with it in the one table, or an approach which has the
> geometries in separate tables, using ID's to link to the aspatial
> attributes. The former is a simpler, monolithic solution, the latter is
> more complex but allows more use of tablespaces & underlying Postgres
> optimisation.
>
> You may also find you need to carry out joins (identify relationships
> between types of polygon, eg: cities within counties within states within
> countries, and this may perform better with a denormalised structure with
> separate tables for different categories of polygon.
>
> One example you might look at is the OSM data model. Not quite what you
> are describing, but a robust & well tested model for global roads & related
> spatial data, which does not use Postgis at all.
>
>
> http://booki.flossmanuals.net/openstreetmap/_draft/_v/1.0/the-osm-data-model/
>
> --- On *Mon, 4/9/12, mkubenka <mkubenka at gmail.com>* wrote:
>
>
> From: mkubenka <mkubenka at gmail.com>
> Subject: [postgis-users] How to design a database for continents,
> countries, regions, cities and POIs?
> To: postgis-users at postgis.refractions.net
> Date: Monday, April 9, 2012, 11:31 PM
>
>
> 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.
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/How-to-design-a-database-for-continents-countries-regions-cities-and-POIs-tp4715669p4715669.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120409/604691c5/attachment.html>