1

I have the following latitude and longitude:

lat - 18.9802767 
lng - 72.8142511

I am trying the following query for places withint 10 kms from the point of interest.

select mbrcontains( geomfromtext(
'LINESTRING(72.8993663648088 19.0702857,72.72913583519122 18.8902677)'
), 
geomfromtext(
'point(18.9802767 72.8142511)'
)  );

The Linestring geometry object is derived from the exact point that I am trying to determine is within using the method for mysql 5.1 and above from this example using the formula :

linestring(point(@lng+10/(111.1/cos(radians(@lat))),@lat+10/111.1), point(@lng-10/(111.1/cos(radians(@lat))),@lat-10/111.1))

From what I understand the point falls within the Minimum Bounding Rectangle (MBR). However the query returns a 0 for the answer. The above is following the principles given in this example.

What is wrong with the query? How can I know if the point is within a certain geospatial distance (in this case the MBR points are calculated using 10 kms from the point given by co-ordinates: lat - 18.9802767, lng - 72.8142511).

BTW, I am using MySQL 5.5.32.

Community
  • 1
  • 1
woofmeow
  • 2,370
  • 16
  • 13

1 Answers1

0

Your point does not fall within the MBR of the line. Looks like you've reversed the latitude or longitude coordinates on either the line or the point. Switch the X and Y in 'point(18.9802767 72.8142511)' to get this point, which will be within the MBR of the line:

POINT (72.8142511 18.9802767)

If you are tied to MySQL, you may consider updating to MySQL 5.6, then using the Buffer function to create a circular area from your point of interest, and then use ST_Within to find anything within that circular area.

If you are not tied to MySQL, consider PostGIS, which provides a nice ST_DWithin function that makes these comparisons very easy.

lreeder
  • 12,047
  • 2
  • 56
  • 65
  • This is the formula I have used as given [here](http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points?lq=1): `linestring(point(@lng+10/(111.1/cos(radians(@lat))),@lat+10/111.1), point(@lng-10/(111.1/cos(radians(@lat))),@lat-10/111.1))` So the `Linestring` geometry object is derived from the exact point that I am trying to determine is within. Am I doing something wrong here ? – woofmeow Aug 03 '13 at 08:50
  • Your linestring is probably correct, but the point you are passing to geomfromtext has the x and y flipped, which puts it far away from the linestring. I've updated my answer to make that more clear – lreeder Aug 03 '13 at 13:25
  • Thanks I think its working for now. Although having a problem with the table still :-(. Anyways could you explain the logic behind the division by cos ? – woofmeow Aug 03 '13 at 20:42
  • 1
    This accounts for the lines of longitude converging at the poles. As you move north or south from the equator, the number of KM/degree of latitude changes as a function of cos(latitude), from about 111KM at the equator where 111KM*cos(0) = 111KM*1 = 111KM, to 0KM at the poles (because the lines have converged to a point), where 111KM*cos(90 deg) = 111KM*0 = 0KM. You don't have to do this for longitude, because lines of latitude don't converge, so KM/degrees of longitude is always about 111. – lreeder Aug 04 '13 at 03:56
  • Thanks a tonne @Ireeder ... that was really helpful.. you rock !! – woofmeow Aug 04 '13 at 11:52