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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top