أكثر

فصل مقاطع مسار GPS المنفصل في Postgres

فصل مقاطع مسار GPS المنفصل في Postgres


هذا ينطوي على الكثير من التعليمات البرمجية ؛ إذا كانت مناسبة بشكل أفضل لـ StackOverflow ، فيرجى إبلاغي بذلك!

أنا أعمل في مشروع يتضمن رحلات تم إنشاؤها من إحداثيات GPS. أقوم برسم خرائط الرحلات المسجلة على صفحة ويب ، باستخدام الإصدار 0.35 من عمود الريح. يتم جمع الرحلات من تطبيق الهاتف المحمول ، ويتم إدخال بيانات GPS في قاعدة بيانات Postgres (9.3) (مع تثبيت PostGIS (2.1.2)). الجداول ذات الصلة هي:

Coord_geog: العمود | اكتب | --------- + ----------------------------- | معرف | عدد صحيح | trip_id | عدد صحيح | مسجل | الطابع الزمني بدون منطقة زمنية | جيوج | الجغرافيا (نقطة ، 4326) | geom | الهندسة (نقطة ، 4326) | التالي | عدد صحيح | trip_geom: العمود | اكتب | --------- + ----------------------------- | معرف | عدد صحيح | الغرض | متفاوتة الأحرف (255) | بدء | الطابع الزمني بدون منطقة زمنية | توقف | الطابع الزمني بدون منطقة زمنية | geom | الهندسة (LineString ، 4326) |

الtrip_idعمودتنسيق_جوجيتوافق معبطاقة تعريفعمودtrip_geom. يوفر التطبيق بيانات لـبطاقة تعريف,trip_id,مسجل، وجيوجأعمدةتنسيق_جيوم، و البطاقة تعريف,غاية,بداية، وتوقفأعمدةtrip_geom. أنا في الواقع لست متأكدا ما هوgeomعمودتنسيق_جوجيُستخدم من أجل ، لكنني خائف جدًا من إزالته (ولا يبدو أنه يُستخدم في أي شيء مذكور في هذا السؤال).

لست متأكدًا تمامًا من كيفية عمل عمود الريح بالكامل ، ولكن إذا كان ذلك مفيدًا ، فسيتم تعيين تكوين عمود الريح حاليًا على النحو التالي:

req.params.sql = "(اختر * من trip_geom_frag حيث الغرض ilike '" + req.params.purpose + "٪') كـ trip_geom_frag" ؛ req.params = _.extend ({}، req.params، {style: style})؛

سأحددtrip_geom_fragأسفل لأسفل - إنه مشابه لـtrip_geom.

المهمة المحددة التي أحاول تحسينها هي إنشاء مسار LineString من جميع النقاط الموجودة في المسار.

لقد ورثت نصًا بلغة بايثون من المفترض أن يتولى هذه المهمة. على الرغم من أنني لست على دراية جيدة في Python أو SQL ، فقد أدركت أن الكثير من الأشياء التي كان يقوم بها البرنامج النصي يمكن القيام بها على أنها SQL خالصة ، مما أدى إلى تسريع كبير في البداية. بعد أن تصبح النقاط مجهولة المصدر (يتم اقتطاعها من نهاية كل رحلة) ، يقوم استعلام مثل هذا ببناء LineString:

UPDATE trip_geom t SET geom = (حدد ST_MakeLine (line.geom) من (حدد ج.المسجل ، ج.جوم من تنسيق_جوج ج حيث c.trip_id = t.id ترتيب بواسطة ج.تسجيل ASC) كخط) حيث t.id = ٪س؛

أين ال٪سالمعلمة هيبطاقة تعريفحقل من صف فيtrip_geom. هذا يبني LineString الذي يربط بين النقاط في الرحلة. ومع ذلك ، تميل إلى أن تكون هناك أخطاء في بيانات GPS ، حيث يتسبب إصلاح GPS السيئ في تحديد مكان واحد أو أكثر بشكل غير صحيح بعيدًا ، أو يتم إيقاف التسجيل مؤقتًا واستئنافه لاحقًا ، مما يؤدي إلى "قفزة" كبيرة في الرحلة.

تكون النقاط الصالحة قريبة من بعضها بشكل عام (نظرًا لأن الرحلات عبارة عن رحلات بالدراجات بسرعة منخفضة نسبيًا) ، لذلك عندما تحتوي الرحلة على جميع بيانات GPS الصالحة ، فإن LineString عبارة عن مخطط سلس إلى حد ما (خلفية الخريطة بواسطة CartoDB):

ومع ذلك ، فإن العديد من الرحلات (خاصة الطويلة منها) بها قفزات أو أخطاء ، مما يؤدي إلى رحلات تبدو كما يلي:

بينما أنا متأكد من وجود بعض راكبي الدراجات المتميزين ، أشك في أن شخصًا ما كان قادرًا على ركوب الدراجة مباشرة عبر النهر ؛ يبدو أن الخط المستقيم ناتج عن توقف راكب الدراجة مؤقتًا واستئناف تسجيل الرحلة. لا تعتبر هذه الأخطاء مشكلة كبيرة عند عزلها ، ولكن عندما يكون هناك الكثير منها ، تصبح خريطة الرحلات مليئة بالخطوط المستقيمة التي تعبر مناطق كبيرة من الخريطة.

لذلك ، فإن المهمة المحددة التي أحاول إنجازها هي إنشاء مسارات LineString بدون نقاط متجاورة تكون متباعدة جدًا. أتخيل أن هذه مهمة مناسبة تمامًا لـ PostGIS (أو SQL بشكل عام) ، لكني أجد صعوبة في إيجاد حل.

كان الحل الأول الذي حاولت القيام به هو إزالة النقاط التي كانت بعيدة عن النقطة السابقة. نظرًا لأنني لا أمتلك SQL بالضبط بعد الآن ، فإليك الإجراء في الكود الزائف:

بالنسبة إلى t في الرحلات: i = 1 بينما i 

أدى ذلك إلى رحلات سلسة ، ولكن تم اقتطاع الرحلات مع القفزات في بيانات GPS بعد القفزة. على سبيل المثال ، في رحلة بها قفزة بين النقطتين الأولى والثانية للرحلة ، ستتم إزالة جميع النقاط الموجودة في الرحلة بعد النقطة الأولى.

الحل الثاني (والحالي) هو إنشاء جدول ثانٍ مثلtrip_geomلعقد الينابيع الخطية من الرحلات ، والتي يتم فصلها كلما حدث قفزة بين النقاط. قررت تسمية هذه المسارات المجزأة - ربما يكون هناك مصطلح أفضل. هذا هو الجدول الجديد الذي قمت بإنشائه:

trip_geom_frag: العمود | اكتب | --------- + ----------------------------- | معرف | عدد صحيح | geom | الهندسة (LineString ، 4326) | الغرض | متفاوتة الأحرف (255) | Orig_trip | عدد صحيح |

ثم ، باستخدام ملفالتاليعمودتنسيق_جوج(الذي أضفته) ، أستخدم استعلامًا مثل هذا لإنشاء نوع من قائمة مرتبطة بالنقاط في كل رحلة ، بحيث-1تمثل القيمة إما قفزة في النقاط أو نهاية الرحلة:

UPDATE format_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 Register> cur_pt.recorded ORDER BY المسجل ASC LIMIT 1) ،٪ s) ثم 1 ELSE -1 END ؛

ثم ، في Python ، أقوم بإدراج صف فيtrip_geom_fragلكل جزء من الرحلة ، مع تحديد نهاية المقطع بعلامة -1:

بالنسبة إلى t في الرحلات: trip_id = t.id c.execute ('SELECT id ، التالي من format_geog حيث trip_id =٪ s ORDER BY id ASC؛'، (trip_id،)) ؛ coords = c.fetchall () ind = [التنسيق [1] للتنسيق في الكورد] بينما len (coords)> 0: i = ind.index (-1) c.execute ('INSERT INTO trip_geom_frag (geom، Orig_trip، الغرض ) حدد ST_MakeLine (line.geom) ،٪ s ،٪ s من (حدد c.geom من format_geog c حيث c.trip_id =٪ s AND c.id> =٪ s AND c.id <=٪ s اطلب بواسطة c. مسجل ASC) كسطر ؛ '، (trip_id، الغرض، trip_id، coords [0] [0]، coords [i] [0]،)) del coords [: i + 1] del ind [: i + 1]

مرة أخرى ، لست خبيرًا جدًا في Python أو SQL ، لذا سامحني إذا كان هذا فظيعًا ؛ أنا متأكد من أن هناك طريقة ما للقيام بذلك دون خلط البيانات بين Python و Postgres.

في أفضل حالة لعدم وجود قفزات أو أخطاء في نظام تحديد المواقع العالمي (GPS) ، ينتج عن ذلك سلسلة خط واحدة ، أو في حالة القفزات أو الأخطاء ، قد ينتج عن ذلك العديد من السلاسل. ينتج عن هذا مسارات يتم فصلها كلما حدث قفزة بين النقاط (نفس البيانات المصورة):

هذه البيانات تمامًا كما أتمنى أن تظهر ، لذا فهذا حل كافٍ في الوقت الحالي. ومع ذلك ، يوجد حاليًا 800 رحلة وما يقرب من 1.5 مليون نقطة إجمالية ؛ استغرقت معالجتها حوالي أربعة أيام (على خادم سيء) - لا أعرف ما إذا كان ذلك بسبب وجود خطأ ما في Postgres أو إذا كان ذلك لمجرد أن حل Python الخاص بي غير فعال للغاية. في كلتا الحالتين ، إذا كنت بحاجة إلى إعادة معالجة جميع الرحلات لسبب ما ، فقد يستغرق الأمر وقتًا طويلاً قبل أن تصبح الرحلات جاهزة.

كما أراه ، إليك بعض الأساليب التي قد تعمل بشكل جيد:

  • أدخل الإحداثيات في LineString بطريقة تسمح باستبعاد مقاطع معينة
  • قسّم كل رحلة إلى أجزاء متجاورة ، عن طريق تغيير الوسيطات إلى ST_MakeLine
  • اجعل عمود الريح يتجاهل مقاطع سلسلة الخط التي تتجاوز بعض الطول
  • قم برسم ST_MakeLine ، وبدلاً من ذلك قم بإنشاء مسار مخصص يتخطى بطريقة ما قفزة في النقاط
  • شيء واضح لا أعرفه لأنني لست على دراية بالأدوات الموجودة

آمل أن يكون هناك حل بسيط ، يتطلب بشكل مثالي PostGIS فقط ، والذي يمكنه معرفة أين يجب تقسيم النقاط في الرحلة وإدراجها في جدول. لذلك ، فإن سؤالي هو ما هو النهج الذي يجب علي اتباعه لتقسيم المسارات المستخدمة في استدعاءات ST_MakeLine لرسم نقاط متجاورة فقط؟

نظرًا لأنني أقوم بالفعل بحفظ البيانات كما تم إنشاؤها مسبقًا (غير مجزأة) في جدول منفصل ، فأنا حر في تعديل أي من الجداول أو إفساد أي من البيانات إذا كانت تساعد بطريقة ما.


تحديث: قمت بإعداد Postgres على جهازي المحلي (الأكثر قوة) وقمت بتنزيل قاعدة البيانات ، وأعدت تشغيل المعالجة محليًا. إنه أسرع بكثير - يبدو أنه يمكن معالجة جميع النقاط في أقل من 30 دقيقة ، مما يعني أن الجاني كان بالفعل خادمًا سيئًا. لا يزال من الرائع أن يكون لديك حل بلغة SQL خالصة ، على الرغم من ذلك.


هذه هي محاولتي لاستعلام يجب أن يُنشئ أجزاء من الرحلات مقسمة على قفزات تبلغ حوالي 100 متر.

CREATE TABLE trip_geom_parts (رقم trip_id الصحيح NOT NULL ، عدد صحيح للجزء NOT NULL ، بدء الطابع الزمني بدون منطقة زمنية ، ختم وقت بدون منطقة زمنية ، هندسة هندسية (linestring ، 4326) ، PRIMARY KEY (trip_id، part_id)) ؛ إنشاء تسلسل مؤقت التسلسل ؛ INSERT INTO trip_geom_parts (trip_id، part_id، start، stop، geom) مع s1 AS (حدد trip_id، CASE WHEN trip_id = lag (trip_id) فوق w ثم CASE عندما ST_Distance_Sphere (geom، lag (geom) OVER w) <100.0 ثم تيار ( 'seq') ELSE nextval ('seq') END ELSE setval ('seq'، 1) END AS part_id، Register، geom FROM Coord_geog WINDOW w AS (PARTITION BY trip_id ORDER BY Register)) حدد trip_id، part_id، min (مسجلة ) AS start ، max (مسجلة) AS stop ، ST_MakeLine (geom ORDER BY Registered) FROM s1 GROUP BY trip_id، part_id؛

لأداء أفضل قد يساعد

CREATE INDEX ON Coord_geog (trip_id ، مُسجل) ؛

لم أختبره ، لذلك قد أحتاج إلى بعض التصحيح.