sql spatial search, find coordinates on a route in certain radius

Question

I'm looking for an answer on the following problem,

I've managed to retrieve all the points in a dataset that are within a certain radius from a single point. Input is longitude, latitude and radius. I'm using the Sql STDistance functionality to achieve this. Now I want to do the following, I have a route from point a to point b, and I want to retrieve all geolocation points that are on that route for a certain radius. As example, inn the screenshot I want to retrieve all black circles,and nothing else from outside the red radius. My input params are start - and endpoint, and a radius. I'm using google maps to render markers and route. I'm trying to make this work by getting the steps from dirService.route, ex. response.routes[0].legs, but the interval between the steps is to large considering the radius input. Anyone? Example

Answer 1

Since you didn't provide any schema or a sample query, I'm going to have to explain this in generalities. Assuming that you have a Geography instance, you can call STBuffer() on it to get what you're calling the "red radius". As far as getting the "black circles", I assume that you have coordinates for those. Whether you want the portion of the black circle that falls within the red radius or a binary "give me the circle if it's fully contained othewise don't give it to me at all", you can use either STIntersects() or STContains() (respectively).