[postgis-users] Re: get data from 2 tables and 2 columns fro second table

[lisek lichu]

Ok i think i have resolved this problem :P

select e.id, AsText(p1.the_geom) as start, AsText(p2.the_geom) as final
    from edges as e,
         points as p1,
         points as p2
    where e.start_point_id = p1.id and e.final_point_id = p2.id;

after that i get what  i want :)

if somebody has better solution for that it would be really great to share
it with me :P



On Sun, Dec 14, 2008 at 21:20, lisek lichu <lisek.lichu at gmail.com> wrote:

> hello i have simple problem but I'm new in SQL so I will as here :P
>
> i have 2 tables
>
>
> CREATE TABLE points (
> id      SERIAL PRIMARY KEY
> );
> SELECT AddGeometryColumn('','points','the_geom','4326','POINT',2);
>
>
> CREATE TABLE edges (
> id                SERIAL PRIMARY KEY,
> start_point_id    INTEGER REFERENCES points (id) ON DELETE RESTRICT ON
> UPDATE CASCADE,
> final_point_id    INTEGER REFERENCES points (id) ON DELETE RESTRICT ON
> UPDATE CASCADE
> );
>
> and right now I want to write a query to get in one line id from edges, and
> two geometrical points (start and final)
> something like this:
>
> select e.id, AsText(p.the_geom) as start, AsText(p.the_geom) as final
>    from edges as e,points as p
>    where ???
>
> and I dont knoe how to write thi query to get in one line edge ID and two
> geometrical points (start and final)
>
> Can somebody help me with that?
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081214/1ca13e9c/attachment.html>