From: Chris Hermansen <[email protected]>
Subject: Re: [postgis-users] several SRID on one table
To: [email protected], "PostGIS Users Discussion" <[email protected]
>
Date: Friday, September 4, 2009, 2:43 AM
Hi Brent;
What you are in effect doing is denormalizing your database
for performance reasons.
In a perfect world, you would only store the initial
information, and use geometric functions to generate the
derived info on the fly. Of course it's not yet a perfect
world... And so it's reasonable to denormalize to improve
performance.
Therefore the objective being to improve performance, the
denormalizations must be done to ensure that.
In some cases, all will be well with everything on one
table; like for instance when the application re-uses more
than one of the stored computed geometries at the same time,
and the cost of "use" is greater than the additional I/O
overhead created by joining multiple separate - perhaps
clustered - tables together as needed.
This would likely depend mostly on the width of the rows in
the single-table approach.
The opposite would be the case if the application only used
one geometry at a time, for example storing the same
geometry in two different projections. In this case, the
performance is better with a separate table for each
geometry (more rows retrieved per physical read).
As Ben points out, this separate table approach, in this
second type of application, adds some clarity as well.
This isn't an issue of whether or not it's good or bad
design to have multiple geometries per row; it's bad design,
but nevertheless acceptable and common practice to
denormalize for performance.
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: [email protected]
Date: Thu, 3 Sep 2009 00:20:02
To: Ben Madin<[email protected]>
Cc: <[email protected]>
Subject: Re: [postgis-users] several SRID on one table
Hi Ben,
I use mutiple geometries in a table not just to have
multiple projections of the same geometry, but in one
example, dealing with fishing trawler paths, the dataset has
(supposedly) a start & finish position (but as lat/lon
numbers- the source database is not spatially enabled), just
as it does a start & finish time.
To say only one position can be stored is as reasonable as
saying only one timestamp can be stored.
I can also generate a trackline between these as an
estimated line traversed, and buffer this by a gear width
(/2) to get an estimated swept area. So with only one
projection, I can have 4 geometries representing the same
entity.
I store the buffer in both lat/lon & a custom equal
area projection. I typically map the lat/lon one, along with
all the other relevant layers, but use the equal area one
for area calculations/spatial analyses.
I agree that the user needs to ensure the two versions are
kept synchronous.
A view with a transform()ed geometry can provide this
capability pretty well, but loses the spatial index
(although I guess you could create an index on the
transformed version - I haven't tried), & transform()
does add noticeable overhead when you have 10s of millions
of polygon geometries.
Cheers,
Brent Wood
--- On Thu, 9/3/09, Ben Madin <[email protected]>
wrote:
From: Ben Madin <[email protected]>
Subject: Re: [postgis-users] several SRID on one
table
To: [email protected],
"PostGIS Users Discussion" <[email protected]>
Date: Thursday, September 3, 2009, 3:40 PM
Arguably the relational
database concept is meant to avoid storing the same
data
more than once, but I have also done this to reduce
overhead
during complex output queries (ie storing a point on
surface
of a polygon instead of calculating it each time).
Ultimately, it worked out slightly faster to have
multiple
smaller tables, as we only wanted one aspect of the
geometry
at any one time. When it came for time for others to
use the
same data, it was also much clearer to them what was
going
on.
It might be a good time to add that storing the
same data in multiple formats requires some method to
ensure
concurrency - if someone updates the column that is
in
WGS84, a trigger to update the other columns would be
essential to avoid returning mixed version
information. This
obviously holds true whether you have multiple columns
in
one table or multiple tables with one column
each.
cheers
Ben
On 02/09/2009, at 3:13 , [email protected]
wrote:
H Steve,
I have had recommendations that this is not good
practice,
but I have done this often myself for various reasons,
with
good success.
As far as I'm concerned, a very useful ability of a
spatially enabled RDBMS is to realise that a geometry
is
only an attribute of an entity, like a date, time,
numeric
or string type. Real world entities can be represented
by
multiple geometries, and have multiple dates, etc,
associated with them, so this is a perfectly good
model, and offers substantial benefits over the
(dated) GIS
model where the geometry is somehow more special than
other
attributes of a feature/entity.
Cheers,
Brent Wood
--- On Wed, 9/2/09, [email protected]
<[email protected]>
wrote:
From: [email protected]
<[email protected]>
Subject:
[postgis-users] several SRID on one table
To: "PostGIS
Users Discussion" <[email protected]>
Date: Wednesday,
September 2, 2009, 2:46 AM
Hello,
We need to use a table
for
several purposes
with different SRID.
Is it a good practice
to
have several
geometry columns on
one table or should we create one table
per SRID?
What are the pros and
cons
of using
several geometry
columns on one table?
thanks
Steve
Steve Toutant, M.
Sc.
Analyste en
géomatique
Secteur environnement
Direction des risques
biologiques, environnementaux et
occupationnels
Institut national de
santé publique du Québec
945, avenue Wolfe
Québec, Qc G1V 5B3
Tél.: (418) 650-5115
#5281
Fax.: (418) 654-3144
[email protected]
http://www.inspq.qc.ca
