Daha çox

Postgres-də ayrılmış GPS yolu seqmentləri ayrılır


Bu bir çox kodu əhatə edir; StackOverflow üçün daha uyğun olsa, xahiş edirəm mənə bildirin!

GPS koordinatlarından hazırlanan səyahətləri əhatə edən bir layihə üzərində işləyirəm. Qeydə alınmış səyahətləri bir Windshaft'ın 0.35 versiyası ilə bir veb səhifədə xəritələşdirirəm. Səfərlər bir mobil tətbiqetmədən toplanır və GPS məlumatları Postgres (9.3) verilənlər bazasına daxil edilir (PostGIS (2.1.2) quraşdırılmış vəziyyətdə). Müvafiq cədvəllər:

coord_geog: Sütun | Yazın | --------- + ----------------------------- | id | tam ədəd | trip_id | tam ədəd | qeyd | saat zonası olmadan zaman damgası | geog | coğrafiya (Point, 4326) | geom | həndəsə (Nöqtə, 4326) | növbəti | tam ədəd | trip_geom: Sütun | Yazın | --------- + ----------------------------- | id | tam ədəd | məqsəd | xarakter dəyişir (255) | başla | saat zonası olmadan zaman damgası | dayan | saat zonası olmadan zaman damgası | geom | həndəsə (LineString, 4326) |

Thetrip_idsütunucog_geogilə uyğunduridsütunutrip_geom. Tətbiqid,trip_id,qeyd edildicoğsütunlarıcoord_geom, vəid,məqsəd,başlamaqdursütunlarıtrip_geom. Əslində nə olduğundan əmin deyiləmgeomsütunucog_geogüçün istifadə olunur, amma onu silməkdən çox qorxuram (və bu sualda göstərilən bir şeydə istifadə olunduğu görünmür).

Bütün Windshaft işinin necə olduğuna çox əmin deyiləm, amma kömək edərsə, Windshaft konfiqurasiyası hazırda belə qurulur:

req.params.sql = "(trip_geom_frag arasından * seçin, məqsəd ilike '" + req.params.purpose + "%') trip_geom_frag olaraq"; req.params = _.extend ({}, req.params, {style: style});

Dəqiqləşdirəcəyəmtrip_geom_fragaşağı aşağı - buna bənzəyirtrip_geom.

İnkişaf etdirməyə çalışdığım xüsusi tapşırıq, yoldakı bütün nöqtələrdən LineString yolu inşa etməkdir.

Bu vəzifəni yerinə yetirməli olan bir Python skriptinə miras qaldım. Xüsusilə Python və ya SQL-də yaxşı bilikli olmasam da, ssenarinin gördüyü bir çox şeyin təmiz SQL olaraq edilə biləcəyini və bunun əvvəlcə böyük bir sürətlənmə ilə nəticələndiyini başa düşdüm. Xallar anonimləşdirildikdən sonra (səyahətin hər ucundan kəsilir), bu kimi bir sorğu LineString-i qurur:

UPDATE trip_geom t SET geom = (SEÇİN ST_MakeLine (line.geom) FROM (SELECT c.recorded, c.geom FROM coord_geog c WHERE c.trip_id = t.id SİFARİŞ BY c.recorded ASC) satır olaraq) HARADA t.id = % s;

harada% sparametr biridbir sətirdən sahətrip_geom. Bu, səfərdəki nöqtələri birləşdirən bir LineString qurur. Bununla birlikdə, GPS məlumatlarında səhv bir GPS düzəlişinin bir və ya daha çox nöqtənin uzaqda səhv yerləşdirilməsinə səbəb olduğu və ya qeydin dayandırıldığı və daha sonra davam etdirildiyi, səfərdə böyük bir 'atlama' ilə nəticələnən səhvlər olur.

Etibarlı nöqtələr ümumiyyətlə bir-birinə yaxındır (gəzintilər nisbətən aşağı sürətlə velosiped gəzintiləri olduğu üçün), buna görə bir səyahət bütün etibarlı GPS məlumatlarına sahib olduqda, LineString kifayət qədər hamar bir süjetdir (CartoDB-dan xəritə fonu):

Bununla birlikdə, bir çox səyahətdə (xüsusən də uzun səfərlərdə) sıçrayış və ya səhvlər olur və nəticədə bu kimi səyahətlər olur:

Bəzi yüksək səviyyəli velosipedçilər olduğuna əmin olsam da, kiminsə düz bir çayın kənarında velosiped sürə biləcəyinə şübhə edirəm; düz xətt velosipedçinin bir səyahət qeydini dayandıraraq yenidən başlamasından qaynaqlanır. Bu səhvlər təcrid olunduqda böyük bir problem deyil, ancaq bir çoxu olduqda, səyahətlər xəritəsi xəritənin geniş ərazilərindən keçən düz xətlərlə çox qarışıq olur.

Buna görə də, yerinə yetirməyə çalışdığım xüsusi vəzifə, bir-birindən çox uzaq olan bitişik nöqtələr olmadan LineString yolları yaratmaqdır. Bunun PostGIS (və ya ümumiyyətlə SQL) üçün çox uyğun bir tapşırıq olduğunu təsəvvür edirəm, amma həll tapmaqda çətinlik çəkirəm.

İlk cəhdim əvvəlki nöqtədən çox uzaq olan nöqtələri qaldırmaq idi. Artıq dəqiq SQL-yə sahib olmadığım üçün yalançı koddakı prosedur belədir:

gəzintilərdəki t üçün: i = 1 ikən i 

Bu, hamar bir gedişlə nəticələndi, ancaq GPS məlumatlarında sıçrayışla gedişlər atlamadan sonra kəsildi. Məsələn, səfərin birinci və ikinci nöqtələri arasında bir sıçrayışla gedişdə, ilk nöqtədən sonrakı səfərdəki bütün nöqtələr silinəcəkdir.

İkinci (və cari) həllim kimi ikinci bir cədvəl yaratmaqdırtrip_geomnöqtələr arasında sıçrayış olduqda ayrılan səfərlərdən LineStrings-i tutmaq. Bu parçalanmış yolları adlandırmağa qərar verdim - ehtimal ki, daha yaxşı bir müddət var. Budur, yaratdığım yeni cədvəl:

trip_geom_frag: Sütun | Yazın | --------- + ----------------------------- | id | tam ədəd | geom | həndəsə (LineString, 4326) | məqsəd | xarakter dəyişir (255) | orig_trip | tam ədəd |

Sonra istifadə edərəknövbətisütunucog_geog(əlavə etdiyim), hər bir səfərdəki nöqtələrin bir növ əlaqəli siyahısını qurmaq üçün belə bir sorğu istifadə edirəm.-1dəyər ya nöqtələrdə bir sıçrayışı və ya səyahətin sonunu təmsil edir:

UPDATE coord_geog cur_pt SET next = CASE WHEN ST_DWithin (cur_pt.geog, (SEÇİN geog FROM coord_geog WHERE trip_id = cur_pt.trip_id AND qeydə alınmış> cur_pt.recorded SİFARİŞ ilə SİFARİLƏN qeyd ASC LIMIT 1),% s) BİRDƏN SONRA -1 SON;

Sonra Python-a bir sıra əlavə edirəmtrip_geom_fragsəyahətin hər seqmenti üçün, seqmentin sonu -1 ilə işarələnir:

t in trips üçün: trip_id = t.id c.execute ('SEÇİN KİMLİK, next FROM coord_geog WHERE trip_id =% s SİFARİŞ İD tərəfindən ASC;', (trip_id,)); coords = c.fetchall () ind = [coord [1] coords in coords] while len (coords)> 0: i = ind.index (-1) c.execute ('INSERT INTO trip_geom_frag (geom, orig_trip, purpose ) SEÇİN ST_MakeLine (line.geom),% s,% s FROM (SELECT c.geom FROM coord_geog c WHERE c.trip_id =% s AND c.id> =% s AND c.id <=% s SİFARİŞ c. qeyd ASC) xətt kimi; ', (trip_id, məqsədi, trip_id, koordinatlar [0] [0], koordinatlar [i] [0],)) del əlaqələr [: i + 1] del ind [: i + 1]

Yenə də Python və ya SQL-də çox təcrübəli deyiləm, bu dəhşətlidirsə, məni bağışla; Python və Postgres arasındakı məlumatları qarışdırmadan bunu etmək üçün bir yol olduğuna əminəm.

GPS atlamalarının və ya səhvlərinin olmamasının ən yaxşı halda, bu, tək bir LineString və ya atlamalar və ya səhvlər halında, çoxsaylı LineStrings istehsal edir. Bu nöqtələr arasında bir sıçrayış olduqda ayrılan yolları meydana gətirir (şəkildəki eyni məlumat):

Bu məlumatlar tam olaraq çıxacağını ümid etdiyim kimidir, buna görə də bu, hələlik kifayətdir. Bununla birlikdə, hazırda 800 səyahət və təxminən 1,5 milyon ümumi bal var; bunların işlənməsi təxminən dörd gün çəkdi (pis bir serverdə) - Bunun Postgres ilə bağlı bir şey olduğuna görə yoxsa Python həllimin bu qədər səmərəsiz olduğuna görə bilmirəm. Hər iki halda da, nədənsə bütün səfərləri yenidən işləməli olsam, səfərlərin hazır olmasından əvvəl çox uzun çəkə bilər.

Gördüyüm kimi, yaxşı işləyə biləcək bir neçə yanaşma var:

  • Xüsusi seqmentlərin xaric olmasına imkan verəcək şəkildə LineString-ə koordinatları daxil edin
  • Arqumentləri ST_MakeLine olaraq dəyişdirərək hər gəzintini bitişik seqmentlərə bölün
  • Windshaft-ı bir LineString-in bəzi uzunluğu aşan seqmentlərini görməməzliyə vurun
  • ST_MakeLine qırıntısı edin və bunun əvəzinə bir şəkildə bal atlamasından keçərək xüsusi bir yol düzəldin
  • İndiki alətləri çox tanımadığım üçün bilmədiyim açıq bir şey

Ümid edirəm ki, bir səfərdəki nöqtələrin harada bölüşdürüləcəyini müəyyənləşdirə və cədvələ daxil edə biləcəyiniz, yalnız PostGIS-i tələb edən sadə bir həll var. Buna görə sualım, yalnız bitişik nöqtələr çəkmək üçün ST_MakeLine-ə edilən zənglərdə istifadə olunan bölmə yollarına hansı yanaşmanı atmalıyam?

Veriləri əvvəllər ayrı bir cədvəldə qurulduğu kimi (parçalanmamış) olduğu kimi saxladığımdan, hər hansı bir şəkildə kömək edərsə, cədvəllərdən birini dəyişdirmək və ya hər hansı bir məlumatı düzəltməkdə sərbəstəm.


Yeniləmə: (daha güclü) yerli maşınımda Postgres qurdum və verilənlər bazasını yüklədim, prosesi yerli olaraq yenidən işə saldım. Çox daha sürətli - deyəsən bütün nöqtələrin 30 dəqiqədən az müddətdə işlənməsi mümkündür, yəni günahkar həqiqətən də pis bir server idi. Yenə də təmiz SQL-də bir həll tapmaq çox yaxşı olardı.


Bu, təxminən 100 m sıçrayışlarda bölünmüş gəzintilərin hissələrini yarada biləcək bir sorğu üçün cəhdimdir.

CƏDVƏL YARADIN trip_geom_parts (trip_id tam ədədi NULL deyil, part_id tam ədədi NULL DEYİL, saat zonası olmadan zaman damğasını başladın, vaxt zonası olmadan zaman damğasını dayandır, geom həndəsəsi (linestring, 4326), İLK KEY (trip_id, part_id)); MÜVƏQQƏTİ SIRALIQ seqmenti yaradın; INSERT INTO trip_geom_parts (trip_id, part_id, start, stop, geom) ilə s1 AS (SEÇİN trip_id, CASE WHEN trip_id = lag (trip_id) ST_Distance_Sphere (geom, lag (geom) w) 'nin üstündə <100.0 ON curr ( 'seq') ELSE nextval ('seq') END ELSE setval ('seq', 1) END as part_id, qeydə alınmış, geom FROM coord_geog WINDOW w AS (BÖLMƏ BY trip_id SİFARİŞ BY qeyd)) SEÇİN trip_id, part_id, min (qeyd ) AS start, maks (qeydə alınmış) AS stop, ST_MakeLine (geom SİFARİŞ BY qeyd edildi) s1 GROUP BY trip_id, part_id;

Daha yaxşı performans üçün kömək edə bilər

INDEX-i koordinat-geoqda yarat (trip_id, qeyd olunur);

Test etmədim, buna görə bir neçə ayıklamaya ehtiyac ola bilər.