[anacy_nivas]

Hi,
I am doing destance search from GEO database query like following

SELECT *,(((acos(sin((46.6333*pi()/180)) * sin((`lat`*pi()/180))+cos((46.6333*pi()/180)) * cos((`lat`*pi()/180)) * cos(((14.5333- `lon`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM `geodb_coordinates` as as1 , geodb_textdata as as2 where as1.loc_id=as2.loc_id and as2.text_type=400300000 and text_locale='de' having distance < 200


i have totally 40500 records each table, result for this query is very slow to display , i am waiting long time but still i cannot get.. what is the problem here, please explain any one.

[wirehopper]

Way too much computation on the query.

Post the schema (describe geodb_coordinates).

My approach to this type of query (it was geolocation) was to compute out a square with the four corners that indicated the area within the query, then search for lat/long within those areas.

Something similar to

Code:

distance=miles converted to lat/long measurements
min_lat=target latitude-distance/2
max_lat=target latitude+distance/2
min_lon=target longitude+distance/2
max_lon=target longitude+distance/2
select from geodb_coordinates where lat>min_lat and lat<max_lat and lon>min_lon and lon<max_lon

In this case, the complex computation is only done once, and used to define the range of acceptable coordinates.

[End User]

Any chance that there is Zip code data attached to the Lat/Long coordinates? If so, there are a number of easy ways to do this. There's also this function I use:

PHP Code:

  function calculate_mileage($lat1, $lat2, $lon1, $lon2) {

 

      // used internally, this function actually performs that calculation to

      // determine the mileage between 2 points defined by lattitude and

      // longitude coordinates.  This calculation is based on the code found

      // at http://www.cryptnet.net/fsp/zipdy/

       

      // Convert lattitude/longitude (degrees) to radians for calculations

      $lat1 = deg2rad($lat1);

      $lon1 = deg2rad($lon1);

      $lat2 = deg2rad($lat2);

      $lon2 = deg2rad($lon2);

      

      // Find the deltas

      $delta_lat = $lat2 - $lat1;

      $delta_lon = $lon2 - $lon1;

    

      // Find the Great Circle distance 

      $temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2);

      $distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));



      return $distance;

   } 

[anacy_nivas]

i have attached schema please see the attachment.

[anacy_nivas]

I think using "calculate_mileage" function , i need to select all records (total 40500) then filter .