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

Tue Apr 10 05:33:28 PDT 2012

Hi Brent,

thanks for your suggestions. I think that second option can fit
our requirements, and it's similar to my first idea how to organize it.

Thank you again.

Michal Kubenka

On Tue, Apr 10, 2012 at 8:22 AM, <pcreso at pcreso.com> wrote:

> Hi Michal,
>
> One suggestion...
>
> There are two ways (at least :-) to do this in a RDBMS. You can have the
> spatial relationship implicit in the feature geometries, so a spatial query
> is used, for example, to determine the cities within a country:
>
> select * from polygons a, polygons b
> where a.type = 'city'
> and b.type='country'
> and b.name='Italy';
>
> While flexible & effective, relying on spatial queries for quick searches
> with polygons with many thousands, or even millions of records may not be
> ideal.
>
> The other approach is to explicitly predefine these relationships, so a
> column for each polygon feature stores the parent id. Simplistically
> assuming the "parent" of a city is the country containing it, rather than
> navigating the hierarchy, the above query becomes:
>
> select * from polygons
> where type=city
> and parent_id = (select id from polygons
>                                where type = 'country'
>                                and name = 'Italy');
>
> Even with both structures optimised & indexed, the latter is likely to be
> much faster. No join is required. Given the country containing a city is a
> pretty static relationship, I suggest predefining to optimise query
> performance makes sense.
>
> If you store the heirarchies as predefined levels then a heirarchical
> search using the recursive "with" capability- see:
> http://www.postgresql.org/docs/8.4/static/queries-with.html
> is perhaps possible, to invoke searches up & down the tree.
>
> So use Postgis to determine the parent id using a spatial function, then
> store this as an indexed id.
>
> HTH,
>
>   Brent Wood
>
>
> I'd say there are several approaches you could take to build a viable
> database, the optimal one is defined by your use case: the sorts of queries
> you want to apply.
>
>
> --- On *Tue, 4/10/12, Michal Kubenka <mkubenka at gmail.com>* wrote:
>
>
> From: Michal Kubenka <mkubenka at gmail.com>
> Subject: Re: [postgis-users] How to design a database for continents,
> countries, regions, cities and POIs?
> To: pcreso at pcreso.com
> Cc: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Date: Tuesday, April 10, 2012, 9:59 AM
>
>
> 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<http://mc/compose?to=pcreso@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<http://mc/compose?to=mkubenka@gmail.com>
> >* wrote:
>
>
> From: mkubenka <mkubenka at gmail.com<http://mc/compose?to=mkubenka@gmail.com>
> >
> Subject: [postgis-users] How to design a database for continents,
> countries, regions, cities and POIs?
> To: postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@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/20120410/b39da974/attachment.html>