PostGIS SQL to generate separate line segments from a set of points, with varying IDs

Go To StackoverFlow.com

2

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)
2012-04-05 15:23
by blammo


0

Looking at this question:

https://gis.stackexchange.com/questions/34687/how-to-split-osm-roads-into-individual-segments-at-intersections

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.

2013-03-31 15:45
by Chris Travers
Ads