[postgis-users] PostGIS-PostgreSQL

Tue Jul 14 12:00:48 PDT 2009

Brent makes some interesting points.  My fault for being terse and 
thereby inviting him to take me too literally.

Forcing a user to restrict geometry columns to one per table is of 
course not a good thing, and may well be an example of archaic GIS 
practice or perhaps even just a coping mechanism for software limitations.

However, if I understand Brent's specific example correctly, it seems 
that it might be offering an example of bad database design, from a 
normalization point of view.

If a line (multiline) is already stored, then the start and end points 
are contained in that line and a function call will retrieve them; as 
Brent says, this can be encapsulated in a view for query software that 
doesn't know how to do the function calls itself.  However, physically 
storing the start and end points and also storing the line (or 
multiline) joining them is an example of functional dependency between 
columns in tables that creates unnecessary opportunities for error, and 
this is bad database design.

I can imagine a hypothetical example of some kind of composite geometric 
object that requires, say, two physical geometries; maybe a polygon and 
a collection of points (think of, for example, a land use polygon and a 
collection of points that at which that polygon's characteristics were 
sampled).

In such cases, it seems obvious that there is some kind of semantic 
relationship between those two geometries that is not inherent in their 
definition.  That is, if a specific polygon instance is updated, must 
its sample points be updated as well?  Are its sample points invalidated 
by the polygon update?  If the polygon disappears for some reason, 
should the sample points disappear as well, or should they continue to 
have some kind of life of their own?

Ignoring this extra semantic meaning, how to store this data 
physically?  As a single table whose rows contain a polygon column and a 
point collection column?  Or as two tables, one with the polygons, one 
with the sample points?

Personally, in this theoretical example, I would lean to the two 
separate tables.  The two table approach might be more compact if only a 
small proportion of polygons are sampled.  Smaller row sizes hint at 
better cache utilization.  It might be the case that having the points 
remain around even if the polygon were to be deleted would be a useful 
feature, and somehow I find that more compelling than a row with a null 
in the polygon geometry column.  It might be the case that a two-table 
design would more helpfully suggest that points could be re-used to 
estimate characteristics of any polygons that overlap them, not just the 
ones to which they are attached.

But, on the balance, I don't feel strongly about this and I certainly 
would not criticize someone who came to the opposite decision.

In the end, we really should allow for this composite geometric object, 
as Brent says.  But we should also consider whether the circumstances 
favour a single, or two (or more) separate, tables.

We should also at least be uncomfortable when our data model uses / 
abuses this facility to store unnecessary / dangerous multiple copies of 
data that are meant to be consistent, instead of using method calls and 
/ or views to display the component information.

pcreso at pcreso.com wrote:
> 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
>>
>>     
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   

-- 
Regards,  (please note new mobile number below)

Chris Hermansen         mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5