hi,
I’ve a query that matches GPS points to locations. This is done periodically for every new point. So points that are not in a location get the value ‘NULL’ as location_id.
Now I want an update function for if I change or add existing locations. First off, lets just assume we only add locations (by adding them or making existing ones bigger)
I have this inser query:
INSERT INTO gps_point_locations (gps_point_id, location_id)
SELECT GpsPoint.id AS gps_point_id, Location.id AS location_id
FROM gps_points AS GpsPoint
INNER JOIN gps_points_geo AS gp ON GpsPoint.id = gp.gps_point_id
LEFT JOIN locations AS Location ON CONTAINS(Location.geo_poly, gp.gps_point_geo)
WHERE GpsPoint.date BETWEEN "2012-04-27 00:00:00" AND "2012-04-27 23:59:59" AND GpsPoint.unit_id=91
One table has the gps points (gps_points)
one table has for each gps_point a geo-point (gps_point_geo)
one table has all the locations (locations)
one table has the result with points matched to a location (gps_point_locations)
As you can see I changed the query to select not only new points, but just points that fall between a period of a track (in which the vehicle passed through the changed locations)
This query will be very heavy if not set up properly.
Any ideas how to make a UPDATE function for this?