Ask the Expert

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!

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: