Hi Brent,<div><br></div><div>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.</div><div><br></div><div>Thank you again.</div><div><br>
</div><div>Michal Kubenka<br><br><div class="gmail_quote">On Tue, Apr 10, 2012 at 8:22 AM, <span dir="ltr"><<a href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<table cellspacing="0" cellpadding="0" border="0"><tbody><tr><td valign="top" style="font:inherit">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 <a href="http://b.name" target="_blank">b.name</a>='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" target="_blank">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><<a href="mailto:mkubenka@gmail.com" target="_blank">mkubenka@gmail.com</a>></i></b> wrote:<br><blockquote style="border-left:2px solid rgb(16,16,255);margin-left:5px;padding-left:5px">
<br>From: Michal Kubenka <<a href="mailto:mkubenka@gmail.com" target="_blank">mkubenka@gmail.com</a>><br>Subject: Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?<br>
To: <a href="mailto:pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a><br>Cc: "PostGIS Users Discussion"
<<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>><br>Date: Tuesday, April 10, 2012, 9:59 AM<div><div class="h5"><br><br><div>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>On Mon, Apr 9, 2012 at 8:11 PM, <span dir="ltr"><<a rel="nofollow" href="http://mc/compose?to=pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a>></span> wrote:<br>
<blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font:inherit" 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 rel="nofollow" href="http://booki.flossmanuals.net/openstreetmap/_draft/_v/1.0/the-osm-data-model/" target="_blank">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 rel="nofollow" href="http://mc/compose?to=mkubenka@gmail.com" target="_blank">mkubenka@gmail.com</a>></i></b> wrote:<br>
<blockquote style="border-left:2px solid rgb(16,16,255);margin-left:5px;padding-left:5px"><br>From: mkubenka <<a rel="nofollow" href="http://mc/compose?to=mkubenka@gmail.com" target="_blank">mkubenka@gmail.com</a>><br>
Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?<br>
To: <a rel="nofollow" href="http://mc/compose?to=postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>Date: Monday, April 9, 2012, 11:31 PM<div><div><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 rel="nofollow" href="http://postgis.17.n6.nabble.com/How-to-design-a-database-for-continents-countries-regions-cities-and-POIs-tp4715669p4715669.html" target="_blank">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 rel="nofollow" href="http://mc/compose?to=postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a rel="nofollow" href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></div></blockquote></td></tr></tbody></table></blockquote></div><br>
</div></div></div></blockquote></td></tr></tbody></table></blockquote></div><br></div>