1

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 come 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?

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
zarun
  • 910
  • 9
  • 26
  • Note that a "polygon" on the Earth does not really form a polygon, it divides the earth into two parts. Which one is 'inside' and which is 'outside'? If you treat Lat-Long as a cartesian coordinate system, what happens if your polygons cover the N or S poles? – Spacedman Sep 19 '11 at 13:04

2 Answers2

2

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.

James C
  • 14,047
  • 1
  • 34
  • 43
  • thanks for the comment - I'm still waiting for someone with Mysql geospatial thing who might run into this. Thanks again! : ) – zarun Sep 19 '11 at 09:34
0

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.

zarun
  • 910
  • 9
  • 26