0

I have question about spacial sql query using MySql; I have a table with latitude and longitude colums; I would to get all points nearest by my geoPosition using my distance (radius). I tried this query:

SELECT
    id, (
      6371 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;
  • Is this query a good way about the performances?
  • How can I change this query if I would consider an square that contains the points? is preferable to use a square?
Safari
  • 11,437
  • 24
  • 91
  • 191

2 Answers2

0

You can use a simple map projection and straight distances for example equirectangular projection. In the formula on this website you can also use a simplier formula without the square root:http://www.movable-type.co.uk/scripts/latlong.html. Of course you can use a bounding box to filter the query:How to calculate the bounding box for a given lat/lng location?, https://gis.stackexchange.com/questions/19760/how-do-i-calculate-the-bounding-box-for-given-a-distance-and-latitude-longitude.

Community
  • 1
  • 1
Micromega
  • 12,486
  • 7
  • 35
  • 72
0

I can't speak on the subject of performance in MySQL but when working on the same problem in SQL Server I build a box based on my distance than I can limit results quickly in WHERE clause by simply verifying for Lat and Long to be within boundaries. At that point it becomes simple arithmetic comparison. After I have narrowed down number of results at that time I check the Distance from my original point.

The whole point of building a box first is to reduce number of records that are involved in complex mathematical calculation.

Here is my example from SQL Server.

DECLARE @Lat DECIMAL(20, 13) = 35.7862
   ,@Long DECIMAL(20, 13) = -80.3095
   ,@Radius DECIMAL(7, 2) = 5
   ,@Distance DECIMAL(10, 2)
   ,@Earth_Radius INT = 6371000;

SET @Distance = @Radius * 1609.344;

DECLARE @NorthLat DECIMAL(20, 13) = @Lat + DEGREES(@distance / @Earth_Radius)
   ,@SouthLat DECIMAL(20, 13) = @Lat - DEGREES(@distance / @Earth_Radius)
   ,@EastLong DECIMAL(20, 13) = @Long + DEGREES(@distance / @Earth_Radius / COS(RADIANS(@Lat)))
   ,@WestLong DECIMAL(20, 13) = @Long - DEGREES(@distance / @Earth_Radius / COS(RADIANS(@Lat)));

SELECT *
    FROM CustomerPosition AS cp
    WHERE (
            cp.Lat >= @SouthLat
            AND cp.Lat <= @NorthLat )
        AND (
              cp.Long >= @WestLong
              AND cp.Long <= @EastLong )

Results of original select I store it into temp table and than evaluate it to see which ones actually Within specified distance.