mysql spatial field

one of the value updated to a field in a mysql table is 'location' of type开发者_如何学C 'point'.

When we select the field

select *,AsText(location) from somtable

How can we use this field in our script ? a we need to find the radius or the distance between 2 points.

Usually, you'll want to return the distance between the two locations as another field, not the location, because this is the point itself and not a distance. Something like: For example:

$sql = 'SELECT *, ( 3959 * acos( cos( radians(' . $lat1 . ') ) * cos( radians(location.lat) ) * cos( radians(location.lng) - radians(' . $lng1 . ') ) + sin( radians(' . $lat1 . ') ) * sin( radians(location.lat) ) ) ) AS distanceFromUser FROM somtable WHERE....';

But if you're rolling with a Point datatype, then use some MySQL spatial functions instead. One of the popular ones is MBRContains. This makes your query something like:

select * from somtable where
 MBRContains(
   GeomFromText('Polygon((swLat swLon, neLat swLon, neLat neLon, swLat neLon, swLat swLon))'), location );

The other coordinates are specifying a bounding box in which your location Point must exist. So in the second case, you are just returning points inside the box. In the first case, you are returning the distance to every point in your table.

This link allows for a detailed example for radius:

http://howto-use-mysql-spatial-ext.blogspot.in/2007/11/using-circular-area-selection.html

It builds on @dRocking's example of MBRContains, and also explains how to build the polygon from a given point.