I need a bit of help, I've tried several examples out there and i'm not able to get any of them to work.
I have a table with Name,Lat,Lon in it, i have a search page that allows a user to select within how many "miles" they would like to show other users who are in the database based on this users specific Lat/Lon, so i need a SQL statement that will return results within say 2500 miles based solely on Lat/Lon.
The key here is my database is MSSQL not MySQL and i haven't been able to get any of the MySQL examples to cross over to MSSQL. I don't think it matters but i'm use PHP on the server side to collect form data and post to MSSQL and returning a result from a function.
Inputs: Lat(26.631903), Lon(-80.133376), Distance(2500)
DB DATA: Name column William, Latitude column 32.7827000, Longitude column -96.7758000
Output: Name, Distance from input Lat/Lon in miles
Ok figured it out, this does seem to work and isn't terribly slow
//users latitude
$lat = $_POST['lat'];
//users longitude
$lon = $_POST['lon'];
//search distance
$rad = $_POST['distance'];
//earth radius in miles use 6371 for km
$earth = 3959;
$SqlStr = "
SELECT Name, (".$earth." * ACOS(SIN(".$lat."/ CAST(57.2958 AS NUMERIC(10,4)))*SIN(lat / CAST(57.2958 AS NUMERIC(10,4)))+COS(".$lat."/ CAST(57.2958 AS NUMERIC(10,4)))*
COS(lat / CAST(57.2958 AS NUMERIC(10,4)))*COS(lon / CAST(57.2958 AS NUMERIC(10,4)) - CAST(".$lon." AS NUMERIC(10,4)) / CAST(57.2958 AS NUMERIC(10,4))))) as distance
FROM DB TABLE NAME
WHERE (".$earth." * ACOS(SIN(".$lat."/ CAST(57.2958 AS NUMERIC(10,4)))*SIN(lat / CAST(57.2958 AS NUMERIC(10,4)))+COS(".$lat."/ CAST(57.2958 AS NUMERIC(10,4)))*COS(lat / CAST(57.2958 AS NUMERIC(10,4)))*
COS(lon / CAST(57.2958 AS NUMERIC(10,4)) - CAST(".$lon." AS NUMERIC(10,4)) / CAST(57.2958 AS NUMERIC(10,4))))) <= ".$rad." ORDER BY distance
";