Friday, March 30, 2012 9:43 PM
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.
Saturday, March 31, 2012 7:02 AMAnswerer
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/
- Marked As Answer by Maggie LuoMicrosoft Contingent Staff, Moderator Friday, April 06, 2012 10:42 AM