[postgis-users] Need help with a query

[Bob and Deb]

Hello all,

I'm new to postgis and postgresql and I need some help with a query.  I am
trying to visualize some boreholes by connecting
the 3d point geometry from a borehole spatial table to the different rock
formations (non spatial table called lithology).  borehole
has a key field called official_name and 3d point geometry called the_geom.
lithology has a key field official_name,layer_number
as well as top_depth, bottom_depth, and lith_type.

     x      <----- borehole location                <---- top_depth of 1st
lith
     |
     |
     x                                                      <---
bottom_depth of 1st lith          <--- top_depth of 2nd lith
     |
     |

x
<--- bottom_depth of 2nd lith

I have figured out how to use ST_MakeLine to make a line that connection for
a borehole down to its total depth ( max(bottom_depth) ),
but I have not figured out a query that would do what I diagrammed above.

I'm using this database to learn both postgis and postgresql and would
greatly appreciate any help.

Thank in advance.

Bob

select official_name, the_geom from borehole
select official_name, top_depth, bottom_depth, layer_number, lith_type from
lithology
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080606/4a2c4451/attachment.html>