asked
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.
No.. I am not using spatial data type instead lat long are "real" data type. What would be better approach to handle this kind of situation. I am getting Lat Long values as float from the History table and need to find the corrosponding Address from the second table which stores Lat Long (real) and address (varchar).
In terms of speed, you're kind of in trouble. You either have to run calculations like you've done, or, you'll have to cast the lat/lon into spatial data type and then use the spatial functions. But, because you're casting the data you can't take advantage of spatial indexes. Because you're running all those calculations, you can't use regular indexes. Best bet, either convert your data into spatial in place, or add a calculated column that is spatial. Then put a spatial index on it and use spatial functions that can take advantage of the index.
15 People are following this question.
