# SDS spatial search on Line

• ### Question

• I have a as the crow flies line with a one start lat/long and one end lat/long. I would like to intersect this with a group of points and getting all points within x miles of this line. Where is some sample code to do this?

Monday, February 12, 2018 4:19 PM

• Take a look at the spatial math module. It provides the calculaitons you need. Here is an example that calculates the distance from a route line to a pushpin:

Simply do this for each pushpin in your data set using your line instead of the route line.

If you want to calculate locations along the line (not your pushpin) there are two ways to do this. The first is to use the interpolate function in the spatial math module which will calculate "mid-point" locations along your line, however it is spatially accurate and result in a curved line. If you want points that are pixel accurate and not spatially accurate you will need to use the tile math tools to do this.

Monday, February 12, 2018 5:10 PM
• That is allot of work for an Intersect (Point by Point)...

Maybe this will trigger some alternative.  In GIS when faced with this problem we generally:

1.  Buffer the line to your search distance/radius  this will create one polygon.

2.  With the above created polygon perform an intersect your point data set.

3.  The result will be the points that are within your criteria distance.

I have not worked with the Bing Spatial Data services .... but in my case I use SQL Server to store my data.  SQL server has an Intersect function that works well.  What I do is send the requirements to create the "Buffer" to a SQL procedure and on the fly within the procedure create the buffer and do the intersection query and return points.   For example: (In this case I am using a user click to draw the polygon radius buffer, but a line buffer could used as well).

```/****** Object: Procedure [dbo].[usp_ProjectQuery]   Script Date: 2/13/2018 10:54:03 AM ******/USE [GeoTest];GOSET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GO-- =============================================-- Author:		<Ted Kowal>-- Create date: <7/2013>-- Description:	<Return the Projects found withing a user defined--               radius click on the map (lat/long)>-- =============================================CREATE PROCEDURE [dbo].[usp_ProjectQuery] 	-- Add the parameters for the stored procedure here	@latitude float,	@longitude float,	@BufferSize floatASBEGIN	-- SET NOCOUNT ON added to prevent extra result sets from	-- interfering with SELECT statements.	SET NOCOUNT ON;    Declare @Point geography	SET @Point = geography::Point(@latitude,@longitude, 4326)	-- Use STBuffer() to greate a buffer around the point to BufferSize in Feet	Declare @SearchArea geometry	Declare @G geography	-- This should be done usine Geography (Have not been able to get it working using	--                                      Geometry)	Set @G = @Point.STBuffer(@BufferSize *.3048)	-- Convert Search Area to Geometry from Geography	Set @SearchArea = geometry::STGeomFromWKB((Select @G.STAsBinary()), 4326)	-- Select any lights that intersect the search area	Select	   Distinct ProjectNumber, ProjectName,	   CONCAT(ProjectDescription,' ',ProjectDesc2Part) as Description,	   ProjectType,Status,CompletionDate	From 	   PROJECTS	Where	   Shape.STIntersects(@SearchArea) = 1ENDGO
```

• Edited by Tuesday, February 13, 2018 4:00 PM
• Proposed as answer by Tuesday, February 13, 2018 4:40 PM
• Marked as answer by Wednesday, February 21, 2018 11:09 PM
Tuesday, February 13, 2018 3:56 PM

### All replies

• Take a look at the spatial math module. It provides the calculaitons you need. Here is an example that calculates the distance from a route line to a pushpin:

Simply do this for each pushpin in your data set using your line instead of the route line.

If you want to calculate locations along the line (not your pushpin) there are two ways to do this. The first is to use the interpolate function in the spatial math module which will calculate "mid-point" locations along your line, however it is spatially accurate and result in a curved line. If you want points that are pixel accurate and not spatially accurate you will need to use the tile math tools to do this.

Monday, February 12, 2018 5:10 PM
• I do have points along the way. I was hoping to be able to just use the line and not send individual point spatial calls.

Could I generate my own shape file around the line and use this as one spatial call to a points database with an in or out? this would be probably be more efficient than 60 individual calls.

Tuesday, February 13, 2018 2:08 PM
• That is allot of work for an Intersect (Point by Point)...

Maybe this will trigger some alternative.  In GIS when faced with this problem we generally:

1.  Buffer the line to your search distance/radius  this will create one polygon.

2.  With the above created polygon perform an intersect your point data set.

3.  The result will be the points that are within your criteria distance.

I have not worked with the Bing Spatial Data services .... but in my case I use SQL Server to store my data.  SQL server has an Intersect function that works well.  What I do is send the requirements to create the "Buffer" to a SQL procedure and on the fly within the procedure create the buffer and do the intersection query and return points.   For example: (In this case I am using a user click to draw the polygon radius buffer, but a line buffer could used as well).

```/****** Object: Procedure [dbo].[usp_ProjectQuery]   Script Date: 2/13/2018 10:54:03 AM ******/USE [GeoTest];GOSET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GO-- =============================================-- Author:		<Ted Kowal>-- Create date: <7/2013>-- Description:	<Return the Projects found withing a user defined--               radius click on the map (lat/long)>-- =============================================CREATE PROCEDURE [dbo].[usp_ProjectQuery] 	-- Add the parameters for the stored procedure here	@latitude float,	@longitude float,	@BufferSize floatASBEGIN	-- SET NOCOUNT ON added to prevent extra result sets from	-- interfering with SELECT statements.	SET NOCOUNT ON;    Declare @Point geography	SET @Point = geography::Point(@latitude,@longitude, 4326)	-- Use STBuffer() to greate a buffer around the point to BufferSize in Feet	Declare @SearchArea geometry	Declare @G geography	-- This should be done usine Geography (Have not been able to get it working using	--                                      Geometry)	Set @G = @Point.STBuffer(@BufferSize *.3048)	-- Convert Search Area to Geometry from Geography	Set @SearchArea = geometry::STGeomFromWKB((Select @G.STAsBinary()), 4326)	-- Select any lights that intersect the search area	Select	   Distinct ProjectNumber, ProjectName,	   CONCAT(ProjectDescription,' ',ProjectDesc2Part) as Description,	   ProjectType,Status,CompletionDate	From 	   PROJECTS	Where	   Shape.STIntersects(@SearchArea) = 1ENDGO
```

• Edited by Tuesday, February 13, 2018 4:00 PM
• Proposed as answer by Tuesday, February 13, 2018 4:40 PM
• Marked as answer by Wednesday, February 21, 2018 11:09 PM
Tuesday, February 13, 2018 3:56 PM