[postgis-users] several SRID on one table
pcreso at pcreso.com
pcreso at pcreso.com
Fri Sep 4 14:34:06 PDT 2009
Hi Ben,
Yes, the start & finish points are recorded values, along with the respective times. The trackline is simply an inferred path between these points (and only exists where both points are recorded, which is not in all cases, unfortunately).
I find a lot of traditional GIS users are stuck on there being one "geometry" per feature, as in the old GIS model.
With or without normalisation issues, one of the freedoms of a spatially enabled RDBMS is that is possible to store several geometries per entity (or feature), which makes my life working with spatial data so much easier!!
In another case I store a polygon & a point for labelling it, which is not the conventional centroid, but a somewhat arbitrary location, not easily subject to automatic derivation. There are a number of instances where more than one geometry can be valid attributes of a feature without de-normalising.
However, such columns share issues common to denormalised structures, in that if one column is modified, it generally has an impact on the value that should be stored in the others that needs to be managed.
In storing data describing events, for example, there may be a business rule that an event cannot have an end time - start time (interval) greater than a specified period. Similarly with geometries, the start & end should not be more than a certain distance apart. Changing one value has an impact on the other. The issues are similar to a denormalised table, but the data are not necessarily denormalised.
However, as Chris says, when the same geometry is stored, but in different SRID's, for example, the issue is precisely one of denormalisation.
Cheers,
Brent
--- On Fri, 9/4/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>
> Cc: chris.hermansen at timberline.ca
> Date: Friday, September 4, 2009, 2:23 PM
> 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...
>
>
>
>
>
More information about the postgis-users
mailing list