Thread: Best way to represent values.
I have several fields that needs to be within a table but not real sure the best way to represent this data. The scenario I'm trying to incorporate is a form that has to be filled out went a well is drilled and then sent to the state. One of the things the form wants is the different depths various types of underburden was encountered. So you start with topsoil that goes from 0 feet to X depth, then say at that X depth clay is encountered to depth Y and then at depth Y gravel is encountered and water is found. 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? BTW, thanks to all that submitted some recommended PostgreSQL books. I have started with Beginning Databases with PostgreSQL. It's been very helpful. I plan to pursue the other recommendations. -- You can tuna piano but you can't tune a fish.
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 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 >
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.