[postgis-users] Need help with a query

[David William Bitner]

Hi Bob,

Select b.official_name, the_geom,z(the_geom)-top_depth as top,
z(the_geom)-bottom_depth, layer_number, lith_type from borehole b left join
lithology l using (official_name) order by official_name, layer_number desc;

Is something like this what you are looking for?

David

On Fri, Jun 6, 2008 at 2:09 PM, Bob and Deb <bobdebm at gmail.com> wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
************************************
David William Bitner
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080606/72f01ece/attachment.html>