Performing a proximity search - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Performing a proximity search

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-03-17 : 12:40:07

Hello everyone,

This is probably going to be the toughest question I have ever asked on this forum.

I need to perform a proximity search and the way it is laid out is that a user enters a pair of x/y coordinate pair like x = 1234.2; y=5346.02
Based on the x/y coordinate pair values above (above are just examples), an address or several addresses could fall within the radius of the x/y coordinate pair but we want the closest address to the x/y coordinate pair to be displayed.
Needless to say, I haven't got a clue how to get started on this.
Any suggestions/samples/links would be greatly appreciated.
Many thanks in advance.
Thanks all in advnce.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-17 : 13:27:21

Have a look at the following link and see if it is useful for your situation.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30843&SearchTerms=zipcode

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-17 : 13:44:53

Fresh from my post in the script library (time in analog) this uses vectors in a similar way to what you are looking for.

If you have a basic understanding of trigonometry it should be quite easy for you.

Perhaps this can help..... Take a look:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33357

BTW Congrats on your veteran status!

Duane.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-03-17 : 14:23:42

Hi ehorn and ditch,

First I think ehorn's code is much closer to what I am looking for ONLY because it makes more sense to me.
I understand it a little bit better.
ehorn, I just have some questions,
1, what where does the number 57,2958 come from and what does it mean?

I think I can adapt this to what I need.
First, my table has following structures:
str_num varchar(25),
name varchar(50)
x_the_geom --for lon
y_the_geom --for lat

the str_num would be street number and name would be street name.
so taken together would be 1366 windy street or drive or road or whatever; which means str-num and name are usually concatenated.

I will make some mods to this code and if I am still stumped, I would be back, you guys don't mind.

ditch: your code is beautiful and efficient.
It is just too complicated for me right now; perhaps when I take another look, it will make more sense.
Urgent need for solutions to this problem is forcing me to abandon it for now.

BTW, thanks.
I didn't realize I have become a vet.
Quite frankly, I get bounced around so much from technical space to another that I am not given enough time to mature in one space.
For instance, I have been moved from oracle to sql server to asp to php to postgres and back to sql server.
Not enough time to become an expert in one space.
Any, I really appreciate you guys coming through for me and host of others in this forum.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-17 : 14:30:23

quote:
Originally posted by simflex

what where does the number 57,2958 come from and what does it mean?

It is 57.2958, which is a constant to convert degrees to radians.

http://distance-calculation.com/HTML5/global-distances-2.asp

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-17 : 15:16:59

if you want to be really really really accurate:

select 180 / pi()

returns 57.295779513082323

Duane.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-03-22 : 11:21:18

Ok ehorn,
I have been working this code sample you did and try to adapt to my needs, needless to say, I am having problems.

Below is the code I am working with but first, a bit of correction in my earlier post.

Earlier, I gave this as the table structure:

First, my table has following structures:
str_num varchar(25),
name varchar(50)
x_the_geom --for lon
y_the_geom --for lat

Infact the structure looks more like this:

str_num varchar(25),
name varchar(50)
the_geom -- this holds the x/y coordinate values.

in postGIS, the x and y of each point shape is usually obtained using the x(geometry) and y(geometry) functions.
However, in our database, the geometry is already in the field called the_geom, so to get the x and y values for each point we use it like this: x(the_geom) and y(the_geom).

Having said that, in your code(ehorn's), I changed the field long to x(the_geom) and lat to y(the_geom).

We would like to use x/y coordinate pair as input params since that is what the user enters in order to obtain the address closest to the x/y point.
Also, I am having problem understanding where the field MileRadius is coming from giving that that field is on the sample table on your post.

Below is my code and any help, as usual, will be greatly appreciated.
I am really stumped on this problem.

CREATE PROC up_AddressWithinRadius

@address varchar(100) ,
@GivenMileRadius int

AS
SET NOCOUNT ON

DECLARE @lat1 float,
@long1 float

SELECT @lat1= y(the_geom),
@long1 = x(the_geom)
FROM roads.daccess
WHERE str_num||' '||name = @address

SELECT str_num||' '||name
FROM
(
SELECT str_num||' '||name,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(y(the_geom)/57.2958)) + (Cos(@Lat1/57.2958) * Cos(y(the_geom)/57.2958) * Cos((x(the_geom)/57.2958) - (@Long1/57.2958)))), 2)) /
((Sin(@Lat1/57.2958) * Sin(y(the_geom)/57.2958)) + (Cos(@Lat1/57.2958) * Cos(y(the_geom)/57.2958) * Cos((x(the_geom)/57.2958) - (@Long1/57.2958)))))) MileRadius
FROM roads.daccess
) a
WHERE a.MileRadius <= @GivenMileRadius
AND str_num||' '||name <> @address
ORDER BY MileRadius

GO

EXEC up_AddressWithinRadius '35085',20
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-22 : 17:08:00

Am I overly simple or doesn't something like this work:


create table coords (x decimal(9,4), y decimal(9,4))

go
insert into coords
select 10, 2 union all
select 13,23 union all
select 8, 23 union all
select 3,1 union all
select 7,8 union all
select 3,11 union all
select 2, 19


-- we want to find closest point to @x, @y
declare @x decimal(9,4);
declare @y decimal(9,4);

-- find within a radius of @r
declare @r decimal(9,4)


set @x = 10
set @y = 10
set @r = 9

select * from
(
select x,y,sqrt(POWER(@x - x,2) + POWER(@y-y,2)) as  Distance
from coords
) a
where Distance <= @r
order by distance ASC

go

drop table coords

Of course, it's a big table scan, but I believe that would be necessary .... you could add some "pruning" as a WHERE clause to the innermost query to exclude all coordinates which are WAY out of range -- for example (I think this is logically sound but haven't totally "proofed" it):


WHERE x between (@x - @r) and (@x + @r) AND
      y between (@y - @r) and (@y + @r)

that should work, since if x is outside of the range of (@x-@r) to (@x+@r), the distance to @x,@y must be at LEAST @r .... and it would make your search MUCH quicker and allow for the use of indexes.

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-22 : 18:42:52

quote:
I am having problem understanding where the field MileRadius is coming from giving that that field is on the sample table on your post

This is a calculated value based on the derived table query (the inner query). In that example the request was for all matching values within a given distance so it was calculated for comparison.

If you are having trouble working it up to meet your needs than Jeffs example should provide you a better base to build from. Also if you are just trying to determine the closest address to the given coordinates then Jeffs query could be easily modified to only select the top 1:

select top 1 * from
(
select x,y,sqrt(POWER(@x - x,2) + POWER(@y-y,2)) as  Distance
from coords
) a
where Distance <= @r
order by distance ASC

Though realize that Jeffs equation is for two points in a plane, The example given in the link uses spherical distance and applies to two points on the earth (when dealing with lat and long).

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-03-23 : 10:16:26

thanks jeff and ehorn.

see I am looking for closest point.
I am looking for closest address to a point.
For instance, if I have 2 addresses, 121 Sprite Dr and 141 Angle road and they are both within x=2227284.1963667543 and y=1435389.6730164126, what I would like to do is a search that should either return 121 Sprite Dr or 141 Angle road depending on which of these 2 is closest to the x/y coordinate pair above.

I think I have gotten away from sql too long that I can't seem to get my thoughts together anymore.
I tried adapting this code thus:

select top 1 * from
(

select str_num+' '+str_name,long,lat,sqrt(POWER(86.583234 - long,2) + POWER(34.303718-lat,2)) as Distance
from addresses
) a
where Distance <= 3959
order by distance ASC

but it keeps blowing up.

No column was specified for column 1 of 'a'.

Sorry folks if I appear too cheap.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-03-23 : 10:18:34

Just one more point I forgot to point out.
The str_num str_name fields would give street number and street name.
So str_num would be 121 and str_name would be Sprite Dr

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-23 : 10:40:12

The error message tells you the exact problem. No column name was specified for the first column in "a". Thus, you need to specify one.

You need to change

select str_num + ' ' + str_name

to

select str_num + ' ' + str_name AS Address

to give the first column an alias. Hopefully that makes sense.

- Jeff

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-03-23 : 11:15:16

hi Jeff,
It made sense, thank you.
Actually, it works but unfortunately, it is not returning the correct address.

Here is an example:
I pass the code the @lon value of 86.060987999999995 and @lat value of 34.303718 and the address associated with these values is 1543 Davie Cirlce but below is what is being returned

Address @lon @lat
141 Pryor Str -86.060987999999995 32.941707999999998

Distance
172.64959444269016

Although, I am only really interested in displaying the address but the result being displayed is not matching up with my input params.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-23 : 12:08:27

please give us some sample data and what you are trying to return. i.e., give us a smapling of your master table of coordinates, and then the SQL you are running to return the match, and what you hope/expect to return based on your sample data. Please provide it in the form of CREATE TABLE and INSERT statements.

- Jeff

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-03-23 : 13:24:31

hi Jeff!
I have actually gotten it to work using some non ansi standard query:

select
str_num||' '||name AS Address,
distance(the_geom,'POINT(2160032.0 1332752.0)'::geometry) as dist
from addresses order by dist limit 1

I thank you, ditch and of course ehorn for continued help to all of us.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources