Finding all table entries within a radius

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding all table entries within a radius

Author

Topic

blindingdawn
Starting Member

1 Post

Posted - 2009-06-02 : 11:29:53

OK.. Right now I have the following function (Scalar-valued)


ALTER FUNCTION [dbo].[Calculate_Distance]
(
@Lat1 float, @Long1 float, @Lat2 float, @Long2 float
)
RETURNS float
AS
BEGIN
	DECLARE @toRadians float
	SET @toRadians = PI() / 180
	
	DECLARE @R float
	SET @R = 3958.7558657440545 -- mi
	
	DECLARE @Distance float
	SET @Distance = acos(sin(@Lat1*@toRadians)*sin(@Lat2*@toRadians)+cos(@Lat1*@toRadians)*cos(@Lat2*@toRadians)*cos((@Long2-@Long1)*@toRadians)) * @R;
  
	RETURN @Distance

END

I have several values that are the same outside of the lat/lon.

Example


2	27.32817	-80.3744	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0
2	27.32817	-80.3745	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0
2	27.3318	        -80.3744	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0
2	27.33181	-80.3745	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0
2	27.33544	-80.3745	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0
2	27.33544	-80.3744	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0
2	27.33908	-80.3744	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0
2	27.33909	-80.3745	0	Florida's Tpke	River Park	Florida	United States	1	0	0	0	1	0	0

What I would like to do is to find all entries in the table that will first pic one location, find all in the surrounding area and if they match certain values in the first location, list it. This will go one by one and list all locations if they match the other values.

I Can use this to find all of the locations within a one mile radius:

Select * from /tablelocation/ where dbo.Calculate_Distance(27.32817,-80.3744,/tablelocation/.lat,/tablelocation/.lon) < 1

but I want it to scan the database to do the same thing and group location by some value that I can group together if they all match and delete one of them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources