CODEWITHSUNDEEP
mysqlmathgps
Terran Brown
Terran Brown

Reputation: 519

Mysql - GPS Locations x 3 and how to Exclude Any Results Of Locations Within From A Search

so I've been working on this problem for a few days. The aim was to produce a radius search of data based on a users current location ( A Lat/Lon ). How ever I want to add some intelligence to the search by calculating B Lat/Lon & B Lat/Lon ( which I can do ) then from the radius search I do remove any matching GPS points within this triangle....

The closest I have mananaged to get to is matching B to C but not the points included up to A...

This is part maths and part MySQL as that is the eventual method I want to use to implement this.

Any thoughts ?

Terran

Main Image

The Final Result

From else where

Q:Why are there radii around B and C if the only radius you want to return results in is A? AThis are simply to show the lat long of each point... I've added an additional diagram to show how this would be used.

Q:What do B and C actually represent (A is current location) A: B and C will be locations worked out ( via maths ) based on the users current location, bearing and speed.

"It's easy to test if a point is in a triangle - think cross products of triangle edge vectors and vectors from triangle corners to test point. Inside points are inside all three sides."

Link - Inside Triangle Calc - http://www.mathworks.com/matlabcentral/fileexchange/22690-inside-triangle/content/inside_triangle.m

Upvotes: 0

Views: 233

Answers (1)

O. Jones
O. Jones

Reputation: 108806

There are several steps to this process.

Find the points within the bounding box of the circle. This works nicely and efficiently, if slightly sloppily on the longitude dimension, with

   SELECT *
     FROM points p
    WHERE p.lat >= alat-radius
      AND p.lat <= alat+radius
      AND p.lon >= alon-radius
      AND p.lon <= alon+radius

Next, exclude the points inside the triangle. You'll need to write a stored function to do triangle inclusion to handle this.

   SELECT *
     FROM points p
    WHERE p.lat >= alat-radius
      AND p.lat <= alat+radius
      AND p.lon >= alon-radius
      AND p.lon <= alon+radius
      AND NOT Inside_Triangle(p.lat, p.lon, alat, alon, blat, blon, clat, clon)

You can look here for algorithms for doing triangle inclusion. How can I determine whether a 2D Point is within a Polygon?

Finally, exclude the points that are outside the radius.

   SELECT *
     FROM points p
    WHERE p.lat >= alat-radius
      AND p.lat <= alat+radius
      AND p.lon >= alon-radius
      AND p.lon <= alon+radius
      AND NOT Inside_Triangle(p.lat, p.lon, alat, alon, blat, blon, clat, clon)
      AND Haversine(p.lat,p.lon,alat,alon) > radius

Here's an example of the Haversine function. Notice that you have to sort out the units of radius carefully. Why does this MySQL stored function give different results than to doing the calculation in the query?

There you have it. How to get a result set containing the points you want.

Upvotes: 2

Related Questions