[postgis-users] Getting height profile from contour lines

[Eric Ladner]

On Mon, Dec 20, 2010 at 4:43 AM, Matthias Ludwig <kaotika at gmx.de> wrote:
> Hello,
>
> I want to calculate a height profile from contour lines. To do this I firstly calculate the intersection points between a profile line and the contour lines. This works good if there is only one line in the profile table. With more then one profile inside i get doubled points with different distance values. How can I prevent this? In other programming languages I would use a loop to calculate the intersection points for the first profile line then for the second... Is this also possible with one SQL query or only in combination with PL/pgSQL?
>

If you've got profiles in one table and contours in another, just do
an open cartesian join between the tables and you'll get a combination
of every row from the first table combined with every row from the
second.

Ex:

gisdb2=> create table a ( nnum integer) ;
CREATE TABLE
gisdb2=> create table b ( lett varchar(1));
CREATE TABLE
gisdb2=> insert into a values (1);
INSERT 0 1
gisdb2=> insert into a values (2);
INSERT 0 1
...etc...
gisdb2=> insert into b values ('A');
INSERT 0 1
gisdb2=> insert into b values ('B');
INSERT 0 1
gisdb2=> insert into b values ('C');
INSERT 0 1
...etc...
gisdb2=> select nnum, lett from a, b;
 nnum | lett
------+------
    1 | A
    1 | B
    1 | C
    1 | D
    1 | E
    2 | A
    2 | B
...etc...

> If someone see a point I can optimize please tell it too. *g*
>
> profile line table (t_hoehenprofil_linie):
> -------------------
>  gid | id | the_geom
> -----+----+----------------------------------------
>  13 |    | 0102000020E964000003000000687BB5B71C911
>  14 |    | 0102000020E964000002000000589CEDA095911
>
> part of contour line table(hoehenlinien_1m):
> ---------------------------
> gid | ID | hoehe | the_geom
> ----+----+-------+----------------------------------------------------
>  1 |  0 |    86 | 0102000020E9640000090000008A3488A6ED9A1341CCEEFD1FE
>  2 |  1 |    87 | 0102000020E96400000B00000029EA729EDDF11341CCEEFD1FE
>  3 |  2 |    88 | 0102000020E96400000D000000DA26C90C53A11341CCEEFD1F
>
> query:
> ------
> DROP TABLE IF EXISTS t_schnittpunkte_hoehenprofil;
> CREATE TABLE t_schnittpunkte_hoehenprofil AS
> SELECT
>        nextval('id_number') AS gid,
>        t_hoehenprofil_linie.gid as profile_nr,
>        ergebnis.hoehe,
>        --Calculate point distance on profile line
>        round(
>                CAST(ST_Line_Locate_Point(t_hoehenprofil_linie.the_geom, ergebnis.the_geom) * ST_Length(t_hoehenprofil_linie.the_geom) AS numeric), 2
>        ) AS dist,
>        ergebnis.the_geom
> FROM
>        t_hoehenprofil_linie,
>        (SELECT
>                cut.gid, cut.hoehe,
>                (ST_Dump(
>                        ST_Intersection(t_hoehenprofil_linie.the_geom, cut.the_geom)
>                )).geom as the_geom
>        FROM
>                t_hoehenprofil_linie,
>                (
>                SELECT
>                        nextval('id_number') AS gid,
>                        intersecting.hoehe,
>                        --Dump it to prevent multilinestrings
>                        (ST_Dump(
>                                --cut contour lines with convex hull
>                                ST_Intersection(
>                                        --generate convex hull from profile
>                                        --with 10m buffer
>                        ST_Buffer(ST_ConvexHull(t_hoehenprofil_linie.the_geom), 10),
>                                        intersecting.the_geom
>                                )
>                        )).geom AS the_geom
>                FROM
>                        --get intersecting contours with profile
>                        (SELECT l.hoehe, l.the_geom
>                                FROM t_hoehenprofil_linie, hoehenlinien_1m as l
>                                WHERE ST_Intersects(l.the_geom, t_hoehenprofil_linie.the_geom)
>                        ) as intersecting,
>                        t_hoehenprofil_linie
>                ) as cut
>        ) as ergebnis
>
>
> output table:
> -------------
> SELECT gid, profile_nr, hoehe, dist, AsText(the_geom) FROM t_schnittpunkte_hoehenprofil;
>
>  gid  | profile_nr | hoehe | dist  | astext
> -------+------------+-------+-------+------------------------
>  58023 |         15 |   160 |  0.00 | POINT(320624.965330361 5777867.98451583)
>  58007 |         16 |   160 |  0.00 | POINT(320586.767288447 5777830.54803155)
>  58010 |         15 |   160 |  0.00 | POINT(320624.965330361 5777867.98451583)
>  58020 |         16 |   160 |  0.00 | POINT(320586.767288447 5777830.54803155)
>  58028 |         15 |   159 |  2.30 | POINT(320649.710451749 5777853.53161307)
>  58011 |         16 |   160 |  6.24 | POINT(320624.965330361 5777867.98451583)
>  58024 |         16 |   160 |  6.24 | POINT(320624.965330361 5777867.98451583)
>  58006 |         15 |   160 | 10.32 | POINT(320586.767288447 5777830.54803155)
>  58019 |         15 |   160 | 10.32 | POINT(320586.767288447 5777830.54803155)
>  58015 |         16 |   160 | 14.56 | POINT(320594.200502687 5777798.8208976)
>  58029 |         16 |   159 | 34.90 | POINT(320649.710451749 5777853.53161307)
>  58014 |         15 |   160 | 42.91 | POINT(320594.200502687 5777798.8208976)
> --
> Mit freundlichen Grüßen
>
> Matthias Ludwig
> B.Eng. Geoinformation
>
> Tel.:   0177/4913288
>         030/68329587
> ICQ:  163168410
>
> Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
> Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Eric Ladner