[postgis-users] Elevation Profiles

[James Marca]

At approximately Wed, Nov 24, 2004 at 11:46:31AM -0700, ANDREW WOOLEY wrote:
> Folks,
> 
> I have been working on this idea for some time and have been trying to
> implement elevation profiles and have yet to get it working like I want.
>  I am hoping that someone has some good ideas for me.
> 
> Here is my problem. I have a series of lines in a table.  I also have a
> table with elevations as polygons. I overlay them using this query:

> select e.gridcode as elevation from mtntrails_elevation as e, route as
> r where e.the_geom && r.the_geom; 
...
>  However, when I list out the elevations, they don't seem to be in the
> "correct" order - that is the order I think they should be in.  The way

As far as I can see, you haven't asked postgresql to order your
results, so you should expect them to be in no particular order.


I think you need something like 

ORDER BY  r.timestamp

This is quite similar to something I am doing in my work.  I have gps
points going into a table called observations.  After travel is
complete, I collate observations into contiguous, continuous segments
of travel, and I want to link these gps linestrings with Tiger/LINE
complete chains for analysis.

Simply fetching the tiger complete chains that overlap my segments
doesn't quite work.  The following SQL gets this overlap:

SELECT segment_id,ogc_fid,a.geom,c.geom 
       FROM segment 
       INNER JOIN segment_lines USING(segment_id)  
       INNER JOIN geom_lines_32611 AS a ON line_gid=gid 
       INNER JOIN ( 
	     SELECT b.geom,ogc_fid 
		    FROM geom_lines_32611 AS b 
		    INNER JOIN tiger_cc_lines ON b.gid=line_gid 
		    WHERE ogc_fid IS NOT NULL) AS c ON true  
       WHERE segment_id=66781 AND a.geom && c.geom;

But there are two problems.  First I have the random sorting problem
that you observed, in that the returned Tiger/LINE complete chain
geometries are in *any* order rather than in the order that I crossed
them.  Second, the && operator is a bounding box overlapper, so the
longer my trip segment and the longer any nearby Tiger/LINEs, the more
items are caught in the overlap.  In this case with segment_id=66781
(arbitrary number), I get 93 matching Tiger complete chain line
segments (roads).  

What I want to do is process the roads in order of appearance, so that
I can build up a tree of *possible* links I drove along and then later
reject those links which are obviously wrong as travel continues.  So
I can't use the segment geometry (LINESTRING), but instead have to use
the observation geometry (POINT), but with a join between segments and
observations to allow me to select those points that belong in some
arbitrary segment.  Using the observation geometry also allows me to
get access to the time it was observed (the utc field, below).  The
following SQL does this. 

SELECT id,utc,segment_id,a.geom,c.geom,ogc_fid 
       FROM observations 
       INNER JOIN obs_segment ON obs_segment.obs_id=id 
       INNER JOIN obs_points ON obs_points.obs_id=id 
       INNER JOIN geom_points_32611 AS a ON point_gid=gid 
       INNER JOIN ( 
	     SELECT b.geom,ogc_fid 
		    FROM geom_lines_32611 as b 
		    INNER JOIN tiger_cc_lines ON b.gid=line_gid 
		    WHERE ogc_fid IS NOT NULL) AS c ON true  
       WHERE segment_id=66781 AND c.geom && a.geom 
       ORDER BY utc;

 (Note that I have observations in one table, and the geometries in a
  separate table, geom_points_32611, and I join the two in a third
  table, obs_points.  Similar for line geometries.)

Now I have the opposite problem from the previous SQL query.  The
point-based query returns 15 rows, but it only includes 11 of the 14
GPS observations that make up the segment in question.  The problem is
that I am now overlapping a point with the bounding box of each
individual Tiger/LINE complete chain geometry.  If the nearest
complete chain is short, and if the GPS error is high, then the GPS
point will not overlap any complete chain geometries.  I tried using
buffer(geom,15), but that took forever.

So I am currently handling those cases in my perl code simply by
making sure that my resulting complete chains form a connected graph,
and fetching the missing links if they do not.

I don't imagine elevation data has any gaps, so this probably won't be
a problem for you (every trail point will be in an elevation
geometry).  

Hope that is relevant,
James