Meer

Skei losstaande GPS-paadsegmente in Postgres


Dit behels baie kode; Laat weet my as dit beter geskik is vir StackOverflow!

Ek werk aan 'n projek wat reis saamgestel het uit GPS-koördinate. Ek karteer die opgetekende ritte op 'n webblad, met weergawe 0.35 van Windshaft. Reise word vanaf 'n mobiele toepassing versamel en die GPS-data word in 'n Postgres (9.3) -databasis ingevoeg (met PostGIS (2.1.2) geïnstalleer). Die relevante tabelle is:

coord_geog: Kolom | Tik | --------- + ----------------------------- | id | heelgetal | trip_id | heelgetal | opgeneem | tydstempel sonder tydsone | geog | geografie (Punt, 4326) | geom | meetkunde (Punt, 4326) | volgende | heelgetal | trip_geom: Kolom | Tik | --------- + ----------------------------- | id | heelgetal | doel | karakter wissel (255) | begin | tydstempel sonder tydsone | stop | tydstempel sonder tydsone | geom | meetkunde (LineString, 4326) |

Dietrip_idkolom vancoord_geogstem ooreen met dieidkolom vantrip_geom. Die aansoek verskaf data vir dieid,trip_id,opgeneem, engeogkolomme vancoord_geom, en dieid,doel,begin, enstopkolomme vantrip_geom. Ek is eintlik nie seker wat diegeomkolom vancoord_geogword gebruik, maar ek is te bang om dit te verwyder (en dit lyk asof dit nie gebruik word in enigiets wat in hierdie vraag genoem word nie).

Ek is nie baie seker hoe die hele Windshaft-ding werk nie, maar as dit help, is die Windshaft-konfigurasie tans ingestel as:

req.params.sql = "(kies * uit trip_geom_frag waar doel soos '' + req.params.purpose +"% ') as trip_geom_frag "; req.params = _.extend ({}, req.params, {style: style});

Ek sal spesifiseertrip_geom_fraglaer af - dit is soortgelyk aantrip_geom.

Die spesifieke taak wat ek probeer verbeter, is om 'n LineString-pad te konstrueer vanaf al die punte in 'n pad.

Ek het 'n Python-skrif geërf wat veronderstel is om hierdie taak te hanteer. Alhoewel ek nie baie vertroud is met Python of SQL nie, het ek besef dat baie dinge wat die script doen, as pure SQL gedoen kan word, wat aanvanklik tot 'n vinnige versnelling gelei het. Nadat die punte geanonimiseer is (van elke einde van 'n reis afgesny), konstrueer 'n navraag soos hierdie die LineString:

UPDATE trip_geom t SET geom = (SELECT ST_MakeLine (line.geom) FROM (SELECT c.recorded, c.geom FROM coord_geog c WHERE c.trip_id = t.id ORDER BY c.recorded ASC) as line) WAAR t.id = % s;

waar die% sparameter is 'nidveld vanaf 'n ry intrip_geom. Dit konstrueer 'n LineString wat die punte in die reis verbind. Daar is egter foute in die GPS-data, waar 'n slegte GPS-oplossing veroorsaak dat een of meer punte verkeerd geleë is, of dat die opname onderbreek word en later hervat word, wat 'n groot sprong in die rit tot gevolg het.

Geldige punte is oor die algemeen naby mekaar (aangesien die fietse met 'n relatiewe lae spoed reis), dus as 'n reis alle geldige GPS-data het, is die LineString 'n redelike gladde plot (kaartagtergrond deur CartoDB):

Baie reise (veral die lang) het egter sprong of foute, wat lei tot reise wat so lyk:

Alhoewel ek seker is dat daar baie bekwame fietsryers is, betwyfel ek dat iemand reguit oor 'n rivier kon fietsry; dit lyk asof die reguit lyn veroorsaak word deur die fietsryer wat stilgehou het en die reisopname hervat het. Hierdie foute is nie 'n groot probleem as dit geïsoleer is nie, maar as dit baie is, word die kaart van die reise baie deurmekaar met reguit lyne wat oor groot dele van die kaart kruis.

Daarom is die spesifieke taak wat ek wil uitvoer om LineString-paaie te genereer sonder aangrensende punte wat baie ver van mekaar af is. Ek verbeel my dat dit 'n taak is wat geskik is vir PostGIS (of SQL in die algemeen), maar ek sukkel om 'n oplossing te vind.

My eerste poging tot oplossing was om punte te verwyder wat ver van die vorige punt af was. Aangesien ek nie meer die presiese SQL het nie, is die prosedure in pseudo-kode:

vir t in ritte: i = 1 terwyl i 

Dit het gelei tot gladde ritte, maar die ritte met spronge in die GPS-data is na die sprong afgekap. Byvoorbeeld, in 'n rit met 'n sprong tussen die eerste en tweede punt van die reis, sal al die punte in die reis na die eerste punt verwyder word.

My tweede (en huidige) oplossing is om 'n tweede tabel soostrip_geomom LineStrings te hou van reise, wat geskei word wanneer daar tussen die punte gespring word. Ek het besluit om hierdie gefragmenteerde paaie te noem - daar is waarskynlik 'n beter term. Hier is die nuwe tabel wat ek geskep het:

trip_geom_frag: Kolom | Tik | --------- + ----------------------------- | id | heelgetal | geom | meetkunde (LineString, 4326) | doel | karakter wissel (255) | orig_trip | heelgetal |

Gebruik dan dievolgendekolom vancoord_geog(wat ek bygevoeg het), gebruik ek 'n navraag soos hierdie om 'n soort gekoppelde lys van die punte in elke reis op te stel, sodat 'n-1waarde verteenwoordig 'n sprong in die punte of die einde van 'n reis:

UPDATE coord_geog cur_pt SET next = CASE WHEN ST_DWithin (cur_pt.geog, (SELECT geog FROM coord_geog WHERE trip_id = cur_pt.trip_id AND record> cur_pt.recorded ORDER BY recorded ASC LIMIT 1),% s) TOE 1 ELSE -1 EINDE;

Dan, in Python, voeg ek 'n ry intrip_geom_fragvir elke segment van 'n reis, met die einde van 'n segment deur -1:

vir t in uitstappies: trip_id = t.id c.execute ('SELECT id, next FROM coord_geog WHERE trip_id =% s ORDER BY id ASC;', (trip_id,)); coords = c.fetchall () ind = [coord [1] for coord in coords] terwyl len (coords)> 0: i = ind.index (-1) c.execute ('INSERT INTO trip_geom_frag (geom, orig_trip, purpose ) SELECT ST_MakeLine (line.geom),% s,% s FROM (SELECT c.geom FROM coord_geog c WAAR c.trip_id =% s AND c.id> =% s AND c.id <=% s ORDER BY c. ASC) as lyn aangeteken; ', (trip_id, purpose, trip_id, coords [0] [0], coords [i] [0],)) del coords [: i + 1] del ind [: i + 1]

Weereens, ek is nie baie ervare in Python of SQL nie, so vergewe my as dit verskriklik is; Ek is redelik seker dat daar 'n manier is om dit te doen sonder om die data tussen Python en Postgres te skuif.

In die beste geval van geen GPS-sprong of -foute nie, lewer dit 'n enkele LineString, of, in die geval van sprong of foute, moontlik baie LineStrings. Dit lewer paaie wat geskei word wanneer daar tussen die punte gespring word (dieselfde gegewens op die foto):

Hierdie gegewens is presies soos ek sou hoop dat dit sou verskyn, en dit is voorlopig 'n voldoende oplossing. Daar is egter tans 800 reise en ongeveer 1,5 miljoen totale punte; die verwerking daarvan het ongeveer vier dae geneem (op 'n slegte bediener) - ek weet nie of daar iets met Postgres verkeerd is nie en of dit net omdat my Python-oplossing so ondoeltreffend is. Hoe dit ook al sy, as ek om een ​​of ander rede al die reise moet verwerk, kan dit heeltemal te lank duur voordat die reise gereed is.

Soos ek dit sien, is hier 'n paar benaderings wat goed kan werk:

  • Voeg koördinate in die LineString op 'n manier waarmee spesifieke segmente uitgesluit kan word
  • Verdeel elke rit in aangrensende segmente deur die argumente na ST_MakeLine te verander
  • Laat Windas segmente van 'n LineString wat langer is, ignoreer
  • Skrap ST_MakeLine, en maak eerder 'n pasgemaakte pad wat op die een of ander manier oor 'n sprong in punte spring
  • Iets voor die hand liggend waarvan ek nie weet nie, want ek is nie vertroud met die gereedskap nie

My hoop is dat daar 'n eenvoudige oplossing is, wat ideaalweg slegs PostGIS benodig, wat kan uitvind waar die punte in 'n reis verdeel moet word en dit in 'n tabel kan plaas. My vraag is dus watter benadering moet ek neem om partisiepaaie te gebruik wat gebruik word in oproepe na ST_MakeLine om slegs aangrensende punte te trek?

Aangesien ek die data alreeds in 'n aparte tabel soos dit voorheen saamgestel is (nie-gefragmenteerd) gestoor het, is ek vry om enige van die tabelle te wysig of om enige van die data te verward as dit op die een of ander manier help.


Opdatering: ek het Postgres op my (baie kragtiger) plaaslike masjien ingestel en die databasis afgelaai en die verwerking plaaslik weer laat loop. Dit is baie vinniger - dit lyk asof al die punte binne minder as 30 minute verwerk kan word, wat beteken dat die skuldige 'n slegte bediener was. Dit sal egter steeds wonderlik wees om 'n oplossing in suiwer SQL te hê.


Dit is my poging om 'n vraag te maak wat dele van ritte moet skep, verdeel by spronge van ongeveer 100 m.

SKEP TABEL trip_geom_parts (trip_id integer NOT NULL, part_id integer NOT NULL, begin tydstempel sonder tydsone, stop tydstempel sonder tydsone, geom meetkunde (linestring, 4326), PRIM ,RE SLEUTEL (trip_id, part_id)); SKEP TYDELIKE VOLGENDE volgorde; INVOER IN trip_geom_parts (trip_id, part_id, start, stop, geom) MET s1 AS (SELECT trip_id, CASE WHEN trip_id = lag (trip_id) OVER w DAN GEVAL WANNEER ST_Distance_Sphere (geom, lag (geom) OVER w) <100,0 DAN kromming ( 'SEQ') ELSE Nextval ('SEQ') EINDE ELSE setval ('SEQ', 1) EINDE AS deel_ID, opgeneem, geom VAN koord_geog WINDOW w AS (DEEL PER trip_ID BESTELLING OP opgenomen)) SELECT trip_id, part_id, min (aangeteken ) AS begin, maksimum (opgeneem) AS stop, ST_MakeLine (geom BESTEL DEUR opgeneem) VANAF s1 GROEP DEUR trip_id, part_id;

Vir beter prestasie kan dit help om

SKEP INDEKS OP coord_geog (trip_id, opgeneem);

Ek het dit nie getoets nie, daarom is dit miskien nodig om ontfout te word.