php/mysql zip code proximity search
Here is the best way I have found. Of course it will require that you have all of your zipcodes lat/lon encoded in the database.
// get all the zipcodes within the specified radius - default 20
function zipcodeRadius($lat, $lon, $radius)
{
$radius = $radius ? $radius : 20;
$sql = 'SELECT distinct(ZipCode) FROM zipcode WHERE (3958*3.1415926*sqrt((Latitude-'.$lat.')*(Latitude-'.$lat.') + cos(Latitude/57.29578)*cos('.$lat.'/57.29578)*(Longitude-'.$lon.')*(Longitude-'.$lon.'))/180) <= '.$radius.';';
$result = $this->db->query($sql);
// get each result
$zipcodeList = array();
while($row = $this->db->fetch_array($result))
{
array_push($zipcodeList, $row['ZipCode']);
}
return $zipcodeList;
}
You should be able to just drop in this function. Pass it the $lat and $lon of the zipcode you want the radius for, include the optional radius, and get a list of zipcodes back.
You could very easily modify this to get all users where zipcode IN (radius_sql) and get your list users back.
Happy Coding!
Sorting zip code proximity search by distance (php/mysql)
You could use something like
$iDistance = 20;
$iRadius = 6371; // earth radius in km
$iRadius = 3958; // earth radius in miles
$fLat = x.y; // Your position latitude
$fLon = x.y; // Your position longitude
$strQuery = "
SELECT
*,
$iRadius * 2 * ASIN(SQRT(POWER(SIN(( $fLat - abs(pos.lat)) * pi() / 180 / 2),2) +
COS( $fLat * pi()/180) * COS(abs(pos.lat) * pi() / 180) * POWER(SIN(( $fLon - pos.lon) *
pi() / 180 / 2), 2) )) AS distance
FROM user_zip_codes pos
HAVING distance < $iDistance
ORDER BY distance";
where you have to fetch your lat/lon value before using the SQL. This works for me
Find the nearest zip code location in PHP / MYSQL
See this Fastest Way to Find Distance Between Two Lat/Long Points
Basically you compute the distance to your base latlon, sort by shortest distance, and get the item on top. You can also do this in code if you don't want to bother having complex queries.
Keywords are "great circle distance" and "haversine formula"
EDIT
Let's say I have a database of vet clinics in LA
Limehouse Veterinary Clinic 34.1534500122 -118.3630599976
LA Pet Clinic 34.0838623047 -118.3284454346
Bestfriends Animal Hospital 34.1692466736 -118.3970489502
And my base coordinates is in hollywood lat 34.092324, lon -118.337122
haversine formula needs coords in radians instead of degrees.
There are 2 pi radians in 360 degrees so
y radians = 6.28319 * x degrees / 360 (solving for y)
The following is the haversine formula where r in 2 r arcsin is the earth's radius: 6,371,000 meters
-- haversine
-- latdif londif
-- 2 r arcsin [ sqrt[ sin^2(------) + cos(lat1) * cos(lat2) * sin^2(------) ] ]
-- 2 2
Therefore to get the nearest clinics near my location, I can use this query as a translation of the above formula
select
name,
2 * 6371000 * ASIN(
sqrt(
SIN(6.28319 * (34.092324 - lat) / 360 / 2) * SIN(6.28319 * (34.092324 - lat) / 360 / 2)
+
COS( 6.28319 * 34.092324 / 360 ) * COS( 6.28319 * lat / 360 )
*
SIN(6.28319 * (-118.337122 - lon) / 360 / 2) * SIN(6.28319 * (-118.337122 - lon) / 360 / 2)
)
)
as dist_in_meters
from locations ORDER BY dist_in_meters;
Giving me
name dist_in_meters
LA Pet Clinic 1234.3897096551932
Limehouse Veterinary Clinic 7204.075434291492
Bestfriends Animal Hospital 10177.689847331932
users near a specific zip code: PHP mysql
If You have zip code in $items then i think you don't need $itemsz for all customer zipcodes.
First you need to format $items array to like this :
$zips = (12345,12346,12347,12348)
Then just run a query like this in customer:
Select * from customer where customer.zipcode IN $zips
demo: https://eval.in/84652
SAMPLE code:
$items =array( '0' => array( 'zip_code' => 13121, 'latitude' => 43.483379, 'longitude' => -76.315044, 'city' => 'New Haven', 'state' => 'NY', 'county' => 'Oswego' ), '1' => array( 'zip_code' => 13126, 'latitude' => 43.465388 ,'longitude' => -76.342172 ,'city' => 'Oswego' ,'state' => 'NY', 'county' => 'Oswego' ) );
$strzipCodes="";
foreach($items as $item){
$strzipCodes .= $item['zip_code']." ,";
}
$strzipCodes = rtrim($strzipCodes,',');
$sql ="SELECT * FROM customer where zip IN(".$strzipCodes.")";
echo $sql;
// then execute query we will get all users inside those zipcodes
I think you get the idea.
Searching by Zip Code proximity - MySql
You should build a table that has each zip code with an associated latitude and longitude. When someone enters a zip and a distance, you calculate the range of latitudes and longitudes that fall within it, and then select all the zip codes that fall within that bounding box. Then you select any stores that have zip codes within that set, and calculate their distance from the provided zip and sort by it. (Use the haversine formula for calculating the distance between points on a globe)
If speed is your main concern, you might want to precompute all the distances. Have a table that contains a store zip code column, the other zip code, and a distance column. You can restrict the other zip codes to zip codes within a certain distance (say 100 miles, or what have you) if you need to cut down on rows. If you don't restrict the links based on distance, you'll have a table with > 700 million rows, but you could certainly do fast lookups.
MySQL Function to Determine Zip Code Proximity / Range
This nifty lil mySQL function that I wrote should definitely do the trick for you.
BEGIN
DECLARE x decimal(18,15);
DECLARE EarthRadius decimal(7,3);
DECLARE distInSelectedUnit decimal(18, 10);
IF originLatitude = relativeLatitude AND originLongitude = relativeLongitude THEN
RETURN 0; -- same lat/lon points, 0 distance
END IF;
-- default unit of measurement will be miles
IF measure != 'Miles' AND measure != 'Kilometers' THEN
SET measure = 'Miles';
END IF;
-- lat and lon values must be within -180 and 180.
IF originLatitude < -180 OR relativeLatitude < -180 OR originLongitude < -180 OR relativeLongitude < -180 THEN
RETURN 0;
END IF;
IF originLatitude > 180 OR relativeLatitude > 180 OR originLongitude > 180 OR relativeLongitude > 180 THEN
RETURN 0;
END IF;
SET x = 0.0;
-- convert from degrees to radians
SET originLatitude = originLatitude * PI() / 180.0,
originLongitude = originLongitude * PI() / 180.0,
relativeLatitude = relativeLatitude * PI() / 180.0,
relativeLongitude = relativeLongitude * PI() / 180.0;
-- distance formula, accurate to within 30 feet
SET x = Sin(originLatitude) * Sin(relativeLatitude) + Cos(originLatitude) * Cos(relativeLatitude) * Cos(relativeLongitude - originLongitude);
IF 1 = x THEN
SET distInSelectedUnit = 0; -- same lat/long points
-- not enough precision in MySQL to detect this earlier in the function
END IF;
SET EarthRadius = 3963.189;
SET distInSelectedUnit = EarthRadius * (-1 * Atan(x / Sqrt(1 - x * x)) + PI() / 2);
-- convert the result to kilometers if desired
IF measure = 'Kilometers' THEN
SET distInSelectedUnit = MilesToKilometers(distInSelectedUnit);
END IF;
RETURN distInSelectedUnit;
END
It takes originLatitude, originLongitude, relativeLatitude, relativeLongitude, and measure as parameters. Measure can simply be Miles or Kilometers
Hope that helps!
mySQL select zipcodes within x km/miles within range of y
You want to do something like this:
SELECT zipcode FROM zipcodes WHERE DistanceFormula(lat, long, 4.808855, 52.406332) < $range
It may be slow if your table of zip codes is large. You may also want to check out the geospatial extensions for MySQL.
Merge two MySQL query and get distance between two zip codes
You can use the spacial functions and spacial type columns http://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html
Here it's a great example to use spacial functions!
http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/
Or you can define a custom function
CREATE DEFINER=`test`@`%` FUNCTION `geoDistance`(`lon1` DOUBLE, `lat1` DOUBLE, `lon2` DOUBLE, `lat2` DOUBLE)
RETURNS double
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE v DOUBLE;
SELECT cos(radians(lat1))
* cos(radians(lat2))
* cos(radians(lon2) - radians(lon1))
+ sin(radians(lat1))
* sin(radians(lat2)) INTO v;
RETURN IF(v > 1, 0, 6371000 * acos(v));
END
then call
SELECT geoDistance(X(point1), Y(point1), X(spoint2), Y(point2))
result comes in meters
Related Topics
Calling a Function Within a Class Method
Laravel Validation Attributes "Nice Names"
Is There Any Difference Between _Dir_ and Dirname(_File_) in PHP
How to Ipc Between PHP Clients and a C Daemon Server
How to Read Xmp Data from a Jpg with PHP
What Is the Use of <<<Eod in PHP
Fastest Way to Extract a Specific Frame from a Video (Php/Ffmpeg/Anything)
Reverse Order of Foreach List Items
Php: How to Get Associative Array Key from Numeric Index
Creating a Folder When I Run File_Put_Contents()
Php: How to Check If a Date Is Today, Yesterday or Tomorrow