Best way to represent values.

On Nov 22, 2005, at 3:19 , 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?

This is similar in concept to temporal intervals. You might want to
look at "Temporal Data and the Relational Model" by Date, Darwen, and
Lorentzos for general theory, and "Developing Time-Oriented Database
Applications" by Richard Snodgrass for implementations in SQL. The
latter is available as a PDF download (the book itself is out of print):
http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Hope this helps!

Michael Glaesemann
grzm myrealbox com

On Monday 21 November 2005 20:04, Michael Glaesemann wrote:

On Nov 22, 2005, at 3:19 , 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?

This is similar in concept to temporal intervals. You might want to
look at "Temporal Data and the Relational Model" by Date, Darwen, and
Lorentzos for general theory, and "Developing Time-Oriented Database
Applications" by Richard Snodgrass for implementations in SQL. The
latter is available as a PDF download (the book itself is out of print):
http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Hope this helps!

Hee, well that's um, kinda over my head. Hee and I'm not all the way through
the PostgreSQL book I just bought. There's probably a gap there. :)

Though I will try to glean something out of the link.

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

The problem is that each well can have a different number of and types of
layers. Trying to pre-plan all the combinations could be a big headache. My
first thought is the following layout-
well_number layer_number bottom_depth layer_type
1 1 10 topsoil
1 2 25 gravel
and so on. The bottom_depth of one layer is the top_depth of the one below.
The final bottom_depth is the depth of the well.
The layer_types can be pulled from another table to maintain consistency and
allow for new types as needed. Come report time you order by
well_no,layer_number to get the desired information.

On Monday 21 November 2005 05:29 pm, Dennis Veatch wrote:

On Monday 21 November 2005 20:04, Michael Glaesemann wrote:

On Nov 22, 2005, at 3:19 , 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?

This is similar in concept to temporal intervals. You might want to
look at "Temporal Data and the Relational Model" by Date, Darwen, and
Lorentzos for general theory, and "Developing Time-Oriented Database
Applications" by Richard Snodgrass for implementations in SQL. The
latter is available as a PDF download (the book itself is out of print):
http://www.cs.arizona.edu/people/rts/tdbbook.pdf

Hope this helps!

Hee, well that's um, kinda over my head. Hee and I'm not all the way
through the PostgreSQL book I just bought. There's probably a gap there. :)

Though I will try to glean something out of the link.

--
Adrian Klaver
aklaver@comcast.net

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*

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.

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 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.