Here's another SQL puzzeler. The goal is to calculate the % slope along a line that has been snapped to 2 GPS points (in the same drawing) that contain a height value. Attached is a map file with such a drawing. Slope is the rise in height over [lenght (I)] of the line. Is it possible to write an SQL statement that calculates the rise or the slope along the line? Thanks for any help.

Attachments:
slope calc.map

I should have added that this would provide a nice way to get accurate slope calcs because you can choose to collect GPS points along a slope's breaklines (hopefully I used that term correctly) and then draw lines between points that follow the actual slope. This is in contrast to calculating slope with the surface tools that aggregates lots of nearby data.

However, so far a SQL solution eludes me.

A start:

SELECT 

    [ID], [Line], 

    [L] AS [Distance], 

    [dH] AS [Rise],

    ([dH] / [L]) * 100 AS [Slope%], 

    Rad2Deg(Atn([dH] / [L])) AS [Slope°] 

FROM

    (SELECT [ID], [Line], Length([Line]) AS [L], [H2] - [H1] AS [dH]

    FROM

        (SELECT [ID], Geom([ID]) AS [Line], [Rise], [Slope] FROM [Garmin] WHERE IsLine([ID]))

        INNER JOIN

        (SELECT Geom([ID]) AS [Point1], [Height] AS [H1] FROM [Garmin] WHERE IsPoint([ID]))

        ON Touches(StartPoint([Line]), [Point1])

        INNER JOIN

        (SELECT Geom([ID]) AS [Point2], [Height] AS [H2] FROM [Garmin] WHERE IsPoint([ID]))

        ON Touches(EndPoint([Line]), [Point2])

    );

Thanks again Tim. This SQL seems like it should work beautifully but after breaking it down, it seems that the inner joins are failing which causes it to return no records. Thinking maybe there was an accuracy issue between lines and points, I tried this condition for the join Contains(Buffer([Line], 5), [Point1]) but still no records.

Hi Mike

I choosed a Gabriel network from the toolbox for your points and did the queryrun and it was working well.

think you are right regading the accuracy.

The Gabriel network is a great way to create lines for this exercise. Thanks for that adaptagis. Don't know what happened to the original lines in the above example as I recall using snapping to draw them. Nevertheless, the query seems to be working great whether snapping lines by hand or using the Gabriel transform.

One suggestion is to add the absolute value function to ensure all values are positive. ... ABS([H2] - H1]) ... on line 8. Thanks very much Tim for another very useful tool. What wonderful flexibility SQL has

Should have added that the gabriel netowork is a great way to add lots of line connections quickly but they may not respect any ridgelines (slope breaklines) on the ground. One could write SQL to delete lines that cross lines in a ridgeline drawing.

Also, I know that handheld GPS units are not that accurate when it comes to giving the height above sea level. However, for calculating slope the concern is accuracy compared to other GPS points. I'm hoping that they are relatively accurate in this sense, especially when collected within the same hour or so. Any thoughts?

I did try using a Trimble GeoXT and collected a point every second. The result was not good because the points were so close together. Close points magnify the inaccuracy of the original height value from the GPS. Just some thoughts for how best to collect GPS points for those who might use this.