Point in Polygon function for MySQL

Question

I have GPS information stored in multiple MySQL tables structured like the following:
(Running MySQL Server version 5.5.43 on Ubuntu Server 14.04.1)

event_number, time, latitude, longitude
1, 12:52:50, 40.5401, -86.5715
1, 12:52:51, 40.5404, -86.5707
1, 12:52:52, 40.5406, -86.5699
1, 12:52:53, 40.5409, -86.5691
1, 12:52:54, 40.5411, -86.5683
1, 12:52:55, 40.5414, -86.5676
2, 13:22:35, 40.9723, -85.1755
2, 13:22:36, 40.9726, -85.1759
2, 13:22:37, 40.9728, -85.1762
2, 13:22:38, 40.9731, -85.1766
2, 13:22:39, 40.9734, -85.1770
2, 13:22:40, 40.9737, -85.1773

Each table represents a separate vehicle with hundreds of separate "trips" within each table.

The goal is to find lambda for a Poisson distribution by testing how many vehicles arrive in a boundary box per hour. While there are several algorithms for determining whether a point is inside a polygon, I'm only concerned with the initial time at which the vehicles enter the boundary area.

I'm open to any suggestions. Ideally I would like to create a function in MySQL that will give me a count of the vehicles entering the boundary area per hour.

Answer 1

The first time a vehicle is in the polygon is

SELECT MIN(time)
    FROM tbl
    WHERE IsInPoly(latitude, longitude);

(Where IsInPoly is whatever function works. If you have a SPATIAL index, then a function is available in MySQL; I don't have the specifics off hand.)

If you need the "first time for each event_number when the vehicle is in the polygon":

SELECT MIN(time)
    FROM tbl
    WHERE IsInPoly(latitude, longitude)
    GROUP BY event_number;

These queries will probably involve table scans, so they won't be fast.