We are developing a city guide type of native iPhone app to find nearest restaurants, hotels etc to the user. User location is detected via iphone GPS. In previous version all of our 1000 records resided in the app code therefore it was easy to sort them by proximity in the code and show the nearest restaurants etc on screen. We now are adding webservices to fetch records from our server/cms. The app will still detect user location. Can anyone please please tell me how I can sort records by proximity on the server and then retrieve and show 20 closest results at a time on iphone. We have about 1000 records, many of them have images therefore we cant download/sync all the records at once because it will take too long. We have been downloading 20 records at a time and sorting them on iphone and then displaying the results on screen. Maybe there is a script or a code someone can suggest to run on the server so the proximity sorting can be done on the server "on the fly" before 20 closest records are fetched at a time.
Last edited by econdev; 03-03-2011 at 01:07 PM.
Reason: clarifying
Oracle, for example, has a location type (lat & long) as a base type. So you can index on it and run very fast queries against it based on distance, etc. Pretty sweet.
With some other DBs (not up on the latest MS SQL Server, so going with older MSSQL) you can define custom data types that will let you do the same thing. Speed there is dependent on how brilliant a programmer you've got.
For even simpler DBs ... well, clearly you're storing lat & long so start there. Each degree of lat is like 70 miles in the US (roughly). So pull all the records +/- 1 degree of lat. Longitude is worse, but you could still pull +/- 1 degree (experiment - maybe vary with input lon). Then have your web service sort by distance - it'll both be faster than asking your DB engine to do so and you'll have more control over epsilon (the amount of error you are willing to tolerate).
Alternately (this may be a better solution) grid up your DB - each entry includes a min & max latitude/longitude for which it responds to. Then it's one query you can immediately pass back and the phone can sort on the fly (like if they're driving). You could even do it as a two query - first on a grid id, then pull all the records that have that grid id. This would allow locations along an interstate to appear if the starting location is an interstate even if it's outside a "local" search. This latter method is more complicated though
pass in user location to the server and have the server figure out distance from user to places you are returning and sort it there. no need to do that work on the phone.
I'm highly averse to asking the database to perform calculations, particularly trigonometric functions.
Now, before the haters start, with 1000 records it's all good. Nobody will care about having to wait a few extra milliseconds. Heck, even cranky old hardware will be able to blip through that query quick enough.
Things get worse with scale. Your query does a full table scan every time ... no worries with 1000 records, but we didn't get an upper bound. Maybe a where clause to limit the records pulled, but still, all that trig inside a query makes my stomach churn
I'd push the calcs and final sorting into the php. Don't know the speed of the trig, but I know php does numeric sorts crazy-fast.
Thanks everyone with great suggestions, what would the php code look like. Also would would to hear more suggestions from other fellow experts here. Is anyone aware of any tool or script that does this.
The problem with PHP doing the calculations is that you would have to fetch every record that is in the database. With the above query, all your getting back are the points that are within 1KM of your location.. even with 10 million records in your database, this query will execute in the matter of milliseconds..
The problem with PHP doing the calculations is that you would have to fetch every record that is in the database. With the above query, all your getting back are the points that are within 1KM of your location.. even with 10 million records in your database, this query will execute in the matter of milliseconds..
you wouldn't have to fetch every record. you could just do the math in php and pass the result into sql. I agree that it makes me a bit twitchy to see that bit of math in a select.
you wouldn't have to fetch every record. you could just do the math in php and pass the result into sql. I agree that it makes me a bit twitchy to see that bit of math in a select.
So how would you know wich object is at what distance? Could you provide a sample query because I thought this was the most effective/best way to do it. If I am wrong, i would love to know.
So how would you know wich object is at what distance? Could you provide a sample query because I thought this was the most effective/best way to do it. If I am wrong, i would love to know.
I don't think there is really anything wrong with what you are doing. For me it was more of a conceptual thing for not wanting to make the select too complicated. I just like my search queries to have all the data they need without needing to calculate anything (when possible)
I don't have time to write a script and I have never worked on this problem so i'd be suspect of anything I wrote on the fly anyway. But the distance you calculate is just done once and based on the users lat/lng and a set radius you want to search in. I just thought you could do that outside the select.
How do i sort & show results by proximity
Linear Mode
