التحليل الجغرافي المكاني باستخدام مجموعة بيانات أبراج الاتصالات الخلوية

تعرّف على كيفية تحميل مجموعة بيانات أبراج الاتصالات الخلوية OpenCelliD إلى ClickHouse Cloud، وإثرائها باستخدام قاموس، والاستعلام عنها باستخدام الدوال الجغرافية، وعرضها مرئيًا باستخدام ClickHouse Agents

في هذا الدليل العملي، ستتعرّف على كيفية استخدام ClickHouse للتعامل مع البيانات الجغرافية من خلال مجموعة بيانات أبراج الاتصالات الخلوية. ستحمّل مجموعة البيانات إلى ClickHouse Cloud، وتفحص مخططها، وتنفّذ استعلامات تجميعية على عشرات الملايين من الصفوف. بعد ذلك، ستُثري البيانات باستخدام قاموس، وتستعين بالدالة pointInPolygon لحساب عدد الأبراج الواقعة داخل ألمانيا، ثم تستخدم ClickHouse Agents لعرض النتيجة بصريًا على خريطة.

​

المتطلبات الأساسية

ستحتاج في هذا الدليل العملي إلى ما يلي:

• حساب ClickHouse Cloud (تحصل على رصيد مجاني بقيمة 300 دولار عند التسجيل)
• خدمة ClickHouse Cloud

1. حمّل بيانات العينة

   مجموعة البيانات المستخدمة في هذا الدليل العملي مصدرها OpenCelliD، أكبر قاعدة بيانات مفتوحة لأبراج الاتصالات الخلوية في العالم. وحتى عام 2021، كانت تضم عشرات الملايين من السجلات عن أبراج الاتصالات الخلوية حول العالم، إلى جانب إحداثياتها الجغرافية وبيانات وصفية مثل رمز الدولة والشبكة.

   1. في ClickHouse Cloud، اختر خدمتك من القائمة المنسدلة في القائمة الجانبية اليسرى
   2. اختر Data sources
   3. انقر على بطاقة Add sample data
   4. اختر مجموعة البيانات Cell Towers (1.1 GB)
   5. استخدم default قاعدةَ بيانات هدفًا، ثم انقر على Import dataset

   من المفترض أن يظهر لك إدخال ضمن Data upload history بالحالة success

2. افحص المخطط

   1. اختر SQL console من القائمة الجانبية اليسرى
   2. انقر على علامة التبويب + بجوار أيقونة الصفحة الرئيسية لإنشاء استعلام جديد
   3. اكتب الاستعلام التالي في محرر SQL، ثم انقر على Run:

   DESCRIBE TABLE cell_towers
   

   من المفترض أن يظهر لك جدول النتائج التالي:

   ┌─name──────────┬─type──────────────────────────────────────────────────────────────────┬
   │ radio         │ Enum8('' = 0, 'CDMA' = 1, 'GSM' = 2, 'LTE' = 3, 'NR' = 4, 'UMTS' = 5) │
   │ mcc           │ UInt16                                                                │
   │ net           │ UInt16                                                                │
   │ area          │ UInt16                                                                │
   │ cell          │ UInt64                                                                │
   │ unit          │ Int16                                                                 │
   │ lon           │ Float64                                                               │
   │ lat           │ Float64                                                               │
   │ range         │ UInt32                                                                │
   │ samples       │ UInt32                                                                │
   │ changeable    │ UInt8                                                                 │
   │ created       │ DateTime                                                              │
   │ updated       │ DateTime                                                              │
   │ averageSignal │ UInt8                                                                 │
   └───────────────┴───────────────────────────────────────────────────────────────────────┴
   

   يقابل كل إدخال في الجدول برج اتصالات خلوية في موقع فعلي على سطح الأرض، تحدده الإحداثيات lon وlat.

3. نفّذ استعلامات بسيطة

   شغّل الاستعلام التالي لعرض عدد أبراج الاتصالات الخلوية حسب نوعها:

   SELECT radio, count() AS c FROM cell_towers GROUP BY radio ORDER BY c DESC
   

   ┌─radio─┬────────c─┐
   │ UMTS  │ 20686487 │
   │ LTE   │ 12101148 │
   │ GSM   │  9931304 │
   │ CDMA  │   556344 │
   │ NR    │      867 │
   └───────┴──────────┘
   

   بعد ذلك، تحقّق من عدد أبراج الاتصالات الخلوية حسب رمز الدولة للشبكات المتنقلة (MCC):

   SELECT mcc, count() FROM cell_towers GROUP BY mcc ORDER BY count() DESC LIMIT 10
   

   ┌─mcc─┬─count()─┐
   │ 310 │ 5024650 │
   │ 262 │ 2622423 │
   │ 250 │ 1953176 │
   │ 208 │ 1891187 │
   │ 724 │ 1836150 │
   │ 404 │ 1729151 │
   │ 234 │ 1618924 │
   │ 510 │ 1353998 │
   │ 440 │ 1343355 │
   │ 311 │ 1332798 │
   └─────┴─────────┘
   

   يتبيّن أن البلدان التي تضم أكبر عدد من أبراج الاتصالات الخلوية تحمل رموز MCC التالية: 310 و262 و250. يمكنك استخدام قاموس لاستبدال القيم الرقمية في العمود mcc بأسماء البلدان:

   CREATE DICTIONARY mcc_country_dict
   (
   MCC UInt16,
   Country String
   )
   PRIMARY KEY MCC
   SOURCE(HTTP(
   url 'https://raw.githubusercontent.com/ClickHouse/examples/refs/heads/main/datasets/mcc-countries.csv'
   format 'CSVWithNames'
   ))
   LAYOUT(HASHED())
   LIFETIME(MIN 0 MAX 0)
   

   بعد إنشاء القاموس، يمكنك الآن استخدامه لإرجاع القائمة نفسها مع إظهار اسم الدولة بجانب رمز MCC:

   Query

   SELECT
   mcc,
   dictGet('mcc_country_dict', 'Country', mcc) AS country,
   count() AS cnt
   FROM cell_towers
   GROUP BY mcc
   ORDER BY cnt DESC
   LIMIT 5
   

   Response

   310	United States of America	5024650
   262	Germany	                    2622423
   250	Russia	                    1953176
   208	France	                    1891187
   724	Brazil	                    1836150
   
4. دمج البيانات الجغرافية

   قد ترغب أيضًا في معرفة عدد أبراج الاتصالات الخلوية الواقعة ضمن منطقة جغرافية محددة. يوفّر ClickHouse لحالة الاستخدام هذه العديد من الدوال الجغرافية المفيدة، مثل الدالة pointInPolygon.

   لنفترض أنك تريد معرفة عدد أبراج الاتصالات الخلوية الموجودة داخل ألمانيا. أنشئ جدولًا باسم germany يحتوي على عمود واحد من النوع polygon:

   CREATE TABLE germany (polygon Array(Tuple(Float64, Float64)))
   ORDER BY polygon;
   

   أدرج الآن إحداثيات حدود ألمانيا (البر الرئيسي) في الجدول:

   INSERT INTO germany VALUES ([(10.454459535579133, 47.55573834324997), (10.890209019630618, 47.537202200721026), (10.970328786144592, 47.40002058348318), (11.272891854275713, 47.39785257163891), (11.637139099589831, 47.59425903862376),
   (12.20388194699433, 47.60680973608049), (12.162598450658038, 47.70112977791695), (12.256972280946059, 47.7430071703447), (12.255189497297522, 47.679294992270115), (12.440126465064338, 47.69517254727526),
   (12.499216429994306, 47.625017004631104), (12.781115045223203, 47.674141358936595), (12.80384823830559, 47.54988690165925), (13.04750486076074, 47.49208302661043), (13.080793954277624, 47.68702414364935),
   (12.905046866219948, 47.72364341860896), (13.003294582878766, 47.85031169133157), (12.758258591061178, 48.126325188057024), (13.329748843534333, 48.32352787700995), (13.508992162958009, 48.59060155142049),
   (13.727359451977179, 48.51300820806239), (13.839506769122124, 48.771604902544254), (13.628668330878838, 48.949206192112115), (13.402895552887514, 48.98734273487935), (13.029109282267598, 49.304348045426025),
   (12.655893345501568, 49.43454683436897), (12.521638213932476, 49.68676182809594), (12.40071737081007, 49.75384886863043), (12.547730388346793, 49.92034323954891), (12.201422342567128, 50.10850072273024),
   (12.10090036995615, 50.31802824350871), (12.184453107724778, 50.32232471771158), (12.289803626736045, 50.176930077860845), (12.334553826997592, 50.17174892257475), (12.512047935363967, 50.39725816952898),
   (12.819380927587815, 50.45979531680047), (12.912391784952945, 50.42372909162435), (12.937308320622549, 50.406328326990774), (12.947972824086605, 50.40427966057791), (13.370982951801807, 50.65054589707552),
   (13.464767584956121, 50.6018925959666), (13.526820353776372, 50.70501677580364), (13.855192645324394, 50.727109704649195), (14.387635007249855, 50.899087975929376), (14.258647344299561, 50.98753751504432),
   (14.301858340490071, 51.05504617034455), (14.508211954118451, 51.043154596807256), (14.59917754360879, 50.98711100167145), (14.564810750215997, 50.91835296052079), (14.650215415109358, 50.93153456746637),
   (14.618868455215079, 50.85771029809183), (14.793911832764934, 50.82014753807846), (15.041789401659628, 51.274035224546026), (14.949088456067443, 51.47126488866371), (14.729118627322862, 51.53143962304023),
   (14.7575478735356, 51.66150403168575), (14.590144372564794, 51.82100998951944), (14.759042582749032, 52.06476202734575), (14.681595447571908, 52.11665177535917), (14.715746889307525, 52.23588355048554),
   (14.576039609740235, 52.288366710755156), (14.534357221808364, 52.39500777668758), (14.63911057749118, 52.57299886300717), (14.12292694822014, 52.83765710679012), (14.143657774686574, 52.96136543636584),
   (14.348570677994417, 53.05471830950245), (14.450570288887775, 53.26224993248843), (14.30255731786599, 53.55340466159589), (14.283595832881417, 53.772302946323975), (13.822463699939874, 53.847704554621544),
   (13.914106243308538, 53.921857458754744), (13.743604331474785, 54.02871742866648), (13.808750796911795, 54.10045250547881), (13.7062583845169, 54.17118193160093), (13.489404453952432, 54.083845783755976),
   (13.142765860556096, 54.2536547023027), (13.178427336852167, 54.26927398011014), (13.132873048252634, 54.27941493335254), (13.138935061603831, 54.319290755963664), (13.393587180304166, 54.220971308479136),
   (13.350821423170203, 54.269835443950626), (13.57531796425269, 54.352998349893994), (13.723904396071077, 54.273407259737326), (13.766909407942876, 54.34183448887063), (13.57052592673017, 54.45765298249921),
   (13.661158812029612, 54.57578947060762), (13.425207789507908, 54.57776585760831), (13.377190829792994, 54.63485405846808), (13.429096601926688, 54.68488300844416), (13.250612882278745, 54.660459153766965),
   (13.160936899918056, 54.559083360308875), (13.28297271262062, 54.64558892578833), (13.245451284549176, 54.5578326488577), (13.368071411966127, 54.61469746081923), (13.446130971966852, 54.55160125899096),
   (13.511133452346996, 54.566114026241394), (13.557162122533953, 54.438539184735646), (13.345708024605699, 54.52048608046829), (13.368973991356654, 54.57891966081377), (13.304904763544073, 54.51411689467898),
   (13.144180806199302, 54.546965665732955), (13.270112698742082, 54.47988865533722), (13.149423426549959, 54.42813433982798), (13.260337438621946, 54.38045384347015), (13.129103578567367, 54.32114076573379),
   (13.015778021838287, 54.43939322591672), (12.809663608988387, 54.34360109191692), (12.668525237349854, 54.408133446301576), (12.42937122989838, 54.29356120888275), (12.436138349505256, 54.37908880570387),
   (12.693160014523073, 54.432254058577996), (12.926951675779264, 54.42796397405823), (12.514359690347476, 54.484348643375256), (12.100993421009605, 54.16928820758477), (11.682936883230639, 54.1532711821194),
   (11.552519675043015, 54.09268650258008), (11.626244382272489, 54.09015710806415), (11.452965579797706, 53.894822219270054), (11.179984660863454, 54.01516631277519), (10.898693904600066, 53.95665538983445),
   (10.760186530184285, 54.02460718607284), (11.09341311572274, 54.19866713068359), (11.07191809484425, 54.34224630552262), (11.125609497880077, 54.373613504659545), (11.118558230278154, 54.39442093987452),
   (10.706974464899702, 54.30503645926524), (10.321948337845583, 54.43574765223241), (10.174162085012256, 54.34574538594086), (10.199382745317507, 54.45556154343052), (10.134027446509492, 54.48433790277204),
   (9.85537006887364, 54.456283168433515), (10.027329336579669, 54.55311722278168), (10.036023455118425, 54.68841701763131), (9.909510390991784, 54.79991988959131), (9.845578708190885, 54.75603640555573),
   (9.591910925348998, 54.886970216702196), (9.343610061754987, 54.80024799285064), (8.94793864795031, 54.90256336560691), (8.638003412527496, 54.911251855288924), (8.42740949814987, 54.877399149451264),
   (8.352821841055174, 54.96785703207644), (8.46419569987171, 55.04571479500186), (8.415419699759411, 55.05866217082257), (8.297902985734254, 54.91065659132869), (8.278555583673324, 54.752208180463185),
   (8.340561373708454, 54.88057457195896), (8.414381016970424, 54.84724070929133), (8.61261435866578, 54.87846776077862), (8.686209753277524, 54.73034082227116), (8.892967769546999, 54.59677687545252),
   (8.807015453500412, 54.47015896026784), (8.983598917356176, 54.52404630129581), (9.018963414130326, 54.4747456609835), (8.660693777196116, 54.39516479791712), (8.6025729405888, 54.31421651837769),
   (8.841787401367526, 54.278455117509736), (8.80886763603263, 54.17103733680369), (8.985125080358216, 54.060986664003394), (8.804773173842932, 54.023438800360395), (8.914904897143344, 53.92489909541695),
   (9.275123379340528, 53.857269302336476), (8.61491387027894, 53.88211822337735), (8.483191313228588, 53.694068058094444), (8.556142860704483, 53.526062883695204), (8.28300703264847, 53.61001675866652),
   (8.226915998274762, 53.52008371229357), (8.315820116805014, 53.461673297584696), (8.249573034967625, 53.398362336752484), (8.071310815641652, 53.46743230775485), (8.170974825128098, 53.54047017575806),
   (8.00872870406647, 53.71213492358788), (7.296326719002934, 53.680098653130415), (7.090335704828988, 53.579652246054366), (7.129998987962949, 53.532359981627906), (7.034145450951826, 53.545670230296935),
   (7.012918474807805, 53.34466909190513), (7.265760915633791, 53.32719714862958), (7.191115728008469, 53.31760156675898), (7.217281326671923, 53.00698383493608), (7.087323855511556, 52.84992858723456),
   (7.055577137075261, 52.64336989040817), (6.75264448085818, 52.64813255966993), (6.766621548046032, 52.561512582439775), (6.680868309524897, 52.553336398755846), (6.753103465152833, 52.46379697395122),
   (6.991802701633446, 52.467452567881764), (7.065868436279345, 52.24125898091728), (6.694652945177324, 52.06980283263465), (6.830419114950246, 51.98620651731699), (6.732607205258375, 51.898707142520664),
   (6.401752481791675, 51.82729228551062), (6.11804667081725, 51.901669637044165), (6.166407642798504, 51.840834718447525), (5.945029570158567, 51.82365634356546), (6.212084663676308, 51.513378407087885),
   (6.226129918361096, 51.36051764687198), (6.072656924877208, 51.24258730406086), (6.175415266801167, 51.15847977362273), (5.867096631104687, 51.04667907857669), (6.093924751825909, 50.921325464498636),
   (5.974861423316099, 50.79797295258976), (6.266895404299532, 50.64152787241346), (6.196995433692337, 50.531031950664044), (6.351259021186934, 50.48826779162533), (6.407870066950068, 50.33513396126335),
   (6.175687402551659, 50.235415978964284), (6.112562492565019, 50.05922877348917), (6.322636569122437, 49.838032009569645), (6.529314320899232, 49.8093868550194), (6.357852033844324, 49.573820737836854),
   (6.367083526083889, 49.46951204834244), (6.550815842976817, 49.425379524945185), (6.738600535586272, 49.16367513140227), (6.938108243600595, 49.222433998330416), (7.052445915346766, 49.11275784459889),
   (7.293176904690426, 49.11490732950972), (7.445646679250899, 49.18415102974626), (7.631201346365117, 49.05488570651022), (8.232632820348442, 48.9665714470147), (7.839740454658283, 48.64127586299071),
   (7.744638258948555, 48.32781093512659), (7.577585310114046, 48.12065583849619), (7.621952269681572, 47.97280642807425), (7.511871830220855, 47.696037521514484), (7.604484107155884, 47.57779110477571),
   (7.693596586013655, 47.60053085751309), (7.667618493298903, 47.536221133898266), (8.582542232737921, 47.59610411291453), (8.60662329822668, 47.67212011105431), (8.405559327800802, 47.67451554525627),
   (8.56785207691189, 47.80848124891054), (8.893659067611907, 47.6521787220704), (8.989864116335923, 47.743465936104144), (9.21772790891589, 47.66612201881617), (9.03953653098614, 47.81748197511314),
   (9.680775632889208, 47.542264915293686), (9.80050512701007, 47.59598448329734), (9.97089855812203, 47.54568043790192), (10.091470374358323, 47.45907615923744), (10.09978048058474, 47.35473600336394),
   (10.23619637995904, 47.381860769459024), (10.172036399760088, 47.27904122237459), (10.232299537493702, 47.27047759546474), (10.436472139577631, 47.38064780435275), (10.454459535579133, 47.55573834324997)]);
   

   يمكنك الآن معرفة عدد أبراج الاتصالات الخلوية الواقعة داخل البر الرئيسي لألمانيا باستخدام الدالة pointInPolygon:

   Query

   SELECT formatReadableQuantity(count()) FROM cell_towers
   WHERE pointInPolygon((lon, lat), (SELECT * FROM germany))
   

   Response

   2.62 million
   
5. اختياري: مثّل البيانات بصريًا باستخدام ClickHouse Agents

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

   انقر على ClickHouse agents أسفل اسم مؤسستك في الجزء السفلي من القائمة اليسرى لفتح ClickHouse Agents. يمكنك مراسلة ClickHouse agent عبر الدردشة. اكتب “Visualize cell towers within Germany on a map using the default.cell_towers and default.germany tables” ثم اضغط إرسال. في إحدى مرات التشغيل، نفّذ الوكيل الخطوات التالية:

   • بحث عن رمز الدولة للهاتف المحمول (Mobile Country Code) الخاص بألمانيا في mcc_country_dict فوجد أن قيمة MCC هي 262.
   • أحصى الأبراج ذات mcc = 262 الواقعة داخل المضلّع في germany باستخدام pointInPolygon، فبلغ عددها 2,588,635 برجًا. وهذا العدد أقل قليلًا من العدد السابق، لأن عامل التصفية الإضافي mcc يستبعد الأبراج الواقعة داخل المضلّع والمسجّلة لدولة أخرى.
   • بدلًا من رسم 2.6 مليون نقطة منفردة، جمّع الأبراج في شبكة بأبعاد 0.1° × 0.1° عبر تقريب lon وlat، ما أنتج 4,821 خلية. ثم تحقّق من أن مجموع أعداد الخلايا يساوي 2,588,635 تمامًا، أي أنه لم يُفقد أي برج ولم يُحتسب أي برج مرتين أثناء التجميع.
   • استرجع المضلّع من germany ورسمه كحدود فوق الشبكة، مستخدمًا مقياس ألوان لوغاريتميًا لتظهر الخلايا الحضرية الكثيفة والخلايا الريفية المتناثرة على حدّ سواء.

   افتح الملف المُنشأ لرؤية النتيجة. تمثّل التجمّعات الساطعة المناطق الحضرية الكبرى: برلين وهامبورغ وميونخ وفرانكفورت.

​

الخطوات التالية

في هذا الدليل العملي، حمّلت مجموعة بيانات أبراج الاتصالات الخلوية OpenCelliD إلى ClickHouse Cloud، وفحصت مخططها، ونفّذت استعلامات تجميعية على عشرات الملايين من الصفوف. كما أنشأت قاموسًا لإثراء العمود الرقمي mcc بأسماء الدول، وخزّنت حدود ألمانيا على هيئة مضلّع، واستخدمت pointInPolygon لحساب عدد الأبراج الواقعة داخله. وأخيرًا، استخدمت ClickHouse Agents لتحويل هذا السؤال إلى خريطة كثافة دون أن تكتب بنفسك شيفرة التجميع أو الرسم.

بعد ذلك، يمكنك:

• طرح أسئلة متابعة على الوكيل. جرّب أن تسأل عن الفرق في الكثافة بين أنواع التقنيات الراديوية مثل LTE وGSM، أو عن المدن التي تضم أكبر عدد من أبراج NR. احفظ الموجّهات التي تستخدمها باستمرار في مكتبة الموجّهات، أو أنشئ وكيلًا متخصصًا باستخدام Agent Builder.
• المقارنة بين الدول. أدرج مضلّعات لدول أخرى ونفّذ استعلام pointInPolygon نفسه على كلٍّ منها. ويتيح لك القاموس الذي أنشأته إرفاق أسماء الدول بالنتائج.
• تجميع الأبراج باستخدام H3 أو geohash. اعتمد الوكيل على شبكة تقريب بسيطة، لكن ClickHouse يوفّر أيضًا دوال H3 وgeohash التي تُنتج خلايا هرمية مناسبة للخرائط القابلة للتكبير.
• استكشاف مجموعات بيانات أخرى. تحتوي مجموعة بيانات سيارات الأجرة في نيويورك هي الأخرى على إحداثيات نقاط الانطلاق والوصول، كما يضم فهرس مجموعات البيانات النموذجية العديد غيرها.