SELECT
* , SQRT(POW(69.1 * (latitude-'49.1044302'), 2) + POW(69.1 * ('-122.801094'-longitude) * COS(latitude / 57.3), 2)) AS distance
FROM stops< br />HAVING distance <5
ORDER BY distance limit 100
But currently this method will slow down under high load. Some queries take 20 seconds to complete.
If anyone knows any better optimization method, it would be great.
Using a comparison with ST_Distance or creating a geometry with a radius of interest as well as ST_within may give you good results and may be much faster than current. However, the best way to achieve this is and The fastest way, ST_Dwithin has not been implemented in mysql.
Currently I have several tables with 100k rows. I am trying to find the following data.
SELECT
*, SQRT(POW(69.1 * (latitude-'49.1044302'), 2) + POW(69.1 * ('-122.801094'-longitude) * COS( latitude / 57.3), 2)) AS distance
FROM stops
HAVING distance <5
ORDER BY distance limit 100
But currently this method will Slow. Some queries take 20 seconds to complete.
If anyone knows any better optimization method, this would be Great.
First of all, if you have a lot of geospatial data, you should use mysql’s geospatial extension instead of calculations like this. Then, you can Use create spatial indexes to speed up many queries, and you don’t have to write long extraction queries as described above.
Use a comparison with ST_Distance or create a geometry with a radius of interest and ST_within may give you Brings good results, and may be much faster than the current one. However, the best and fastest way to achieve this goal, ST_Dwithin has not yet been implemented in mysql.
p>