<!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>