Search for range Latitude/Longitude coordinates

This script works, but I am sure there is a better way to approach this. I have already tried storing them using the MySQL gis functionality any other suggestions would be helpful.

$vicinity = .001;

$lat_ceiling = floatval($lat) + floatval($vicinity);
$lat_floor =  floatval($lat) - floatval($vicinity);
$lng_ceiling = floatval($lng) + floatval($vicinity);
$lng_floor =  floatval($lng) - floatval($vicinity);

开发者_运维知识库$query = "SELECT * FROM `geolocations` WHERE".
            "`latitude` <= '".$lat_ceiling."' AND `latitude` >= '". $lat_floor ."' AND `longitude` <= '".$lng_ceiling."' AND `longitude` >= '". $lng_floor ."'  "; 

Some version of what you are doing is right but I think you probably want to use the actual geometry types, especially if you are on MyISAM and can create an R-Tree spatial index. You can have columns with any supported type (ie point, polygon), or the catchall geometry type:

mysql> create table spatial_table (id int, my_spots point, my_polygons geometry);
Query OK, 0 rows affected (0.06 sec)

Then query and update with WKT syntax:

mysql> insert into spatial_table values (1, GeomFromText('POINT(1 1)'), GeomFromText('POLYGON((1 1, 2 2, 0 2, 1 1))'));
Query OK, 1 row affected (0.00 sec)

mysql> insert into spatial_table values (1, GeomFromText('POINT(10 10)'), GeomFromText('POLYGON((10 10, 20 20, 0 20, 10 10))') );
Query OK, 1 row affected (0.00 sec)

You can then do your query (ie vicinity), against the minimum bounding rectangle of a linestring with terminating points point1 = longitude - increment, lon - increment, y = longitude + increment, latitude + increment, ie here with a +- of 1:

mysql> select * from spatial_table where MBRContains(GeomFromText('LINESTRING(9 9, 11 11)'), my_spots);
+------+---------------------------+-----------------------------------------------------------------------------------+
| id   | my_spots                  | my_polygons                                                                       |
+------+---------------------------+-----------------------------------------------------------------------------------+
|    1 |              $@      $@ |                    $@      $@      4@      4@              4@      $@      $@ |
+------+---------------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This will perform much better than doing arithmetic on a bunch of floats representing longitude and latitude. BTW at about the location of San Francisco the following constants work pretty well for converting between km and degrees longitude and latitude (ie, if you want clean square maps of Santa Cruz):

lonf 0.01132221938
latf 0.0090215040

That is, (x +- 2*lonf, y +- 2*latf) gives you the relevant $lat_floor etc values for a 2km wide boy around your point of interest.

I don't see anything really wrong with the way you're doing it, but this might be a little cleaner:

SELECT *
FROM `geolocations`
WHERE
    `latitude` BETWEEN $lat - $vicinity AND $lat + $vicinity
    AND
    `longitude` BETWEEN $lng - $vicinity AND $lng + $vicinity

PostGIS is an add-on for PostgreSQL databases, for managing geographic data. It has a distance-function: http://postgis.refractions.net/documentation/manual-1.3/ch06.html#id2574517

If you are looking for an approximation of the harvesine formula maybe you want to use a spatial index or a space-filling-curve. A sfc is a quadtree and fractal like data structure reducing the 2d complexity to a 1d complexity. It's a curve that completley fills the space and a clever algorithm to subdivide the map. Instead of computing the index you can also nest the tiles and accumulate a quadkey and search in this key with the standard mysql string operators. For example if you have a quadkey 11111222233334444 you can search for 1111* to find all key => location pairs from this node and below or right from it.