[postgis-users] several SRID on one table
Chris Hermansen
chris.hermansen at timberline.ca
Thu Sep 3 08:38:12 PDT 2009
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 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Regards,
Chris Hermansen mailto:chris.hermansen at timberline.ca
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
More information about the postgis-users
mailing list