[postgis-users] several SRID on one table
pcreso at pcreso.com
pcreso at pcreso.com
Thu Sep 3 12:05:08 PDT 2009
Hi Chris,
I accept that some of the extra geometries could be construed as denormalising, where the geometries are in fact the same, and we physically store more than one projection.
However I disagree that storing a start & finish point of an event such as these is denormalised, any more than storing separate start & finish times for the event is.
While you may use a trackline between these points to to represent such an
event in a fully denormalised database, I don't believe this is actually a normalising process, as the actual path traversed is often unlikely to have been a straight line, so the line is an estimated path derived from known measurements. Storing the measurements in separate columns as 2 point geometries does not imply any level of denormalisation.
Sure, it is possible to have a single timestamp column, which can store start & finish times, & be joined to the event, just like it is possible to have a single varchar() column to store all string fields such as name, street address, suburb, city, county, state & country, each of which is appropriately flagged & then joined in a self relation to retrieve an address, but this is not generally considered as normalisation, nor is storing strings representing different attributes in different columns regarded as denormalised.
I don't see why a geometry is any different from timestamps or strings in the relational model, such that only one geometry column is possible in a normalised model, but other datatypes can have more than one, when the entity being modelled (in this case an event in time & space) in fact has more than one geometry attribute to store.
I accept this is different from multiple SRIDS, & about multiple geometries...
Regards,
Brent Wood
--- On Fri, 9/4/09, Chris Hermansen <chris.hermansen at timberline.ca> wrote:
> From: Chris Hermansen <chris.hermansen at timberline.ca>
> Subject: Re: [postgis-users] several SRID on one table
> To: pcreso at pcreso.com, "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> 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 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: pcreso at pcreso.com
>
> Date: Thu, 3 Sep 2009 00:20:02
> To: Ben Madin<ben at remoteinformation.com.au>
> Cc: <postgis-users at postgis.refractions.net>
> 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 <ben at remoteinformation.com.au>
> wrote:
>
> > From: Ben Madin <ben at remoteinformation.com.au>
> > Subject: Re: [postgis-users] several SRID on one
> table
> > To: pcreso at pcreso.com,
> "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> > 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 , pcreso at pcreso.com
> > 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, Steve.Toutant at inspq.qc.ca
> > <Steve.Toutant at inspq.qc.ca>
> > wrote:
> >
> > From: Steve.Toutant at inspq.qc.ca
> > <Steve.Toutant at inspq.qc.ca>
> > Subject:
> > [postgis-users] several SRID on one table
> > To: "PostGIS
> > Users Discussion" <postgis-users at postgis.refractions.net>
> > 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
> >
> > steve.toutant at inspq.qc.ca
> >
> > http://www.inspq.qc.ca
> >
> >
> > -----Inline Attachment
> > Follows-----
> >
> >_______________________________________________
> > 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
> >
> >
> >
> > --
> > Ben Madin
> > REMOTE INFORMATION
> >
> > t : +61 8 9192 5455
> > f : +61 8 9192 5535
> > m : 0448 887 220
> > Broome WA 6725
> >
> > ben at remoteinformation.com.au
> >
> >
> >
> >
>
> Out
> > here, it pays to know...
> >
> >
> >
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list