[postgis-users] several SRID on one table
Ben Madin
ben at remoteinformation.com.au
Thu Sep 3 19:23:13 PDT 2009
Brent,
I think I misunderstood what you were doing, but it sounds like you
are storing two separate pieces of information (start and end point),
not the same information in two separate columns. Is this right? I
certainly agree that storing a line is inappropriate in that case, in
the same way that storing zero is an inappropriate solution to
represent no (null) data.
This certainly proves that no one solution fits all - good news for
those of us making a living from data solutions development I guess!
cheers
Ben
On 04/09/2009, at 3:05 , pcreso at pcreso.com wrote:
>
> 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 <chris.hermansen at timberline.ca>
> wrote:
>
>> From: Chris Hermansen <chris.hermansen at timberline.ca>
>> Subject: Re: [postgis-users] several SRID on one table
>> To: pcreso at pcreso.com, "PostGIS Users Discussion" <postgis-users at postgis.refractions.net
>> >
>> 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 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
--
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...
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090904/aac2740d/attachment.html>
More information about the postgis-users
mailing list