Re: Best way to represent values. - Mailing list pgsql-general

I suggest you look at PostGIS to store 2D & 3D geometric (spatial) data,
it may solve some of your problems....

You can store a vertical line as a well, and segments of that line which
represent the layers you are describing.

Brent Wood


On Tue, 22 Nov 2005, Dennis Veatch wrote:

> On Tuesday 22 November 2005 11:40, Lincoln Yeoh wrote:
> > At 01:19 PM 11/21/2005 -0500, Dennis Veatch wrote:
> > >I had thought just adding some fields called topsoil_start/topsoil_end,
> > >gravel_start/gravel_end, etc. But them I'm left with how to take those
> > > values and give to total depth for each layer and total depth of the
> > > well.
> > >
> > >But I'm not sure that is the best way to handle this.
> > >
> > >Does anyone have some other suggestions?
> >
> > I'm no DB guru, so I am probably a bit out of my depth here.
> >
> > But how about something like:
> >
> > create table well (
> > id serial,
> > name text,
> > created timestamp default null,
> > -- more fields probably follow - site, location, status etc
> > )
> >
> > create table layers (
> > id serial,
> > well_id int,
> > layertype_id int,
> > end_depth int
> > )
> >
> > create table layertype (
> > id serial,
> > layername text,
> > comment text
> > -- probably more fields
> > )
> >
> > (you probably might want to add the foreign key constraints etc etc).
> >
> > Basically you have table of wells.
> >
> > And then you have lots of rows in layers that are linked to the same well
> > by well_id, and you sort them by the end depth.
> >
> > And then you have a table of layertypes which each layer links to. So you
> > can create types of layers.
> >
> > e.g.
> > select layername,startdepth from well,layers,layertype
> > where
> > well.name='somewell'
> > and
> > well_id=well.id
> > and
> > layertype.id=layertype_id
> > order by end_depth asc
> >
> > I've no experience in wells but you might want an "Unknown" layertype to
> > fill in the gaps ;).
> >
> > You might alternatively want to have "start depth" instead of an "end
> > depth". I'd do end depth, since your data probably ends at the deepest
> > layer (I assume you never reach the core ;) ).
> >
> > You may need both start and end depths if there are multiple layers per
> > depth per well (nonuniform). In that case the queries could be a bit more
> > complex...
> >
> > I might have overlooked a few pitfalls here and there. Oh well...
> >
> > Good luck!
> >
> > Link.
> >
> > *runs and hides*
> >
>
> Thanks everyone for the on-line and off-line suggestions. Now I just need to
> sort through them.
>
> --
> You can tuna piano but you can't tune a fish.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>