You can export out the data and import into a MyISAM MYSQL
this way you will then have spatial indexing which will speed up the entire process by many times.
PostGIS would be even faster.
I don't think your queries are wrong - just needs to be a spatial table with index.
Types of Spatial dependent on the database software used:
Spatial indices are used by spatial databases (databases which store information related to objects in space) to optimize spatial queries. Indexes used by non-spatial databases cannot effectively handle features such as how far two points differ and whether points fall within a spatial area of interest. Common spatial index methods include:
Grid (spatial index)
Z-order (curve)
Quadtree
Octree
UB-tree
R-tree: Typically the preferred method for indexing spatial data. Objects (shapes, lines and points) are grouped using the minimum bounding rectangle (MBR). Objects are added to an MBR within the index that will lead to the smallest increase in its size.
R+ tree
R* tree
Hilbert R-tree
X-tree
kd-tree
m-tree - an m-tree index can be used for the efficient resolution of similarity queries on complex objects as compared using an arbitrary metric.
Source:
http://en.wikipedia.org/wiki/Spatial_database#Spatial_index
With points as close as these we can ignore what type of spheroid the earth is and use the eqirectangular projection Pythagoras formula is all that is required.
The following two functions are in PHP.
function Equirectangular($lat1,$lng1,$lat2,$lng2){
$lng1 = abs($lng1);
$lng2 = abs($lng2);
$alpha = $lng2-$lng1;
$x = deg2rad($alpha) * cos(deg2rad($lat1+$lat2)/2);
$y = deg2rad($lat1-$lat2);
$R = 6372.8*1000; // gives d in km
$distance = sqrt($x*$x + $y*$y) * $R;
return $distance;
}
function Haversine($lat1,$lng1,$lat2,$lng2) {
$deltaLat = $lat2 - $lat1 ;
$deltaLng = $lng2 - $lng1 ;
$earthRadius = 6372.8 *1000; // 3959 in miles 6371 in meters.
$alpha = $deltaLat/2;
$beta = $deltaLng/2;
$a = sin(deg2rad($alpha)) * sin(deg2rad($alpha)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin(deg2rad($beta)) * sin(deg2rad($beta)) ;
$c = 2 * atan2(sqrt($a), sqrt(1-$a));
$distance = $earthRadius * $c;
return $distance;
}
As you can Equirectangular() is simpler than Haversine()and can be used for small distances. I have multiplied earth radius by 100 to convert to metres.
Result.
Equirectangular 0.211513 m
Haversine 0.211513 m
The following code can be modified to suit your database
PDOPyth.php
Use PDO with parameters to query database using Equirectangular approximation formula for range.
Save exceptions to file.
02/03/2016
*/
require("dbinfo2.php");
//Create coordinates
$lat = 52;
$lng =-2;
$radius = 25;
//Connect to database
$dbh = new PDO("mysql:host=$host;dbname=$database", $username);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
// Prepare statement
//SELECT (SQRT( POW(RADIANS(lng1 -lng2), 2) + POW(RADIANS(lat1 - lat2)* COS(RADIANS(lat1 - lat2)/2),2 ))*6372
$stmt1 = $dbh->prepare("SELECT name, lat, lng, ( SQRT( POW(RADIANS(? - lng), 2) + POW(RADIANS(? - lat)* COS(RADIANS(? - lat)/2),2 ))*6372 ) AS distance FROM gbstn HAVING distance < ? ORDER BY distance ");
// Assign parameters
$stmt1->bindParam(1,$lng);
$stmt1->bindParam(2,$lat);
$stmt1->bindParam(3,$lat);
$stmt1->bindParam(4,$radius);
//Execute query
$stmt1->setFetchMode(PDO::FETCH_ASSOC);
$stmt1->execute();
$data = array();
// Iterate through the rows, adding nodes for each
while($row = $stmt1->fetch()) {
$data[] = $row;
}
echo $lat ." ".$lng;
echo "<BR>" ;
echo json_encode($data);
}
catch(PDOException $e) {
echo "I'm sorry I'm afraid you can't do that.". $e->getMessage() ;// Remove or modify after testing
file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", mapSelect.php, ". $e->getMessage()."\r\n", FILE_APPEND);
}
//Close the connection
$dbh = null;
?>
Best Answer
The code above was tested in Postgre and PostGIS 2.1 :
I have a table named UNITS and need to search units near by 500 Kilometers to the "MYSERIAL" unit. So I make the query using two alias to same table UNITS, one to refer MYSERIAL (p1) and other to search near units and show the DISTANCE (p2). A nested query is necessary to order by DISTANCE.
Note P1 refer to MYUNIT, so P2 must ignore the searched unit, so I make p2.serial <> 'MYSERIAL' to avoid include itself in result (distance = 0). I use ST_Distance_Sphere (PostGIS) to make sure the distance respects the used projection.