搜索范围纬度/经度坐标

Question

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 ."'  "; 

Answer 1

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.