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

    Friday, March 30, 2012 9:43 PM

Answers

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

    Saturday, March 31, 2012 7:02 AM
    Answerer