Q

Query to find cities, including nearby locations

I'm trying to generate a code within each field under the "Query" column that searches through the entire table, looking for neighboring cities to the city that is in question (say to the nearest 1 degree of longitude and latitude) and display the output as shown under the "Query" column. I need to be able to accomlish this automatically, since my table of existing cities, long and lat. is huge. If you have any suggestions, your input would be MUCH APPRECIATED!

This should just be basically like the following:

 select tab1.* from a tab1 cross join (select lat+1 maxlat,lat-1 minlat,long+1 maxlong,long-1 minlong from a where city = 'somecity') tab2 where tab1.lat between tab2.minlat and tab2.maxlat and tab1.long between tab2.minlong and tab2.maxlong
I would also recommend grabbing books by Joe Celko, Itzik Ben-Gan/Tom Moreau and Ken Henderson. All of them have some very good examples of advanced queries and things like this type of proximity query.

 

For More Information

This was first published in June 2002

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close