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
damianb123Asked:
Who is Participating?
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.

liveaspankajCommented:
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
0
XzKtoCommented:
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).';';

Open in new window


0
liveaspankajCommented:
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 :)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

liveaspankajCommented:
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 :)
0
damianb123Author 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
0
liveaspankajCommented:
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

$sql='SELECT username
        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>

Open in new window

0
liveaspankajCommented:
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

$sql='SELECT username
        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>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Ray PaseurCommented:
0
damianb123Author 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
0
Ray PaseurCommented:
See the comment on line 14:

//Connect to DB and fetch result
0
damianb123Author 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;

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

http://www.webcheatsheet.com/PHP/connect_mysql_database.php
0
Ray PaseurCommented:
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.
0
Vimal DMSenior 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.
GoogleMapAPI.class.php
0
damianb123Author Commented:
liveaspankaj,
    There seems to be an issue with your code......  if I take the SQL statement:

$sql='SELECT username
        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
0
Ray PaseurCommented:
If a map is what the asker wants, this article teaches how to do that.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3350-Using-the-Google-Maps-API-in-PHP.html

An example of using that technology is on this page:
http://landonbaseball.com/site_visitors.php
0
Ray PaseurCommented:
"How can I output the SQL string to see what's being formed?"

If the SQL string is in a varible named, for example, $sql you can use this:

var_dump($sql);
0
damianb123Author Commented:
Thanks very much everyone, all sorted now!  Great work.....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.