[postgis-users] several SRID on one table

[pcreso at pcreso.com]

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...
> 
> 
> 
> 
>