Please help. I am trying to select records from a MySQL database table based on a radius. Users enter an address and a radius in a form and are received in $_POST. I can successfully determine the latitude and longitude of the submitted address. The variables are $lat1 and $long1, and the radius is $radius. The database table has columns named "lat" and "long" for the latitude and longitude of each record. I can calculate the distance between to sets of latitudes and longitudes successfully using the code below.

PHP Code:

$distance = (3958*3.1415926*sqrt(('$lat1'-lat)*('$lat1'-lat) + cos('$lat1'/57.29578)*cos(lat/57.29578)*('$long1'-long)*('$long1'-long))/180) 


My existing code below works:

PHP Code:

$resultsrchdate = "SELECT date FROM wamuexp where date >= '$date1' && date <= '$date2' && state = '$selectstate' ";
$sql_resultsdate = mysql_query($resultsrchdate,$dbc); 


What I need to do is somehow incorporate the formula for calculating distance into the SELECT statement of my existing code, or a loop might be needed, so only records that are less than or equal to the radius are returned. Thanks in advance.

You don't specify your db, but I assume mysql:

create table geo (lat float, lng float);
insert into geo values (46.7999999. 7.1500000);
insert into geo values (46.81321897604, 7.3424720);

SELECT ACOS(SIN(src.lat) * SIN(dest.lat) + COS(src.lat) * COS(dest.lat) * COS(dest.lng - src.lng)) * 3956 AS distance, src.lat, src.lng, dest.lat, dest.lng
from geo as src
  cross join geo as dest
where src.lat!=dest.lat
and src.lng!=dest.lng

+-----------------+---------+---------+---------+---------+
| distance        | lat     | lng     | lat     | lng     |
+-----------------+---------+---------+---------+---------+
| 725.18610599125 | 46.8132 | 7.34247 |    46.8 |    7.15 |
| 725.18610599125 |    46.8 |    7.15 | 46.8132 | 7.34247 |
+-----------------+---------+---------+---------+---------+
2 rows in set (0.00 sec)

Or you could take a look at mysql spatial functions:
http://dev.mysql.com/doc/refman/5.0/...xtensions.html

Never used them, though.

Thanks for the reply. As I'm a noob, it will take a little time to understand the code you sent. At 1st glance, it looks like I may not have been clear. I want to select only records that are less than or equal to a radius from a address that is input from a form by a user. Each record in the database contains its latitude ("lat") and longitude ("long") along with other fields (or columns). From the user input of address, and radius, I derive the variables of $lat1 and $long1 for the geocode of the user's address, and $radius for the radius. I have the code to calculate the distance (see previous post). Where I am stuck is, how to put these together to construct the proper query. I'm thinking a code similar to this:

$result = mysql_query("SELECT * , (CODE TO CALCULATE DISTANCE) as radius FROM tablename HAVING radius <= '$radius' WHERE field1 = '$var1' AND field2 > '$var2' ");
$num_rows = mysql_num_rows($result);

The (CODE TO CALCULATE DISTANCE) is where lat, long, $lat1, $long1, and $radius are used to compute the distance.

Thanks again for any help.

This is the code I am trying to get to work:

PHP Code:

$resultsrch = mysql_query("select *, (((acos(sin(('.$lat1.'*pi()/180)) * sin((lat*pi()/180))+cos(('.$lat1.'*pi()/180)) * cos((lat*pi()/180)) * cos((('.$long1.'- long)*pi()/180))))*180/pi())*60*1.1515) as radius from wamuexp HAVING radius <= '.$radius.' where date >= '$date1' AND date <= '$date2' AND state = '$selectstate' AND units >= '$units1' AND units <= '$units2' ORDER BY date");
$num_rowssrch = mysql_num_rows($resultsrch); 


When I run it, I get the following warning:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/gedani2/public_html/askdata/multi/testbody.php on line 108

Thanks again for your help on this.

Really, you ought to look at the spatial extension...
This is exactly what they are mean to be.
The problem with your method, is that no indexes will be used, as the db will need to compute for each rows of your table the distance from a surface.

The spatial extension allows you to create a special index on a lat/long object, and to use this index in a query where you ask if the point is in the surface of your reference point+radius.
On postgresql with postGis (the spatial functions) the faq says:

3.7.What is the best way to find all objects within a radius of another object?

To use the database most efficiently, it is best to do radius queries which combine the radius test with a bounding box test: the bounding box test uses the spatial index, giving fast access to a subset of data which the radius test is then applied to.
The ST_DWithin(geometry, geometry, distance) function is a handy way of performing an indexed distance search. It works by creating a search rectangle large enough to enclose the distance radius, then performing an exact distance search on the indexed subset of results.
For example, to find all objects with 100 meters of POINT(1000 1000) the following query would work well:

SELECT * FROM geotable
WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0);

I just started looking into spatial functions for postgres yesterday evening, as I need it in a fore coming project.
I'll keep you posted about it's result.