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 <[email protected]> wrote: > 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 > > > > > > -----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 > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
