Sorry all, typing faster than I was thinking, I guess. Below, I said: '[when] the cost of "use" is greater than the additional I/O overhead created by joining multiple separate - perhaps clustered - tables together as needed'
What I meant to say was 'when the cost of "use" - as multiple geometries in a single row - is less than the additional I/O overhead etc etc' Sorry sorry sorry. Chris Hermansen wrote: > 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 > -- Regards, Chris Hermansen mailto:[email protected] tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644 Timberline Natural Resource Group · http://www.timberline.ca 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
