Find how many (googlemap) polygons near x distance of a point

I'm working on a MYSQL/PHP system where I have the following:

-- a set of latitude, longitude in the form of (lat,lng) stored as text format : (lat1,lng1)#(lat2,lng2)#(lat3,lng3) etc. which is basically a polygon drawn over a googlemap instance stored in the database.

-- a table which stores in a field - a point's coordinates P(plat,plng) which is basically a point where a device is stationed

I need to figure out how many polygons from the first table are within a distance of X kilometers from the point P essentially using MYSQL.

I have c开发者_如何学Goome across quite a few Google Map libraries regarding this already, but I intend to resolve this by the quickest method possible - which I assume is via a MYSQL query.

Can anyone please please shed some light regarding this?

I've so far consulted a few examples on geospatial querying - and come up with this :

    SELECT user_id, latitude, longitude, 
      GeomFromText( "POINT(CONCAT_WS(' ',latitude,longitude))" ) AS point,
      Contains( GeomFromText( 'POLYGON(-26.167918065075458 28.10680389404297, 
    - 26.187020810321858 28.091354370117188, -26.199805575765794 28.125,-26.181937320958628 28.150405883789062, -26.160676690299308 28.13220977783203, -26.167918065075458 28.10680389404297)' ) , 
      GEOMFromText( "POINT(CONCAT_WS(' ',latitude,longitude))" ) ) 
   FROM user_location

But the problem is it shows a record with lat: -26.136230, long: 28.338850 as well which is way off the polygon's boundaries. Can anyone please guide?

I'm not sure if you want to calculate the distance to the nearest corner of the polygon, boundary of the polygon or some notional central point of it. Either way I think the mathmetical solution to this is to use Pythagoras' theorem to work out the proximity of points.

If you have lat1,lng1 and lat2,lng2 expressed in metres I believe that the distance between them is:

SQRT(POW(ABS(lat1 - lat2),2) + POW(ABS(lng1 - lng2),2))

Using an algorithm similar to this you need to decide whether you want to compare your known lat/lng to a single central point of the polygon or to the points of its corners (three times the work!).

MySQL does have a geospatial extension which could be worth looking at. Unfortunately I don't have experience of it.

Okay, did this - and it works - might help someone:

SELECT user_id,latitude,longitude,
   Contains(
          PolyFromText( 'POLYGON((-26.167918065075458 28.10680389404297, -26.187020810321858 28.091354370117188, -26.199805575765794 28.125,-26.181937320958628  28.150405883789062, -26.160676690299308 28.13220977783203, -26.167918065075458 28.10680389404297))' ),
          PointFromText(concat("POINT(",latitude," ",longitude,")"))
   ) as contains
FROM user_location

=====

Although I agree on expert's views that PostGIS could be a better option.