[postgis-users] several SRID on one table

Thu Sep 3 07:43:36 PDT 2009

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