[postgis-users] Getting height profile from contour lines

[Matthias Ludwig]

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 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