Search code examples
mysqlgoogle-mapsgoogle-fusion-tables

How do I calculate distance using a Google Maps Fusion Table?

I am working with Google Map Fusion Tables and recently faced a tough problem while getting required data.

I am using below query:

SELECT geometry, ZIP, latitude, longitude,( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM 18n-gPzxv_usPqtFJka9ytDArJgi3Hh8tlGnfuwrN WHERE distance <= 25

But the query is returning "could not parse query" error. I also tried below query but i got same error.

SELECT geometry, ZIP, latitude, longitude FROM 18n-gPzxv_usPqtFJka9ytDArJgi3Hh8tlGnfuwrN WHERE ST_DISTANCE(LATLNG(24.547123404292083, -114.32373084375001), LATLNG(37.4,-122.1)) <= 25

I can't calculate the distance after fetching all of the records. it's like millions of records (table size as of now is 10MB). I need a solution like just as we could fetch rows from a MYSQL table using spatial function like ST_DISTANCE or using the distance formula.

If any one could help giving some alternate or some out of the box solution, it would be awesome :)

Solution

    1. You can't use functions like acos or sin in a FusionTable-Query, the supported functions are aggregrate-functions:COUNT SUM AVERAGE MAXIMUM MINIMUM

    2. ST_DISTANCE expects the first argument to be the name of a Location-column

    3. ST_DISTANCE may not be used in a WHERE-clause, it's only supported in ORDER BY

    summary: what you are trying to achieve is (currently) not possible with a FusionTable-Query