CODEWITHSUNDEEP
google-maps-api-3
user1865154
user1865154

Reputation:

SQL Great Circle Distance with fusion table

I have difficulties with the search location radius function using with geocoder and a fusion table where column X and Y store long lat. Actually the problem is in the WHERE condition of the query. Thank you if you can debug. This is my function:

function CP_query() {
  CPsearch = document.getElementById('CP_Input').value;
  geocoder.geocode( { 'address': CPsearch}, function(results, status) {
    if (status == google.maps.GeocoderStatus.OK) {
      mylat = results[0].geometry.location.lat();
      mylon = results[0].geometry.location.lng();
      //alert("lat : " + mylat + "long: " + mylon);
      layer_imm = new google.maps.FusionTablesLayer({
          query: {
              select: 'geometry',
              from: '1ckqpE8ZxVzfgJ5bT-vAkpBwjVANbV-KiNjfScjw',
              where: '6371 * acos( cos( radians(' + mylat + ') ) * cos( radians( Y ) ) * cos( radians( X ) - radians(' + mylon + ') ) + sin( radians(' + mylat + ') ) * sin( radians( Y ) ) ) < 2'
           }
       }); 
       layer_imm.setMap(map);
       map.setCenter(results[0].geometry.location);
       var marker = new google.maps.Marker({
       map: map,
       position: results[0].geometry.location
       });
   } else {
     alert("Geocode was not successful for the following reason: " + status);
   }
 });
}

Upvotes: 1

Views: 187

Answers (1)

geocodezip
geocodezip

Reputation: 161334

Use ST_INTERSECTS

3218 meters is the radius of the circle.

query: {
  select:'geometry'
  from:  '1ckqpE8ZxVzfgJ5bT-vAkpBwjVANbV-KiNjfScjw',
  where: 'ST_INTERSECTS(geometry,CIRCLE(LATLNG('+ mylat + ',' + mylon + '), 3218))'
}

Working Example

Upvotes: 1

Related Questions