[lucianocn]

I have an API that response to the user a list of places registered in MySQL
The API returns ordered with distance based on the coordinates of the user, each entry in the database.
My current database has around 200 entries of places.

Regarding performance: It would be better the API returning all databases entries with the coordinates latitude and longitude (with cache) and make the calculation of distance through the mobile app or the calculation be made directly in MySQL Select and use pagination (so this way would not have how to make cache, as each user have a different response)?

Thanks

[Keith]

If the database isn't heavily loaded, it's probably best to do the calculation there. Either way would work fine given how few records you're dealing with.

How many different places do you intend for your database and application to support?

[lucianocn]

Keith, on 08 October 2012 - 03:27 PM, said:

If the database isn't heavily loaded, it's probably best to do the calculation there. Either way would work fine given how few records you're dealing with.

How many different places do you intend for your database and application to support?

Exactly my point of view, we are calculating an increase around 200 places per month in our database.
A possible solution: Add to to the response of the API filtered by City, so it will reduce the load, and continue calculating the distance on the mobile app.

What do you think?


Thanks

[Keith]

Yes, if you can limit the relevant data in some way by filtering on other criteria it'll help to keep either data transfer or processing time down. Again, you'll need to decide the best course of action based on your use cases and expected load.

If you can keep the amount of data that you send to a minimum, sending the data to the client for processing seems reasonable. At a bare minimum, the place id and the long and lat could be sent in a very compact representation.

[oxigen]

lucianocn, on 08 October 2012 - 03:11 PM, said:

I have an API that response to the user a list of places registered in MySQL
The API returns ordered with distance based on the coordinates of the user, each entry in the database.
My current database has around 200 entries of places.

Regarding performance: It would be better the API returning all databases entries with the coordinates latitude and longitude (with cache) and make the calculation of distance through the mobile app or the calculation be made directly in MySQL Select and use pagination (so this way would not have how to make cache, as each user have a different response)?

Thanks

Quote

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Details "Finding Locations with MySQL"
https://developers.g...search_v3?hl=vi

More information:
http://en.wikipedia....versine_formula<h3 style="margin: 1.5em 0px 1em; padding: 0px; border: 0px; vertical-align: baseline; "></h3>

[lucianocn]

oxigen, on 08 October 2012 - 09:31 PM, said:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Details "Finding Locations with MySQL"
https://developers.g...search_v3?hl=vi

More information:
http://en.wikipedia....versine_formula<h3 style="margin: 1.5em 0px 1em; padding: 0px; border: 0px; vertical-align: baseline; "></h3>

Yeah I was using that to calculate the distance directly in the database, but the point of this topic is:
Is better calculate the distance using Mysql (no cache possible) or in the mobile device (returning lat and long at api) with cache?