All,
I'm probably trying to push the SQL angle too far, but I need to generate simple two vertice line segment from a set of GPS tracking points as a feed from many vehicles(veh_id). The resulting line segments are also grouped in the input by IDs.
I can generate multi-segment lines just fine with the existing POSTGIS tools, and even bust them apart into separate segments after making a multi-segment line, but this keeping the line segments separated is giving me a brain freeze.
The business need, is that I need to apply a time/distance formula to each segment as it's created based on calc from each point used to arrive at a Speed per segment. The fact that the input is mixed vehicle IDs seems like it might be a problem.
I know this is some sort of join, but I've not been able to make it work as of yet. I think if I can figure out a method of at least pairing up the points per segment to build the individual line segment, I can get the rest to work. The "cmdrecid" is time sequential, but for all vehicles so there would be gaps on a per vehicle basis
My sql so far from the raw points looks like, any ideas or pointers from anyone, thanks!! :
select a.cmdrecid,
a.esn,
a.time_idx,
a.the_geom,
a.speed,
a.veh_id
from stmaint_plots a, (select veh_id, count(*)
from stmaint_plots
where time_idx >= (NOW() - INTERVAL '1 minutes')
group by veh_id having count(*) > 1
) as b
where a.time_idx >= (NOW() - INTERVAL '1 minutes')
and a.veh_id = b.veh_id
order by b.veh_id, a.time_idx desc;
cmdrecid | esn | time_idx | the_geom | speed | veh_id
----------+----------+------------------------+----------------------------------------------------+-------+--------
562233 | 10202001 | 2012-04-05 10:05:54-05 | 0101000020840D030031FB5FADF10221418696195E70770341 | 16.0 | 2042
562226 | 10202001 | 2012-04-05 10:05:46-05 | 0101000020840D030031FB5FADF10221418696195E70770341 | 16.0 | 2042
562246 | 10202010 | 2012-04-05 10:06:16-05 | 0101000020840D0300DC583A10C74921419B78BD2F3C440441 | 29.6 | 2075
562232 | 10202010 | 2012-04-05 10:05:53-05 | 0101000020840D0300BAD35538334A2141F97506A3732D0441 | 1.0 | 2075
562216 | 10202010 | 2012-04-05 10:05:33-05 | 0101000020840D0300BAD35538334A2141F97506A3732D0441 | 0.0 | 2075
562239 | 11031319 | 2012-04-05 10:06:08-05 | 0101000020840D030090D4FFEB8DFD214168556D2F88770341 | 0.0 | 2202
562227 | 11031319 | 2012-04-05 10:05:46-05 | 0101000020840D030090D4FFEB8DFD214168556D2F88770341 | 0.0 | 2202
562245 | 10202004 | 2012-04-05 10:06:14-05 | 0101000020840D03000EE2342EC8382141C3A07B7223420241 | 3.8 | 2234
562231 | 10202004 | 2012-04-05 10:05:53-05 | 0101000020840D0300645F4BBDF837214194A58D9944430241 | 1.5 | 2234
562219 | 10202004 | 2012-04-05 10:05:35-05 | 0101000020840D0300645F4BBDF837214194A58D9944430241 | 0.0 | 2234
562242 | 11030817 | 2012-04-05 10:06:10-05 | 0101000020840D0300F07C6C31085B2141DAE94B086BF20341 | 6.7 | 2350
562213 | 11030817 | 2012-04-05 10:05:31-05 | 0101000020840D03009BF21B7B0C5C2141D4D6754598EB0341 | 0.1 | 2350
562229 | 11072928 | 2012-04-05 10:05:51-05 | 0101000020840D0300A05299973C66214137D638BF22820441 | 0.0 | 2369
562217 | 11072928 | 2012-04-05 10:05:34-05 | 0101000020840D0300A05299973C66214137D638BF22820441 | 0.1 | 2369
562244 | 11072930 | 2012-04-05 10:06:12-05 | 0101000020840D0300526B108976CE2041538A57D001C40241 | 0.0 | 2372
562234 | 11072930 | 2012-04-05 10:05:55-05 | 0101000020840D0300526B108976CE2041538A57D001C40241 | 0.1 | 2372
562211 | 11072930 | 2012-04-05 10:05:31-05 | 0101000020840D0300526B108976CE2041538A57D001C40241 | 0.1 | 2372
562243 | 11031308 | 2012-04-05 10:06:11-05 | 0101000020840D0300A3888CBB4D0C224131ACAA5F99860341 | 0.0 | 2482
562230 | 11031308 | 2012-04-05 10:05:51-05 | 0101000020840D0300A3888CBB4D0C224131ACAA5F99860341 | 0.0 | 2482
562214 | 11031308 | 2012-04-05 10:05:32-05 | 0101000020840D0300A3888CBB4D0C224131ACAA5F99860341 | 0.0 | 2482
562240 | 11072901 | 2012-04-05 10:06:08-05 | 0101000020840D0300D5C9EF09F7FD214181016B7FD7710341 | 4.4 | 2522
562220 | 11072901 | 2012-04-05 10:05:36-05 | 0101000020840D03007FE8CC1AF6FD214104C0399242750341 | 2.4 | 2522
562241 | 11072910 | 2012-04-05 10:06:08-05 | 0101000020840D0300BE72D47D5A0D2241C53399774B630341 | 0.0 | 2532
562225 | 11072910 | 2012-04-05 10:05:45-05 | 0101000020840D0300BE72D47D5A0D2241C53399774B630341 | 0.0 | 2532
562249 | 11030821 | 2012-04-05 10:06:25-05 | 0101000020840D0300E15572FD4E042141D491539CA1E80441 | 0.0 | 2538
562236 | 11030821 | 2012-04-05 10:06:04-05 | 0101000020840D0300E15572FD4E042141D491539CA1E80441 | 0.0 | 2538
562223 | 11030821 | 2012-04-05 10:05:44-05 | 0101000020840D03008EA28CFC7F032141E6E959569FE80441 | 12.2 | 2538
562235 | 11031332 | 2012-04-05 10:05:55-05 | 0101000020840D030064B179212BFE2141F351ECB0B4700341 | 3.9 | 2809
562215 | 11031332 | 2012-04-05 10:05:33-05 | 0101000020840D0300B033836AF6FD214143DE49E11E740341 | 2.8 | 2809
562248 | 11031326 | 2012-04-05 10:06:20-05 | 0101000020840D030046D5FF39849421418F854A13E9DA0241 | 0.0 | 2811
562221 | 11031326 | 2012-04-05 10:05:39-05 | 0101000020840D030046D5FF39849421418F854A13E9DA0241 | 0.2 | 2811
(31 rows)
Looking at this question:
The overall consensus is that this is not a good idea because you can create intersections that don't exist (overpasses, etc, become intersections). The better approach is to use a tool like osm2pgrouting or osm2po or the like.