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 > > > -----Inline Attachment > Follows----- > > _______________________________________________ > 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 > > > > -- > Ben Madin > REMOTE INFORMATION > > t : +61 8 9192 5455 > f : +61 8 9192 5535 > m : 0448 887 220 > Broome WA 6725 > > [email protected] > > > > Out > here, it pays to know... > > > > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
