Reverse Geo Address based on LatLong from local database table
We are using SQL Server 2008 web edition. We have a table named "tbl_geodata" which has address and latitude and longitude values. We also have a "History" table which has only latitude and longitude values including other informations. What we need is like following... 1. We get a set of records based on a query from "Histroy" (lat long values), say 5000 records 2. Now we are using the following formula to calculate address from the "tbl_geodata" for each row (5000 rows). SELECT top 1 geo_street,geo_town,geo_country,( 3959 * acos( cos( radians(History.lat) ) * cos( radians( gps_latitude ) ) * cos( radians( gps_longitude ) - radians(History.long) ) + sin( radians(History.lat) ) * sin( radians( gps_latitude ) ) ) ) AS distance FROM tbl_geodata ORDER BY distance Currently we are seeing high cpu utilisation and performance issue. Can anyone please suggest the improvement steps?
Because you're doing a calculation and then using that in an ORDER BY clause, you're probably going to be seeing scans. Add to that the fact that you don't have any kind of WHERE clause, you're just going to scan the entire table, every single time you access it. Look at what the execution plan is telling you the optimizer is doing. Instead of using calculations for this, assuming you're storing your data as actual spatial data types, I'd use the spatial functions to find matches. With those you can use spatial indexes to make a difference.