Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
Thanks for the quick answer. Just read your writeup on the subject [1]. This looks like a _very nice_ addition and should be extremely helpful. I've got some experimenting to do! -=- Jerry [1] http://strk.keybit.net/projects/postgis/Paris2011_TopologyWithPostGIS_2_0.pdf In otherwords, you skip On Apr 11, 2012, at 11:51 AM, Sandro Santilli wrote: > On Wed, Apr 11, 2012 at 11:47:02AM -0400, Jerry Carter wrote: > >> I might have a geometry for a country and separate geometries for the >> next layer of administrative regions (e.g. states in the United States). >> But there are often minor differences in the polygons such that the >> polygon for the country is nearly but not exactly the union of the states. >> How is this handled? > > In the topology model your higher layer will be defined by composition > of lower layer items. So you would only say that "United States" is > formed by all the countries. And for each country you would only list > the counties they are formed of. And for each county the parcels. > And for each parcel the primitive faces. And each face is defined by > its edges. > > The only actual geometries involved in all of the above would be > the edge geometries. Not any other geometry. So there's no difference > because primitive elements are singletons. > > --strk; > > ,--o-. > | __/ |Delivering high quality PostGIS 2.0 ! > | / 2.0 |http://strk.keybit.net - http://vizzuality.com > `-o--' > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
On Wed, Apr 11, 2012 at 11:47:02AM -0400, Jerry Carter wrote: > I might have a geometry for a country and separate geometries for the > next layer of administrative regions (e.g. states in the United States). > But there are often minor differences in the polygons such that the > polygon for the country is nearly but not exactly the union of the states. > How is this handled? In the topology model your higher layer will be defined by composition of lower layer items. So you would only say that "United States" is formed by all the countries. And for each country you would only list the counties they are formed of. And for each county the parcels. And for each parcel the primitive faces. And each face is defined by its edges. The only actual geometries involved in all of the above would be the edge geometries. Not any other geometry. So there's no difference because primitive elements are singletons. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
Question out ignorance because I was not aware that this capability existed. I might have a geometry for a country and separate geometries for the next layer of administrative regions (e.g. states in the United States). But there are often minor differences in the polygons such that the polygon for the country is nearly but not exactly the union of the states. How is this handled? Thanks. -=- Jerry On Apr 11, 2012, at 11:42 AM, Michal Kubenka wrote: > Thank you. > > On Wed, Apr 11, 2012 at 2:29 PM, Sandro Santilli wrote: > On Mon, Apr 09, 2012 at 11:59:14PM +0200, Michal Kubenka wrote: > > Actually what we need is some hierarchical base for relationship between > > countries, cities, regions, etc. > > PostGIS topology supports hirearchical modeling of layers, with each object > in upper layer defined by items of the lower layer, down to primitives. > > --strk; > > ,--o-. > | __/ |Delivering high quality PostGIS 2.0 ! > | / 2.0 |http://strk.keybit.net - http://vizzuality.com > `-o--' > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
Thank you. On Wed, Apr 11, 2012 at 2:29 PM, Sandro Santilli wrote: > On Mon, Apr 09, 2012 at 11:59:14PM +0200, Michal Kubenka wrote: > > Actually what we need is some hierarchical base for relationship between > > countries, cities, regions, etc. > > PostGIS topology supports hirearchical modeling of layers, with each object > in upper layer defined by items of the lower layer, down to primitives. > > --strk; > > ,--o-. > | __/ |Delivering high quality PostGIS 2.0 ! > | / 2.0 |http://strk.keybit.net - http://vizzuality.com > `-o--' > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
On Mon, Apr 09, 2012 at 11:59:14PM +0200, Michal Kubenka wrote: > Actually what we need is some hierarchical base for relationship between > countries, cities, regions, etc. PostGIS topology supports hirearchical modeling of layers, with each object in upper layer defined by items of the lower layer, down to primitives. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
Hi,
A good start is to check what has been already done in the domain of gazetteers
(http://en.wikipedia.org/wiki/Gazetteer).
You will probably find across the list some data and schemas (with hierachical
structure) that should fit your needs.
Marc-André Morin
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de
pcr...@pcreso.com
Envoyé : 10 avril 2012 02:22
À : Michal Kubenka
Cc : postgis-users@postgis.refractions.net
Objet : Re: [postgis-users] How to design a database for continents,countries,
regions, cities and POIs?
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 wrote:
From: Michal Kubenka
Subject: Re: [postgis-users] How to design a database for continents,
countries, regions, cities and POIs?
To: pcr...@pcreso.com
Cc: "PostGIS Users Discussion"
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, 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/
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
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, 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 * wrote: > > > From: Michal Kubenka > Subject: Re: [postgis-users] How to design a database for continents, > countries, regions, cities and POIs? > To: pcr...@pcreso.com > Cc: "PostGIS Users Discussion" > 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, > http://mc/compose?to=pcr...@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
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
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 wrote: From: Michal Kubenka Subject: Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs? To: pcr...@pcreso.com Cc: "PostGIS Users Discussion" 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, 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 wrote: From: mkubenka Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs? 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
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
All, You probably want to add in lines as well, for doing buffered searches from along Railraod routes for example. bobb >>> Michal Kubenka wrote: 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, 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 wrote: From: mkubenka Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs? 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@postgis.refractions.net ( http://mc/compose?to=postgis-users@postgis.refractions.net ) http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
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, 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 * wrote: > > > From: mkubenka > Subject: [postgis-users] How to design a database for continents, > countries, regions, cities and POIs? > 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@postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net> > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
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 6:53 PM, Andrea Peri wrote: > >Thank's for your reply. > > > >I think we'll go same ways as you describe - use two main tables `polygons` > >and `points` and couple supporting tables which handle relationships > >and additional content. > > > >Michal K. > > > Hi, > If you plan to use two tables of kind polygons and point, and don't plan > to use an explicit line table, > I guess you should use the new postgis topology feature. > > You should create a topology (node,edge,face) and apply a TopoGeometry > over that topology. > Where the topogeometry should be the "polygons" you will use in your main > table. > > This approach is really important because otherwise the polygons you will > use in the main table don't be really topology to each-other. > > Please don't understimate the important of postgis topology. > Expecially when you plan to describe temathics informations like city > boundaries, countries and so on. > The simple relationships between polygons is not sufficient to grant a > really exact separation between two countries :) > When we don't use topology we found often some little area where there is > two or more cities :) > > -- > - > Andrea Peri > . . . . . . . . . > qwerty àèìòù > - > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
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 wrote: From: mkubenka Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs? 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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to design a database for continents, countries, regions, cities and POIs?
>Thank's for your reply. > >I think we'll go same ways as you describe - use two main tables `polygons` >and `points` and couple supporting tables which handle relationships >and additional content. > >Michal K. Hi, If you plan to use two tables of kind polygons and point, and don't plan to use an explicit line table, I guess you should use the new postgis topology feature. You should create a topology (node,edge,face) and apply a TopoGeometry over that topology. Where the topogeometry should be the "polygons" you will use in your main table. This approach is really important because otherwise the polygons you will use in the main table don't be really topology to each-other. Please don't understimate the important of postgis topology. Expecially when you plan to describe temathics informations like city boundaries, countries and so on. The simple relationships between polygons is not sufficient to grant a really exact separation between two countries :) When we don't use topology we found often some little area where there is two or more cities :) -- - Andrea Peri . . . . . . . . . qwerty àèìòù - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
Thank's for your reply. I think we'll go same ways as you describe - use two main tables `polygons` and `points` and couple supporting tables which handle relationships and additional content. Michal K. On Mon, Apr 9, 2012 at 3:05 PM, Andrea Peri wrote: > >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. > > > Hi, > we chhose by desing to use two table for the geometries (lines and > polygons). > And many tables to link the geometries inside these two tables. > The two tables with geometries was called ""elementary_lines" (arcs) and > "soil" (polygons). > > The main rule is that every element of the "elementary lines" is a linear > object and it is also a segment on the boundary of one or two element > polygons of the "soil" . > Another rule is that the "soil" will give a "full cover of the soil". And > every point has one and only one polygon to cover it. > > Every object of the "elementary lines" and of the "soil" as an unique > alphanumeric universal identifier. > It allow to have others table without geometry that allow to have distinct > and specific attributes for every specific kind of object. > > This choice was need to grant a full topology for all the objects of the > db. > > Also we develope a profile of "GML topological" to allow the exchange of > out database. > > -- > - > Andrea Peri > . . . . . . . . . > qwerty àèìòù > - > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
Thank you for reply. But I'm afraid that such solution won't fit our system, because for example there can be situation when POI belong to region. And also I think it can cause some problems with ORM. Michal K. On Mon, Apr 9, 2012 at 2:22 PM, Francois Hugues wrote: > I think it could be better to have a table for each kind of object because > they will have different attributes (for an example, in a matter of scale, > a POI belong to a city, a city belong to a province which is part of a > sub_region which is included into a state and the continent is composed of > several states). > > Hugues > > -Message d'origine- > De : postgis-users-boun...@postgis.refractions.net [mailto: > postgis-users-boun...@postgis.refractions.net] De la part de mkubenka > Envoyé : lundi 9 avril 2012 13:31 > À : postgis-users@postgis.refractions.net > Objet : [postgis-users] How to design a database for continents, > countries, regions, cities and POIs? > > 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to design a database for continents, countries, regions, cities and POIs?
>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. Hi, we chhose by desing to use two table for the geometries (lines and polygons). And many tables to link the geometries inside these two tables. The two tables with geometries was called ""elementary_lines" (arcs) and "soil" (polygons). The main rule is that every element of the "elementary lines" is a linear object and it is also a segment on the boundary of one or two element polygons of the "soil" . Another rule is that the "soil" will give a "full cover of the soil". And every point has one and only one polygon to cover it. Every object of the "elementary lines" and of the "soil" as an unique alphanumeric universal identifier. It allow to have others table without geometry that allow to have distinct and specific attributes for every specific kind of object. This choice was need to grant a full topology for all the objects of the db. Also we develope a profile of "GML topological" to allow the exchange of out database. -- - Andrea Peri . . . . . . . . . qwerty àèìòù - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
I think it could be better to have a table for each kind of object because they will have different attributes (for an example, in a matter of scale, a POI belong to a city, a city belong to a province which is part of a sub_region which is included into a state and the continent is composed of several states). Hugues -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de mkubenka Envoyé : lundi 9 avril 2012 13:31 À : postgis-users@postgis.refractions.net Objet : [postgis-users] How to design a database for continents, countries, regions, cities and POIs? 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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to design a database for continents, countries, regions, cities and POIs?
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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
