How to design a database for continents, countries, regions, cities and POIs?

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

How to design a database for continents, countries, regions, cities and POIs?

mkubenka
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.
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Hugues François
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 : [hidden email] [mailto:[hidden email]] De la part de mkubenka
Envoyé : lundi 9 avril 2012 13:31
À : [hidden email]
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
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

mkubenka
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 <[hidden email]> 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 : [hidden email] [mailto:[hidden email]] De la part de mkubenka
Envoyé : lundi 9 avril 2012 13:31
À : [hidden email]
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
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Brent Wood
In reply to this post by mkubenka
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 <[hidden email]> wrote:

From: mkubenka <[hidden email]>
Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: [hidden email]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

mkubenka
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, <[hidden email]> 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 <[hidden email]> wrote:

From: mkubenka <[hidden email]>
Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: [hidden email]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Basques, Bob (CI-StPaul)

All,


You probably want to add in lines as well, for doing buffered searches from along Railraod routes for example.


bobb




>>> Michal Kubenka <[hidden email]> 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, <[hidden email]> 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 <[hidden email]> wrote:


From: mkubenka <[hidden email]>
Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: [hidden email]
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Brent Wood
In reply to this post by mkubenka
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 <[hidden email]> wrote:

From: Michal Kubenka <[hidden email]>
Subject: Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: [hidden email]
Cc: "PostGIS Users Discussion" <[hidden email]>
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@...> 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@...> wrote:

From: mkubenka <mkubenka@...>
Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: postgis-users@...
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

mkubenka
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, <[hidden email]> 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 <[hidden email]> wrote:

From: Michal Kubenka <[hidden email]>
Subject: Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: [hidden email]
Cc: "PostGIS Users Discussion" <[hidden email]>
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@...> 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@...> wrote:

From: mkubenka <mkubenka@...>
Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: postgis-users@...
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Morin, Marc-André
In reply to this post by Brent Wood
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 : [hidden email] [mailto:[hidden email]] De la part de [hidden email]
Envoyé : 10 avril 2012 02:22
À : Michal Kubenka
Cc : [hidden email]
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 <[hidden email]> wrote:

From: Michal Kubenka <[hidden email]>
Subject: Re: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: [hidden email]
Cc: "PostGIS Users Discussion" <[hidden email]>
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@...> 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@...> wrote:

From: mkubenka <mkubenka@...>
Subject: [postgis-users] How to design a database for continents, countries, regions, cities and POIs?
To: postgis-users@...
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Sandro Santilli
In reply to this post by mkubenka
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
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

mkubenka
Thank you.

On Wed, Apr 11, 2012 at 2:29 PM, Sandro Santilli <[hidden email]> 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
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Jerry Carter
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 <[hidden email]> 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
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Sandro Santilli
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
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: How to design a database for continents, countries, regions, cities and POIs?

Jerry Carter
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
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users