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

On Wednesday 23 November 2005 17:15, Brent Wood wrote:
> 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

Interesting suggestion though at this point clueless how to do that. OTOH, at
some point I do want to use PostGIS. One of the other import things the
database needs to track is the lat/long of each well. I had forgot that til
you mentioned PostGIS. It's a relatively new documentation requirement by
Ohio when well logs are sent in.

>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
You can tuna piano but you can't tune a fish.