[postgis-users] PostGIS-PostgreSQL

Tue Jul 14 02:44:43 PDT 2009

I must disagree with Chris on this.

As far as I'm concerned, one of the big steps forward in spatial data management using OGC or SQL/MM capable RDBMS's is that users do not suffer the artificial constraint of only allowing one geometry per entity foisted on us by an an outdated and invalid GIS data management model.

I'm using PostGIS to manage scientific marine data. Transects for data capture have a start time & end time as well as a start point & end point. They may also have a trackline between these and a polygon representing the swept area of the sampling gear on the seabed. These may be required in multiple coordinate systems. Some of these can be implemented as views, thus a start point could be implemented as a virtual column by a query on the trackline, but whether implemented as real or virtual tables, they very usefully have multiple geometries. Some entities have both vessel positions and gear positions.

It is archaic GIS practice to restrict the number of geometry columns to one, not good RDBMS design. The fact that many GIS systems have problems with datasets where features have more than one geometric representation is a failing which you should not allow to restrict your use of a spatially enabled RDBMS. 

Regarding Chris's comment that it makes sense that a real world object represented as a row in a database should only ever have one geometry, I suggest this is a limitation of one's understanding of the real world.

It makes no more sense to restrict the attributes of an entity (instantiated as columns of a table) to only one geometry per entity than to allow only one int, date or time column. A real world object may have several time attributes, several string attributes and several geometry attributes (quite distinct from a multi feature type). Good database design should reflect this, and good database capabilities should enable & support this facility.

However, Chris's comments on roads being stored as features with a unique database key linked to names, rather than using names as a primary key, or directly in the road table, are well reasoned and well expressed :-) 

Cheers,

  Brent Wood

--- On Tue, 7/14/09, Chris Hermansen <chris.hermansen at timberline.ca> wrote:

> From: Chris Hermansen <chris.hermansen at timberline.ca>
> Subject: Re: [postgis-users] PostGIS-PostgreSQL
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Date: Tuesday, July 14, 2009, 3:03 AM
> I should have mentioned that it's not
> considered good practice to have more than one geometry
> column per table, and hence more than one geometry object
> per row. This makes sense if you think of rows as
> representational instances of real world objects.
> Chris Hermansen        chris.hermansen at timberline.ca
> tel+1.604.714.2878 · fax+1.604.733.0631 ·
> mob+1.778.840.4625
> Timberline Natural Resource Group · www.timberline.ca
> 401 · 958 West 8th Avenue  · Vancouver BC · Canada
> · V5Z 1E5
> 
> 
> -----Original Message-----
> From: Nenad Milasinovic <nenad.milasinovic at zesium.com>
> 
> Date: Mon, 13 Jul 2009 07:43:17 
> To: <postgis-users at postgis.refractions.net>
> Subject: Re: [postgis-users] PostGIS-PostgreSQL
> 
> 
> 
> Thanks Chris,
> 
> However maybe better question will be can spatial and
> non-spatial data be
> saved in one PostgreSQL table?
> Precisely, is it possible to have table with some spatial
> columns (geometry
> columns) and with some 
> ordinary columns (non-spatial) and is it good practice?
> From your answer i can conclude that it is possible to have
> table without
> spatial data that refers to some spatial table through
> foreign key?
> 
> 
> Chris Hermansen wrote:
> > 
> > This is a part of good database design. Street name,
> which can be changed,
> > is only stored in one location.
> > 
> > This is part of "normalization".  This practice
> relates to any / all
> > databases 
> > ------Original Message------
> > From: Nenad Milasinovic
> > Sender: postgis-users-bounces at postgis.refractions.net
> > To: postgis-users at postgis.refractions.net
> > ReplyTo: PostGIS Users Discussion
> > Subject: [postgis-users] PostGIS-PostgreSQL
> > Sent: Jul 13, 2009 06:18
> > 
> > Hello,
> > 
> > I have some questions relating to PostGIS and
> PostgreSQL.
> > I am interested how GEO-data from PostGIS are
> connected to data in 
> > PostgreSQL.
> > Say that we have vector layer of all city streets in
> PostGIS table, with 
> > some street attributes (e.g. street name).
> > Say also that we have all data about locations in
> PostgreSQL table. That 
> > data contains information about location street also.
> > What i want to accomplish is that when user change
> street name is 
> > PostGIS table, that street name should automatically
> be changed in
> > all locations which belongs to that street. I am
> interested is there any 
> > connection between tables in PostGIS and PostgreSQL
> > that could automatize this process, or i need to
> update all locations 
> > with new street data by myself.
> > 
> > Thanks.
> > 
> > 
> > 
> > 
> > -- 
> > Nenad Milasinovic
> > Software Development and Testing
> > 
> > ---
> > 
> > "ZESIUM mobile" d.o.o.
> > Valentina Vodnika 8/9
> > 21000 Novi Sad
> > Serbia
> > Tel: +381 (0)21 472 15 48
> > Fax: +381 (0)21 472 15 49
> > Mob: +381 (0)61 231 41 20
> > E-mail: nenad.milasinovic at zesium.com
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> > 
> > Chris Hermansen        chris.hermansen at timberline.ca
> > tel+1.604.714.2878 · fax+1.604.733.0631 ·
> mob+1.778.840.4625
> > Timberline Natural Resource Group ·
> www.timberline.ca
> > 401 · 958 West 8th Avenue  · Vancouver BC ·
> Canada · V5Z 1E5
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> > 
> 
> -- 
> View this message in context: http://www.nabble.com/PostGIS-PostgreSQL-tp24461543p24463072.html
> Sent from the PostGIS - User mailing list archive at
> Nabble.com.
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 