Something

איך מוצאים כמה מבנים יש בארץ?

כמה מבנים יש במדינה? למי יש יותר מבנים? ישראל או תימן?

איך בודקים במהירות כמה מבנים, ישובים, נקודות עניין יש במדינה ספיציפית?

למשל ישראל מול תימן: כמה מבנים יש בכל מדינה? כבישים? מסעדות?

מבנים מסוג מסוים,  מעל מספר קומות או גובה ,  כמה מסעדות, בתי מרקחת ובעצם כל דבר שרק נרצה דעת ולנתח, הכל בעזרת מידע פתוח, בענן או בתוכנות פתוחות בעזרת 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) כבסיס נתונים לתישאול. במידע הפתוח יש גם מידע על מבנים, עסקים,גבולות ישובים ועוד.

[המידע  הזה לא רשמי או מסחרי אבל מספיק טוב למשימה מסוג זה]

אז איך עונים על השאלות הללו? איך אפשר להציג אותן על מפה או בטבלה בגליון אלקטרוני?

איך מוצאים כמה מבנים יש בארץ? בניינים בישראל לפי כמות H3 hexagon הקסגון על מפת גוגל טופו

מתכון למציאת כמויות מבנים ועוד במדינה

מצרכים:

  • חשבון גוגל סטנדרטי

  • פרויקט 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

 

בתיאבון! ושנדע ימים שקטים יותר עם השכנים הקרובים והרחוקים ומתכונים אמיתיים לאוכל תימני.

 

בניינים בישראל לפי כמות H3 hexagon הקסגון על מפת גוגל
How to find how many buildins are in a countryבניינים בתימן לפי כמות H3 hexagon הקסגון על מפת גוגל
				
					--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.
				
			
results of the query: Yemen vs Israel

שתפו מאמר

פייסבוק
טויטר
פינטרסט
לינקדין
וואטספ
טלגרם

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *

מעבר להודעה
1
שלום לך!
אני רן, המייסד של Mikoom
אשמח לענות לך על כל שאלה.
לך נשאר רק ללחוץ על הכפתור למטה לשליחת הודעה.
דילוג לתוכן