Problem solve Get help with specific problems with your technologies, process and projects.

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

Dig Deeper on Microsoft SQL Server Installation

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.