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.
[email protected] 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 <[email protected]> wrote:
From: Chris Hermansen <[email protected]>
Subject: Re: [postgis-users] PostGIS-PostgreSQL
To: "PostGIS Users Discussion" <[email protected]>
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 [email protected]
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 <[email protected]>
Date: Mon, 13 Jul 2009 07:43:17
To: <[email protected]>
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: [email protected]
To: [email protected]
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: [email protected]
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Chris Hermansen [email protected]
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
[email protected]
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Regards, (please note new mobile number below)
Chris Hermansen mailto:[email protected]
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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
