<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.17108" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=029165811-11042012><FONT face=Arial 
color=#0000ff size=2>Hi,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029165811-11042012><FONT face=Arial 
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029165811-11042012><FONT face=Arial 
color=#0000ff size=2>A good start is to check what has been already done in the 
domain of gazetteers (<A 
href="http://en.wikipedia.org/wiki/Gazetteer">http://en.wikipedia.org/wiki/Gazetteer</A>).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029165811-11042012><FONT face=Arial 
color=#0000ff size=2>You will probably find across the list some data and 
schemas (with hierachical structure) that should fit your 
needs.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=029165811-11042012></SPAN><SPAN 
class=029165811-11042012><FONT face=Arial color=#0000ff 
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=029165811-11042012><FONT face=Arial 
color=#0000ff size=2>Marc-André Morin</FONT></SPAN></DIV>
<DIV><SPAN class=029165811-11042012><FONT face=Arial color=#0000ff 
size=2> </FONT></SPAN></DIV>
<DIV><SPAN class=029165811-11042012> </SPAN></DIV>
<DIV>
<HR tabIndex=-1>
</DIV>
<DIV><FONT face=Tahoma size=2><B>De :</B> 
postgis-users-bounces@postgis.refractions.net 
[mailto:postgis-users-bounces@postgis.refractions.net] <B>De la part de</B> 
pcreso@pcreso.com<BR><B>Envoyé :</B> 10 avril 2012 02:22<BR><B>À :</B> 
Michal Kubenka<BR><B>Cc :</B> 
postgis-users@postgis.refractions.net<BR><B>Objet :</B> Re: [postgis-users] 
How to design a database for continents,countries, regions, cities and 
POIs?<BR></FONT><BR></DIV>
<DIV></DIV>
<TABLE cellSpacing=0 cellPadding=0 border=0>
  <TBODY>
  <TR>
    <TD vAlign=top>Hi Michal, <BR><BR>One suggestion...<BR><BR>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:<BR><BR>select 
      * from polygons a, polygons b<BR>where a.type = 'city' <BR>and 
      b.type='country'<BR>and b.name='Italy'; <BR><BR>While flexible & 
      effective, relying on spatial queries for quick searches with polygons 
      with many thousands, or even millions of records may not be 
      ideal.<BR><BR>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:<BR><BR>select * from polygons<BR>where type=city<BR>and parent_id 
      = (select id from 
      polygons<BR>                               
      where type = 
      'country'<BR>                               
      and name = 'Italy');<BR><BR>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. <BR><BR>If 
      you store the heirarchies as predefined levels then a heirarchical search 
      using the recursive "with" capability- see:<BR><A 
      href="http://www.postgresql.org/docs/8.4/static/queries-with.html">http://www.postgresql.org/docs/8.4/static/queries-with.html</A><BR>is 
      perhaps possible, to invoke searches up & down the tree. <BR><BR>So 
      use Postgis to determine the parent id using a spatial function, then 
      store this as an indexed id.<BR><BR>HTH,<BR><BR>  Brent 
      Wood<BR><BR><BR>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.<BR><BR><BR>--- On <B>Tue, 4/10/12, 
      Michal Kubenka <I><mkubenka@gmail.com></I></B> wrote:<BR>
      <BLOCKQUOTE 
      style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: rgb(16,16,255) 2px solid"><BR>From: 
        Michal Kubenka <mkubenka@gmail.com><BR>Subject: Re: 
        [postgis-users] How to design a database for continents, countries, 
        regions, cities and POIs?<BR>To: pcreso@pcreso.com<BR>Cc: "PostGIS Users 
        Discussion" <postgis-users@postgis.refractions.net><BR>Date: 
        Tuesday, April 10, 2012, 9:59 AM<BR><BR>
        <DIV id=yiv117983933>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.
        <DIV><BR></DIV>
        <DIV>That's why I would choose two tables:</DIV>
        <DIV><BR></DIV>
        <DIV>1) `polygons` - which can store countries, regions, 
        sub-regions, provinces etc.</DIV>
        <DIV>2) `points` - which can store cities and POIs</DIV>
        <DIV><BR></DIV>
        <DIV>Thanks.</DIV>
        <DIV><BR></DIV>
        <DIV>Michal K. </DIV><BR>
        <DIV class=yiv117983933gmail_quote>On Mon, Apr 9, 2012 at 8:11 PM, <SPAN 
        dir=ltr><<A href="/mc/compose?to=pcreso@pcreso.com" target=_blank 
        rel=nofollow 
        ymailto="mailto:pcreso@pcreso.com">pcreso@pcreso.com</A>></SPAN> 
        wrote:<BR>
        <BLOCKQUOTE class=yiv117983933gmail_quote 
        style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
          <TABLE cellSpacing=0 cellPadding=0 border=0>
            <TBODY>
            <TR>
              <TD vAlign=top>Are you planning to store multiple versions of 
                these polygons, for zoom layers?<BR><BR>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.<BR><BR>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. <BR><BR>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. <BR><BR>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. <BR><BR><A 
                href="http://booki.flossmanuals.net/openstreetmap/_draft/_v/1.0/the-osm-data-model/" 
                target=_blank 
                rel=nofollow>http://booki.flossmanuals.net/openstreetmap/_draft/_v/1.0/the-osm-data-model/</A><BR><BR>--- 
                On <B>Mon, 4/9/12, mkubenka <I><<A 
                href="/mc/compose?to=mkubenka@gmail.com" target=_blank 
                rel=nofollow 
                ymailto="mailto:mkubenka@gmail.com">mkubenka@gmail.com</A>></I></B> 
                wrote:<BR>
                <BLOCKQUOTE 
                style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: rgb(16,16,255) 2px solid"><BR>From: 
                  mkubenka <<A href="/mc/compose?to=mkubenka@gmail.com" 
                  target=_blank rel=nofollow 
                  ymailto="mailto:mkubenka@gmail.com">mkubenka@gmail.com</A>><BR>Subject: 
                  [postgis-users] How to design a database for continents, 
                  countries, regions, cities and POIs?<BR>To: <A 
                  href="/mc/compose?to=postgis-users@postgis.refractions.net" 
                  target=_blank rel=nofollow 
                  ymailto="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>Date: 
                  Monday, April 9, 2012, 11:31 PM
                  <DIV>
                  <DIV class=yiv117983933h5><BR><BR>
                  <DIV>I'm brand new to GIS programming and I am designing a GIS 
                  application. Target<BR>is to create system with continents, 
                  countries, regions (including states,<BR>sub-regions, 
                  provinces), cities and places in cities. Each of this 
                  elements<BR>will contain some text information and related 
                  stuff. As database we are<BR>going to use PostgreSQL with 
                  PostGIS.<BR><BR>My question is how to design database for this 
                  system? I was thinking of 2<BR>tables polygons and points, but 
                  I'm not sure if it's good way of thinking.<BR><BR>--<BR>View 
                  this message in context: <A 
                  href="http://postgis.17.n6.nabble.com/How-to-design-a-database-for-continents-countries-regions-cities-and-POIs-tp4715669p4715669.html" 
                  target=_blank 
                  rel=nofollow>http://postgis.17.n6.nabble.com/How-to-design-a-database-for-continents-countries-regions-cities-and-POIs-tp4715669p4715669.html</A><BR>Sent 
                  from the PostGIS - User mailing list archive at 
                  Nabble.com.<BR>_______________________________________________<BR>postgis-users 
                  mailing list<BR><A 
                  href="http://mc/compose?to=postgis-users@postgis.refractions.net" 
                  target=_blank 
                  rel=nofollow>postgis-users@postgis.refractions.net</A><BR><A 
                  href="http://postgis.refractions.net/mailman/listinfo/postgis-users" 
                  target=_blank 
                  rel=nofollow>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></DIV></DIV></DIV></BLOCKQUOTE></TD></TR></TBODY></TABLE></BLOCKQUOTE></DIV><BR></DIV></BLOCKQUOTE></TD></TR></TBODY></TABLE></BODY></HTML>