# Finding all agents in database from longitude and latitude

Hi,
I have a registration form where users register as either staff or employer, when they register, their longitude and latitude values get automatically populated in my database.  What I need to now do is:

When someone logs in they automatically see all other staff within a 10 mile radius of their longitude and latitude.

How can I achieve this?

Many thanks

Damian
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I will give you a idea how it will work you can make the exact thing then:
1. read the values of \$lat and \$lon from the user where he is.
2. make the sql like below.
3. 'C' is the constant value which you can calculate and put directly. How?
i. find the relation how lat,lon actuate in real distances, or
ii. take lat,lon of two points on map at 10 miles apart and calculate:
C = ((lat1 - lat2) * (lat1 - lat2) + (lon1 - lon2) * (lon1 - lon2);

SELECT * from users WHERE ((lat - \$lat) * (lat - \$lat) + (lon - \$lon) * (lon - \$lon)) > C;

i belive you can take forward from here
Commented:
I think that "liveaspankaj" got it right, but you need to use C*C, not just C, and you need to use '<', not '>'.

Let's imaging that you have table `USERS` with fields `username`,`longitude`,`latitude` and logged in user has  longitude=\$user_longitude, latitude=\$user_latitude, and you are looking for neighbours in radius \$max_user_distance. SQL query will look like:

``````\$sql='SELECT username
FROM `USERS`
WHERE
(
(longitude-'.\$user_longitude.')*(longitude-'.\$user_longitude.')
+
(latitude-'.\$user_latitude.')*(latitude-'.\$user_latitude.')
)<'.(\$max_user_distance*\$max_user_distance).';';
``````

Commented:
yes its < and > thanks XzKto.

but C*C is again a Constant :) so why give that burden to the sql server :) we can calculate and give it directly. if you dont like C we can make it D :) where D = C * C :)
Commented:
Err:

yes its < and NOT > thanks XzKto.

but C*C is again a Constant :) so why give that burden to the sql server :) we can calculate and give it directly. if you dont like C we can make it D :) where D = C * C :)
Author Commented:
That's great code people, I'm trying to add to my form.  Could anyone show me how to have a drop down in php with say:

10
20
30
40
50

(which is the miles), and then integrate this into my SQL search?

Many thanks

Damian
Commented:
Following is an approx code:
I have left the db connection and fetch for you. You also have to fill the actual user's co-ordinates:

``````<?php
if(isset(\$_GET['submit'])){
\$miles = \$_GET['miles'];
\$miles_latlon = 0.00020882756 * \$miles * \$miles; //Approx Value
\$user_longitude = 41;//'fill actual
\$user_latitude = -71;//fill actual

FROM `USERS`
WHERE    ((longitude-'.\$user_longitude.')*(longitude-'.\$user_longitude.')
+
(latitude-'.\$user_latitude.')*(latitude-'.\$user_latitude.'))<'. \$miles_latlon).';';

//Connect to DB and fetch result

}

?>
<form method='get">
<select name="miles">
<option value="10"></option>
<option value="20"></option>
<option value="30"></option>
<option value="40"></option>
<option value="50"></option>
</select>
<input type="submit" name="submit" value="submit"/>
</form>
``````
Commented:
Correction:

``````<?php
if(isset(\$_GET['submit'])){
\$miles = \$_GET['miles'];
\$miles_latlon = 0.00020882756 * \$miles * \$miles; //Approx Value
\$user_longitude = 41;//'fill actual
\$user_latitude = -71;//fill actual

FROM `USERS`
WHERE    ((longitude-'.\$user_longitude.')*(longitude-'.\$user_longitude.')
+
(latitude-'.\$user_latitude.')*(latitude-'.\$user_latitude.'))<'. \$miles_latlon).';';

//Connect to DB and fetch result

}

?>
<form method='get">
<select name="miles">
<option value="10">10</option>
<option value="20">20</option>
<option value="30">30</option>
<option value="40">40</option>
<option value="50">50</option>
</select>
<input type="submit" name="submit" value="submit"/>
</form>
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Author Commented:
Hi,
I have added this to my code:

\$sql='SELECT *
FROM `members`
WHERE    ((longitude-'.\$user_longitude.')*(longitude-'.\$user_longitude.')
+
(latitude-'.\$user_latitude.')*(latitude-'.\$user_latitude.'))<'.(\$miles_latlon).';';

if (!mysql_query(\$sql))
{
die('Error: ' . mysql_error());
}

echo \$sql;

However, in the echo of the SQL return, this is all I see:

SELECT * FROM `members` WHERE ((longitude--3.0134983)*(longitude--3.0134983) + (latitude-53.8306789)*(latitude-53.8306789))<0.020882756;

It just contains the select statement with all my values?  What I need it to do is list the people i.e. firstname.

What have I done wrong?

Regards,

Damian
Commented:
See the comment on line 14:

//Connect to DB and fetch result
Author Commented:
Ray,
That's what this line is for:

\$sql='SELECT *
FROM `members`
WHERE    ((longitude-'.\$user_longitude.')*(longitude-'.\$user_longitude.')
+
(latitude-'.\$user_latitude.')*(latitude-'.\$user_latitude.'))<'.(\$miles_latlon).';';

if (!mysql_query(\$sql))
{
die('Error: ' . mysql_error());
}

echo \$sql;

???
Commented:
this link give info on how to connect to db and fetch the results

http://www.webcheatsheet.com/PHP/connect_mysql_database.php
Commented:
The first code snippet in the article shows how to connect, select and query a data base.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html

See lines 75 through 90.
Senior Software EngineerCommented:
Hai,

Just find the attachment and go throw on "GoogleApi.class"

you can get different ideas to make the map more better,

GoogleMapAPI() function is the main one.
Author Commented:
liveaspankaj,
There seems to be an issue with your code......  if I take the SQL statement:

FROM `USERS`
WHERE    ((longitude-'.\$user_longitude.')*(longitude-'.\$user_longitude.')
+
(latitude-'.\$user_latitude.')*(latitude-'.\$user_latitude.'))<'. \$miles_latlon).';';

it doesn't return any values, so I manually entered the values for the longitude and latitude, as well as the distance on the end and it works, both in SQL directly, and in PHP, however as soon as I change the :

'.\$user_latitude.'

it starts to fail.  I can confirm this variable does have a value.

How can I output the SQL string to see what's being formed?

Thanks
Commented: