FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> Thanks for all the help guys.  Here's a more detailed 
> description of my 
> problem.
> 
> I get a current latitude, longitude value into my program, 
> what i have 
> to do then is compare it with my table( called PostCodes) 
> which has the 
> 2.4 million records to find the record im looking for.  What 
> i mean by 
> this is bit hard to explain but here goes.
> 
> Table PostCodes columns include:
> PlaceName, Town, Country, Latitude, Longitude, SweepRadius.
> 
> When i get my lat lon values i have to do a number of things, 
> i call a 
> function called LatLonDistance which takes my current lat lon 
> values and 
> works out the distance between the lat lon values in each Postcodes 
> record(means i have to go into each record and work out the 
> distance), 
> if the distance is less than the SweepRadius this is the record im 
> looking for, once found i do an insert into another table and call 
> 'return NO_MORE_RESULTS' in order to stop looping around the 
> PostCodes 
> table.
> 
> I cant see any other way to do this query because i need to call 
> LatLonDistance on each record until i find the one i want.
> 

This is a spacial problem. A query like

select ... from PostCodes where 
        lon >= start_lon and lon <= end_lon and lat >= start_lat and lat
<= end_lat

This query should reduce search area.
There are also some solution that allow you to index your lon/lat data
(which Oracle for instance should implement natively). 

However this is just a workaround for this particular case. Current CVS
implement a much better dbcancel...

freddy77