כמה מבנים יש במדינה? למי יש יותר מבנים? ישראל או תימן?
איך בודקים במהירות כמה מבנים, ישובים, נקודות עניין יש במדינה ספיציפית?
למשל ישראל מול תימן: כמה מבנים יש בכל מדינה? כבישים? מסעדות?
מבנים מסוג מסוים, מעל מספר קומות או גובה , כמה מסעדות, בתי מרקחת ובעצם כל דבר שרק נרצה דעת ולנתח, הכל בעזרת מידע פתוח, בענן או בתוכנות פתוחות בעזרת Spatial SQL.
איך משיגים מידע כזה ?
אפשר לחפש בגוגל, לשאול את CHAT GPT, לא בטוח שנקבל תשובה
אבל, אפשר גם לעשות את זה לבד בקלות עם פתרונות GIS בענן, בלי ידע עצום בגיאוגרפיה, או עם מערכות מידע גיאוגרפיות(GIS) ולמשוך את המידע ישירות לגליון אלקטרוני של גוגל.
צריך בשביל זה רק חשבון גוגל רגיל וקצת SQL.
בפוסט הקודם הסברתי איך מוצאים את המרחק של ישובים מהגבול בעזרת Spatial SQL.
הפעם המתכון בניחוח תימני ישראלי, בתיאבון..
אז איך עושים את זה בקלות, בדפדפן, בחינם ועם מידע פתוח בכמה שורות? הרי מדובר במיליונים של רשומות עם נפח רב לכל מדינה.
אפשר להוריד מידע פתוח למחשב מבסיסי המידע הפתוח העולמיים: overture maps, open street map להשתמש בכלי GIS שולחניים או יותר טוב לטעון לבסיס נתונים כמו PostgreSQL-PostGIS או DuckDB אבל זה דורש איתור של החומר למדינה מתוך כל העולם, הורדה, טעינה וכח מיחשוב שלא תמיד זמין.
חברות הענן למיניהן (AWS,GCP, Azure) נותנות אפשרות לעבוד עם מוצרי Datawarehouse שלהם וגם מתחזקות מידע פתוח עולמי, חלקו גיאוגרפי.
[אני בחרתי להשתמש במנוע של גוגל, אבל זה אפשרי גם בעננים האחרים]
לגוגל יש את מנוע ה- Big query שכולל יכולות SQL ו-SQL Spatial ובנוסף כולל מידע פתוח עולמי, חלקו גיאוגרפי (overture maps, open street map) כבסיס נתונים לתישאול. במידע הפתוח יש גם מידע על מבנים, עסקים,גבולות ישובים ועוד.
[המידע הזה לא רשמי או מסחרי אבל מספיק טוב למשימה מסוג זה]
אז איך עונים על השאלות הללו? איך אפשר להציג אותן על מפה או בטבלה בגליון אלקטרוני?
מתכון למציאת כמויות מבנים ועוד במדינה
מצרכים:
חשבון גוגל סטנדרטי
פרויקט GCP
קורט SQL
תבלין Spatial SQL
תבנית אפיה של גוגל שיטס
אופן ההכנה:
פתיחת פרויקט ב- GCP, אם אין לכם. (ראו לינק בפוסט קודם איך עושים את זה)
כתיבת שאילתה לקבלת שטח המדינה המבוקשת והמידע הרצוי בתוכה (ראו מטה בסוף)
הפעלת מפה לתצוגה (אם רוצים)
פתיחת גליון גוגל שיטס חדש והתחברות לשאילתה (אם רוצים)
את התוצאות, אם רוצים לנתח הלאה, אפשר לפתוח בדשבוארד, ב-Looker Studio של גוגל ולשלב עם מידע אחר שיש לכם. אפשר גם בקלות לשנות את השאילתה למידע ומדינות אחרות.
הסבר מפורט
אחרי שפתחתם פרויקט ב-Google cloud-Big query פותחים שאילתה חדשה דרך הדשבוארד או על ידי לחיצה על ה + הכחול למעלה:
עכשיו, ייפתח טאב של שאילתה חדשה בשם “Untitled query”.
העתיקו הדביקו שם את קוד ה-SQL שכתבתי מטה (הסבר טכני בהערות בתוכו) ולחצו על כפתור RUN או ctrl+enter. ויש לכם טבלה עם תוצאות שעונה על השאלה: כמה מבנים יש בישראל וכמה לתימן.
עכשיו אפשר לקחת את המידע ולהמשיך לעבוד איתו, על מפה, בסטודיו או לייצא לקובץ (פירוט בסוף פוסט קודם)
בדוגמא אני מכין מפה מהתוצאה ובמקום להציג המון המון מבנים שיעמיסו על המפה (אם בכלל הדפדפן יצליח לצייר אותם) אני מקבץ את מיליוני המבנים בכל מדינה באגרגציה לפי כמויות בצורה ברורה יותר הנקראת H3 hexagon של Uber. התצוגה מציגה ריכוז כמויות של מבנים ברשת של הקסגונים על המפה עם צבע לפי כמות.
התוצאה מראה בבירור גם איפה הריכוזים הגבוהים וגם איפה אין כמעט מבנים ואוכלוסיה.
אפשר גם להשתמש בפיצ’ר החדש והממש מועיל: חיבור לגוגל שיטס.
פשוט לבחור google sheets בתפריט save results מעל תוצאות הטבלה מימין:
הלחיצה תפתח את התוצאות בגוגל שיטס, מקושרות לדאטה.
משם אפשר לשתף, להמשיך לנתח וכל מה שבא לכם, כאשר אם תשנו את השאילתה
(אפשר גם ישירות דרך הגליון) תקבלו תוצאות מעודכנות! וזה כבר נושא לבלוג
אחר.
אפשר גם להציג את התוצאה על מפה של גוגל! באותו התפריט אפשר לפתוח:
Open in GeoViz ואז למלא אחר ההנחיות במפה.
בשורה התחתונה:
🇮🇱 ישראל:
שטח: 22,000 קמ”ר
מבנים : 1,357,986
מבנים מעל 10 קומות: 2,833
כבישים: 463,118
מסעדות: 3,466
בתי מרקחת: 526
🇾🇪 תימן:
שטח: 555,000 קמ”ר
מבנים : 4,876,253
כבישים: 257,873
מבנים מעל 10 קומות: 2 (כן, זו לא טעות)
מסעדות: 132
בתי מרקחת: 57
בתיאבון! ושנדע ימים שקטים יותר עם השכנים הקרובים והרחוקים ומתכונים אמיתיים לאוכל תימני.
--This exmaple query will result in a table that counts all restaurants and pharmacies in Yemen vs Israel
--get ready the area of intrest shape ofcountries from overture maps (Isreal, Yemen countries area)
with Yemen as
(
SELECT st_simplify(geometry,100) as geom
from `bigquery-public-data.overture_maps.division_area`
WHERE country='YE' and subtype='country'
),
Israel as
(
SELECT st_simplify(ST_UNION_AGG (geometry),100) as geom
from `bigquery-public-data.overture_maps.division_area`
WHERE country='IL' and (subtype='country' or names.primary='Golan Subdistrict')
)
--Now we will count how many pharmacies and restaurants are in each country
SELECT
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Israel
where ST_intersects(Israel.geom, Places.geometry) and categories.`primary`='pharmacy'
) as count_pharm_IL,
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Yemen
where ST_intersects(Yemen.geom, Places.geometry) and categories.`primary`='pharmacy'
) as count_Pharm_YE,
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Israel
where ST_intersects(Israel.geom, Places.geometry) and categories.`primary`='restaurant'
) as count_rest_IL,
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Yemen
where ST_intersects(Yemen.geom, Places.geometry) and categories.`primary`='restaurant'
) as count_rest_YE
--This is example using the POI (Point of intrests) from overture world data
--You can easiy chang it to count buildings or roads:
--just replace the layer name with: `bigquery-public-data.overture_maps.building` or 'segment' for roads.
--BUT BEWARE!!! of the huge amount of data each query sized, it will easily be above the free tier for BQ which is 1TB per month, and you will be charged with your credit card.