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.

 Answers

3

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
4

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
 
5

OK, in the end I just implemented this: http://local.wasp.uwa.edu.au/~pbourke/geometry/lineline2d/

Tuesday, August 16, 2022
2

UPDATE

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.


UPDATE 2

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_box_2d
angle_contains_ray_2d
angle_deg_2d
angle_half_2d # MLM: double *
angle_rad_2d
angle_rad_3d
angle_rad_nd
angle_turn_2d
anglei_deg_2d
anglei_rad_2d
annulus_area_2d
annulus_sector_area_2d
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 *
box_01_contains_point_2d
box_01_contains_point_nd
box_contains_point_2d
box_contains_point_nd
box_ray_int_2d
box_segment_clip_2d
circle_arc_point_near_2d
circle_area_2d
circle_dia2imp_2d
circle_exp_contains_point_2d
circle_exp2imp_2d
circle_imp_contains_point_2d
circle_imp_line_par_int_2d
circle_imp_point_dist_2d
circle_imp_point_dist_signed_2d
circle_imp_point_near_2d
circle_imp_points_2d # MlM: double *
circle_imp_points_3d # MLM: double *
circle_imp_points_arc_2d
circle_imp_print_2d
circle_imp_print_3d
circle_imp2exp_2d
circle_llr2imp_2d # MLM: double *
circle_lune_area_2d
circle_lune_centroid_2d # MLM; double *
circle_pppr2imp_3d

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: https://github.com/hoonto/pygeometry

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

http://people.sc.fsu.edu/~jburkardt/cpp_src/geometry/geometry.html

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 geometry.py 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 geometry.py.

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!

Rgds....Hoonto/Matt

(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 :