I need to find the different trajectories between point A and B. In the image is possible to see 2 differents trajectories. The way i use to calculate them is: Found the min time and the max time (in a specific date), and make a linestring with the points between this two times. The problem is that the min and max are near one of the other and the result is only one linestring. My problem now is split the line string, and stay with different trajectories for example 1 and 2 (image), because what i want is the different trajectories between A and B like image.
Way to found nearest points of A and B
SELECT * from tese.cars WHERE ((( st_dwithin( (SELECT geom FROM tese.cars WHERE (carid = 1 AND rdate = 190101 AND rtime = 85000)) -- Point A , geom, 20)) OR st_dwithin( (SELECT geom FROM tese.cars WHERE (carid = 2 AND rdate = 120101 AND rtime = 153804)) -- Point B , geom, 20)) )
Way to found Points between A and B
INSERT INTO tese.cars2 SELECT t.* FROM tese.cars as t INNER JOIN tese.pontosAB as tt ON t.carid=tt.carid AND t.rdate=tt.rdate AND ( t.rtime > (SELECT min(rtime) from tese.pontosAB where carid=t.carid and rdate=t.rdate) AND t.rtime < (SELECT max(rtime) from tese.pontosAB where carid=t.carid and rdate=t.rdate) )
Point to LineString
SELECT rdate, min(rtime), max(rtime), st_makeline(geom) FROM tese.cars2 group by rdate