Retrieve all coordinates that lie a certain distance from a linestring.

Question

I need help in getting around this problem.

I have two tables that have GPS coordinate locations (X, Y, Z in separate columns as well as a geometry column, GP) known as TableA. The second TableB also have separe columns (Xs,Ys,Zs , Xe,Ye,Ze as the start and finish points of traverses and then a geometry column GL, of linestrings formed from Xs,Ys,Zs , Xe,Ye,Ze). I can display the geometry as well as linestrings in sqlserver 2008r2 spatial results.
My challenge is:

1.Bringing these two geometry points and linestrings together as one display when a query is run.
2.Get a logic/algorithm to retrieve all points that lie within  a certain distance (for example x meters from the  red linestring) so that they can be shown in both spatial results as well as in the business Intelligence environment of ss 2008r2. Below is a sketch of the problem: I appreciate any suggestion.

Answer 1

To display both points and linestrings in the same results:

SELECT GP FROM TableA
UNION ALL
SELECT GL FROM TableB;

To select all points that lie within x metres of a given linestring (I'm not sure how you're defining what the "red" linestring is):

-- Select the Red Linestring from the Linestring table
DECLARE @RedLinestring geography;
SELECT @RedLinestring = GL FROM TableB WHERE id = '...';

-- Select points that are within x metres of the linestring
SELECT GP FROM TableA
WHERE GP.STDistance(RedLinestring) < x;

---

twitter: @alastaira blog: http://alastaira.wordpress.com/