Viewed   297 times

I've got a working PHP script that gets Longitude and Latitude values and then inputs them into a MySQL query. I'd like to make it solely MySQL. Here's my current PHP Code:

if ($distance != "Any" && $customer_zip != "") { //get the great circle distance

    //get the origin zip code info
    $zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
    $result = mysql_query($zip_sql);
    $row = mysql_fetch_array($result);
    $origin_lat = $row['lat'];
    $origin_lon = $row['lon'];

    //get the range
    $lat_range = $distance/69.172;
    $lon_range = abs($distance/(cos($details[0]) * 69.172));
    $min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
    $max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
    $min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
    $max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
    $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";

Does anyone know how to make this entirely MySQL? I've browsed the Internet a bit but most of the literature on it is pretty confusing.



From Google Code FAQ - Creating a Store Locator with PHP, MySQL & Google Maps:

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance 
FROM markers 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20;
Monday, December 19, 2022

SQL Server 2008

Using the spacial function STDistance return distance in meters

geography::Point(@lat1, @lon1, 4326).STDistance(geography::Point(@lat2, @lon2, 4326))

Monday, August 15, 2022

OK, in the end I just implemented this:

Tuesday, August 16, 2022


Moving on now to finishing out the other 576 functions in that library not including the two polygon functions that are finished, the three sphere distance algorithms that are done, and two new ones, an angle_box_2d and angle_contains_ray_2d. Also, I switched to the C version so that externs are not needed, simplifies the work. Put the old C++ version in directory old_c++, so its still there.

Tested performance, it is identical as listed at the bottom of the answer.


So just a quick update, I haven't finished the whole library yet (I'm only about 15% of the way through), but I've added these untested functions, in case you need them right away, on github, to add to the old point in polygon and sphere distance algorithms.

angle_half_2d # MLM: double *
annulus_sector_centroid_2d # MLM: double *
ball_unit_sample_2d # MLM: double *
ball_unit_sample_3d # MLM: double *
ball_unit_sample_nd # MLM; double *
basis_map_3d #double *
circle_imp_points_2d # MlM: double *
circle_imp_points_3d # MLM: double *
circle_llr2imp_2d # MLM: double *
circle_lune_centroid_2d # MLM; double *

The ones that I've commented above probably won't work, the others might, but again - polygon & sphere distances definitely do. And you can specify meters, kilometers, miles, nautical miles, it doesn't really matter on the spherical distance ones, the output is in the same units as the input - the algorithms are agnnostic to the units.

I put this together this morning so it currently only provides the point in polygon, point in convex polygon, and three different types of spherical distance algorithms, but at least those ones that you requested are there for you to use now. I don't know if there is a name conflict with any other python library out there, I only get peripherally involved with python these days, so if there's a better name for it I'm open to suggestions.

On github:

It is just a python bridge to the functions described and implemented here:

The GEOMETRY library is pretty good actually, so I think it'll be useful to bridge all of those functions for python, which I'll do probably tonight.

Edit: a couple other things

  1. Because the math functions are actually compiled C++, you do of course need to make sure that the shared library is in the path. You can modify the to point at wherever you want to put that shared library though.
  2. Only compiled for linux, the .o and .so were compiled on x86_64 fedora.
  3. The spherical distance algorithms expect radians so you need to convert decimal lat/lon degrees for example to radians, as shown in

If you do need this on Windows let me know, it should only take a couple minutes to get it worked out in Visual Studio. But unless someone asks I'll probably just leave it alone for now.

Hope this helps!


(new commit: SHA: 4fa2dbbe849c09252c7bd931edfe8db478de28e6 - fixed some things, like radian conversions and also the return types for the py functions. Also added some basic performance tests to make sure the library performs appropriately.)

Test Results In each iteration, one call to sphere_distance1 and one call polygon_contains_point_2d so 2 calls to the library total.

  • ~0.062s : 2000 iterations, 4000 calls
  • ~0.603s : 20000 iterations, 40000 calls
  • ~0.905s : 30000 iterations, 60000 calls
  • ~1.198s : 40000 iterations, 80000 calls
Thursday, September 8, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :