יותר

מצא את הטווח הרחב הקרוב ביותר לקלט הרחב (SQL Server 2008)


יש לי ענן נקודות במסד הנתונים שלי (SQL Server 2008 מרחבי). מדובר בכ -6 מיליון שיאים. יש 3 עמודות: id, value, geom. מהי הדרך האופטימלית להשיג את 'הערך' בכניסה לטווח ארוך ??

אני חדש בשאילתות מרחביות ב- SQL Server 2008. מישהו יכול להעלות דוגמא פשוטה למציאת הנקודה בעמודה geom, תואמת או הקרובה ביותר מהקלט לטווח ארוך?


מה שאתה מחפש הוא שאילת השכנים הקרובה ביותר. תסתכל על הקישורים הבאים, אני חושב שתמצא את מה שאתה מחפש.

שאילת השכנים הקרובה ביותר

השכנים הקרובים ביותר

אופטימיזציה של השכן הקרוב ביותר ב- SQL Server Denali


זה משתמש בגיאוגרפיה ולא בגיאומטריה (אם הנתונים Lat/Lng הנתונים צריכים להיות מסוג גיאוגרפיה לא גיאומטריה)

"סוג הנתונים של הגיאוגרפיה של שרת SQL שומר נתונים אליפסואידים (כדור הארץ), כגון קואורדינטות קו אורך ורוחב GPS."

לבחירת 5 הרשומות הקרובות ביותר מנקודה lat/lng (-122.0 37.0) בה תוכל להשתמש.

בחר 5 גיאוגרפיה למעלה :: STGeomFromText ('POINT (-122.0 37.0)', 4326) .STDistance (p) מתוך סמנים היכן הגיאוגרפיה :: STGeomFromText ('POINT (-122.0 37.0)', 4326) .STDistance (p) <25 סדר לפי גיאוגרפיה :: STGeomFromText ('POINT (-122.0 37.0)', 4326) .STDistance (p);

סוגים מרחביים - גיאוגרפיה

סוג הנתונים המרחבים הגיאוגרפיים, גֵאוֹגרַפיָה, מיושם כסוג נתונים של .NET בשפה נפוצה (CLR) בשרת SQL. סוג זה מייצג נתונים במערכת קואורדינטות סביב כדור הארץ. שרת SQL גֵאוֹגרַפיָה סוג הנתונים מאחסן נתונים אליפסואידים (כדור-הארץ), כגון קואורדינטות רוחב וקו אורך GPS.

שרת SQL תומך בקבוצת שיטות עבור גֵאוֹגרַפיָה סוג נתונים מרחביים. זה כולל שיטות בנושא גֵאוֹגרַפיָה המוגדרים על ידי התקן Open Geospatial Consortium (OGC) וסט של תוספי Microsoft לתקן זה.

סובלנות השגיאות עבור גֵאוֹגרַפיָה השיטות יכולות להיות גדולות עד 1.0e-7 *. ההיקפים מתייחסים למרחק המקסימלי המשוער בין נקודות ה גֵאוֹגרַפיָהלְהִתְנַגֵד.


מצא את הטווח הקרוב ביותר לקלט הרחב (SQL Server 2008) - מערכות מידע גיאוגרפיות

אז ניסיתי לחשוב על כמה דוגמאות שעושות שימוש בנתוני POI של Garmin מהפוסט האחרון שלי. מכיוון שנתוני POI מתאימים באופן טבעי לשאילתות של השכן הקרוב, חשבתי לכתוב מעט על אלה.

הצורה הכללית של שאילתת השכנים הקרובה ביותר היא: "הראה לי את x המסעדות/פאבים/תחנות הרכבת הקרובות ביותר למיקום זה"זהו דפוס השאילתה המשמש את היישומים המודעים למיקום לאיתור נקודות עניין הקרובות למיקום הנוכחי של המשתמש, למשל. עם זאת, דפוס אחר שראיתי הרבה פחות דוגמאות הוא כיצד לעדכן טבלת רשומות שלמה על מנת לקבוע ולאכלס את השכן הקרוב ביותר עבור כל שורה בטבלה (אכן, שאלה זו בדיוק עלתה בפורום המרחבי של MSDN רק בשבוע שעבר).

עם זאת, חשבתי שאדגים תרחיש מעשי: נניח שאתה מתכנן לשחות בבריכה המקומית, ואחרי ששחקת 50 אורכים, סביר להניח שתרגיש קצת מגוחך - אולי ב הצורך בכמה פיש אנד צ'יפס. אז בדוגמה זו אני קודם כל מייבא את נתוני ה- POI של Garmin לבריכות שחייה ולחנויות צ'יפס בבריטניה, כל אחת מיוצגת על ידי מופע גיאוגרפיה פוינט. לאחר מכן אוסיף טור נוסף לשולחן הבריכה ואאכלס אותו בחנות הדגים והצ'יפים הקרובה ביותר לכל בריכת שחייה.

ראשית, צור טבלאות המכילות את הנתונים של חנויות הדגים והצ'יפס ובריכות השחייה (הורד את הנתונים מכאן). שים לב שכללתי שדה מפתח ראשי של IDENTITY בכל טבלה, שאצטרך כדי להוסיף אינדקס מרחבי מאוחר יותר:

כעת נוסיף כמה טורים נוספים לשולחן בריכות השחייה, אותם נאכלס במידע על חנות הדגים 'צ'יפס הקרובה ביותר:

עכשיו, אתה לא צוֹרֶך להוסיף אינדקס מרחבי לביצוע שאילתת העדכון, אבל זה יהיה הרבה יותר מהיר עם אחת מאשר בלי. שרת SQL יכול לעשות שימוש באינדקס מרחבי אחד בלבד בחיבור בין הטבלאות, כך שאין צורך להוסיף אינדקס לשתי הטבלאות. רק נוסיף אחד לשולחן פיש אנד צ'יפס, כדלקמן:

לאחר מכן, הפעל שאילתת UPDATE כדי לאכלס את Bar הקרוב ביותר ו מֶרְחָק עמודות, כדלקמן:

שאילתה זו כנראה דורשת הסבר כלשהו:

  • באופן כללי, אתה עשוי לצפות להשתמש בשאילתת משנה המתואמת כדי להשיג את חנות הדגים והצ'יפס הקרובה ביותר לכל בריכת שחייה. עם זאת, שאילתות משנה המתואמות יכולות להחזיר ערך יחיד בלבד, ואני רוצה לאחזר את השם ואת המרחק לסרגל הדגים הקרוב ביותר, ולכן השתמשתי במקום זאת ב- CROSS APPLY.
  • בפונקציה המיושמת, אני ממיין את שולחן חנויות הדגים והצ'יפס בסדר עולה של מרחקן מבריכת השחייה הנוכחית (הזמנה מאת FishAndChips.Location.STDistance (SwimmingPools.Location) ASC) ולאחר מכן בחר את למעלה 1 שם ומיקום (כלומר הקרוב ביותר).
  • כדי להפוך את השאילתה ליעילה, הוספתי גם רמז לאינדקס לשימוש באינדקס המרחבי בטבלת הדגים והצ'יפס - WITH (אינדקס (sidx_FishAndChips)), וכללתי פרדיקט נוסף, היכן FishAndChips.Location.STDistance (SwimmingPools.Location) אינה אפס. פרדיקט נוסף זה יעזור לאופטימיזציית השאילתות לבחור את תוכנית השאילתות הייעודית של השכן הקרוב ביותר שהוצגה ב- SQL Server 2012 ו- SQL Azure.
  • שים לב שתוכנית השאילתות השכנות הקרובות ביותר לשכונה הקרובה ביותר המותאמת לאינדקס זמינה רק ב- SQL Server 2012/SQL Azure. אם תנסה לבצע את שאילתת העדכון כפי שנכתב למעלה ב- SQL Server 2008/R2, תקבל שגיאה:

מעבד השאילתה לא הצליח לייצר תוכנית שאילתה לשאילתה עם רמז לאינדקס מרחבי. סיבה: אינדקסים מרחביים אינם תומכים במשווה המסופק בפרופיל. נסה להסיר את רמזים לאינדקס או להסיר את SET FORCEPLAN.

כאמור, כדי לפתור זאת יהיה עליך להסיר את WITH (אינדקס (sidx_FishAndChips)) רמז לאינדקס (וגם לצפות שהשאילתה שלך תרוץ הרבה יותר לאט!). במקום זאת, ייתכן שתצטרך לנסות את אחת הגישות האלטרנטיביות לאיתור יעיל של השכנים הקרובים ביותר ב- SQL Server 2008/R2.

שאילתה זו אמורה להימשך מספר שניות (או אם אינך יוצר/משתמש באינדקס המרחבי, מספר דקות), ולאחר מכן תוכל לבדוק את התוצאות כדלקמן:

ויש לך את הטבלה המלאה של בריכות השחייה, שכל אחת מהן רשומה עם השם והמרחק לחנות הדגים והצ'יפס הקרובה ביותר שלה:

מכיוון שזה פחות ממאה מטרים מהיציאה מהמים לשקית צ'יפס, אני חושב שאטבול את הטבילה הבאה שלי בבריכת השחייה של פארנוורת '.


מערכת הפניה מרחבית

קו הרוחב והאורך נחשבים לערכים אבסולוטיים המקובלים באופן אוניברסלי, אך אין זה כך. למשל, מרידיאנים ראשיים, או קו אורך של אפס, הם די שרירים. הוא מכונה בדרך כלל קו צפון-דרום שעובר בגריניץ ', לונדון, אנגליה, אך למען האמת, ישנם ארבעה ראש מרידיאן שונים שהיו בשימוש היסטורי שעוברים למעשה בגריניץ'. אפילו הפריים מרידיאן הנוכחי המשמש את מערכת ה- GPS (ורוב אתרי מיפוי האינטרנט) נמצא למעשה 102.5 מטרים ממזרח לקו "הרשמי" המסמן את ראש המרידיאן המקובל אחרת.

ברגע שאתה יודע את נקודות ההתחלה המדויקות של מערכת הקואורדינטות בה משתמשת מערך הנתונים, נותר לנו דילמה נוספת כאשר אנו מנסים לחשב את המרחק בין שתי נקודות. בגלל פעולתו של כדור הארץ המסתובב על צירו, הוא אינו למעשה בצורת כדור, אלא אליפסיד (הוא שמן יותר באמצע מאשר בקטבים). זה מסבך את היכולת לחשב במדויק את המרחק והשטח בצורה אחידה, אך לא הופך את זה לבלתי אפשרי.

כדי למדוד מרחק בין שתי נקודות על הגלובוס, עלינו לדעת את הרדיוס של כדור הארץ (כלומר, כמה רחוק ממרכז כדור הארץ שכל נקודה נמצאת בו). הבעיה היא שבגלל המוני היבשה, לכדור הארץ אין רק רדיוס אחיד אחד שניתן ליישם ברחבי העולם. אם תמדוד את המרחק בין שתי נקודות המרוחקות מעלה אחת בגובה פני הים, תקבל תוצאה שונה משתי נקודות המרוחקות זו מזו בהרים, העשויים להיות 5000 רגל מעל פני הים.

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

עם היקף נתוני ה- GPS, אני טוען כי מערכת ההתייחסות המרחבית הנפוצה ביותר כיום היא WGS84.


משימות קשורות

צור, בנה ומופקות גיאומטריה של שאילתה
מתאר את השיטות שבהן תוכל להשתמש עם מופעים מסוג הנתונים של הגיאומטריה.

צור, בנה ושאיר מופעים גיאוגרפיים
מתאר את השיטות בהן תוכל להשתמש עם מופעים מסוג הנתונים הגיאוגרפיים.

שאילתא נתונים מרחביים לשכן הקרוב ביותר
מתאר את תבנית השאילתה הנפוצה המשמשת לאיתור האובייקטים המרחביים הקרובים ביותר לאובייקט מרחבי ספציפי.

צור, שנה ושחרר אינדקסים מרחביים
מספק מידע אודות יצירה, שינוי והשמדה של אינדקס מרחבי.


מה שהופך אותו לקל יותר לעין

צוות השיווק שמח מאוד שהם יכולים כעת לראות היכן מתבצעות כל המכירות שלהם, ולכן מקבלים החלטות עסקיות טובות יותר. עם זאת, ישנה בעיה מכיוון שחלק מהאזורים מתנהלים טוב יותר מאחרים והסיכות כולן אחת על השנייה מקשות מאוד על המפה. כדי לפתור את זה ולהקל על העין, אנו הולכים לקחת ייעוץ מתפקוד האשכולות של ה- API. כאשר האשכולות מופעלים, מפות Bing יקבצו את כל הסיכות הנמצאות קרובות זו לזו ומציגה אייקון מיוחד. זה מאפשר למשתמש לדעת שיש מספר סיכות לחיצה במיקום זה. אם המשתמש מתקרב, ממשק ה- API יתחיל לבטל את הקבוצה באופן אוטומטי מכל הסיכות. בדוגמה זו אנו הולכים להשתמש באותה שאילתה כמו בדוגמה האחרונה ולהוריד אותה אוטומטית על טעינת הדף. ההבדל היחיד הוא שאנחנו הולכים לאסוף את התוצאות באופן אוטומטי ולתת למשתמשים להחליט אם הם רוצים לראות סיכות סיכה מקובצות או לא מקובצות. איור 17 מציג את פונקציית javascript של GetMap השונה מעט מהקודמת שלנו.

map.SetCenterAndZoom (VELatLong חדש (43.79488907226601, -121.014), 6)

איור 17: פונקציית javascript של GetMap שמקבצת באופן אוטומטי את כל התוצאות.

כדי להפעיל את האשכולות עלינו להוסיף שכבה על גבי המפה, להגדיר את תצורת אשכולות השכבה & rsquos ל- VEClusteringType.Grid והוסף את כל סיכות הדחיפה לשכבה במקום המפה. הפעלה או כיבוי של אשכולות היא רק עניין של שינוי התצורה של אשכולות השכבות ו- rsquos רֶשֶׁת ל אף אחד. איור 18 מראה כיצד לעשות זאת ואיור 19 ו -20 מציגים את התוצאה הסופית.

איור 18: Javascript לאשכול וללא אשכולות

איור 20: התקרב בתצוגה של סיכות מקובצות


מצא את הטווח הקרוב ביותר לקלט הרחב (SQL Server 2008) - מערכות מידע גיאוגרפיות

אני חושב שמיקרוסופט צריכה להציג ראיות נוספות המגבות את הפרשנות הזו של המפרט. לפרשנות זו תהיה עלות גבוהה מאוד ללקוחות מבחינת יכולת פעולה הדדית ומחסום באימוץ טכנולוגיות מיקרוסופט.

שאר התעשייה פירשה את התקן כ- lon/lat אם מיקרוסופט מתכוונת לפרש זאת אחרת, עליהן לספק הסבר חסין כדורים המצדיק מדוע הן אחת מהיישומים היחידים המשתמשים במסדר lat/lon ב- SQL.

ההבנה שלי היא שרוב חברי ה- OGC פירשו תקן זה כ- lon/lat. כך שגם אם מיקרוסופט צודקת בפרשנותה, הוועדה הצביעה על משמעות התקן עם המקלדות שלהן. אני חושב שזה לא סביר שמיקרוסופט תילחם בקרב תאימות זה באמצעות בסיס הלקוחות שלה-זה אמור להתרחש בחדרי העשן המעושנים של פגישות OGC.

האם תוכל להראות היכן המסמך, העמוד, הפסקה והמשפט הזה, היכן שהוא קובע שהתקן חל רק על מערכות מישוריות או שאינו כולל מערכות גיאוגרפיות? קראתי את המסמך מספר פעמים ושהעמיתים שלי בדקו אותו גם כן. שום דבר לא גרם לאף אחד מאיתנו להאמין שמקרי הנדסה גיאוגרפיים אינם נכללים.

6.1 מתאר את הארכיטקטורה של מודל הגיאומטריה, הקובע בבירור כי כל אובייקט גיאומטרי משויך למערכת & quotSpatial Reference System & quot. זה אינו מגביל זאת למערכות קואורדינטות פרויקט.

סעיף 9 מתאר את ייצוג ה- WKT של מערכות הפניה מרחבית. זה מכסה הן מערכות גיאוגרפיות והן מערכות צפויות. בשום מקום זה לא מגביל את המפרט למערכות מישוריות בלבד.

נספח B.8 לחלק 1 של ה- SFA קובע כי "קו הרוחב של המקור" הוא קו הרוחב שנבחר כמקור y-coordinates & quot. למזרח שווא/לצפון יש הגדרה דומה. סעיף זה סותר כל מושג בשימוש בהזמנת CRS עבור X ו- Y, שכן הוא מגדיר במפורש את X/Y במערכת קואורדינטות מוקרנת.

כיצד מיקרוסופט פותרת את הקונפליקט שבו CRS עשוי להקרין את קו הרוחב ל- X במקום לאורך? האם אתה דוחה את ההגדרה המסופקת בתקן, או שאתה עוזר לשיטה הטובה ביותר?

כפי שצוין, WKT כהגדרתו במפרט SFA אינו משתמש בסימון מתאים למערכות לא מישוריות. אז אם היינו מניחים שמיקרוסופט נכונה וה- SFA חל רק על מישוריים, אז זה יהיה לא סטנדרטי לייצג כל מערכת קואורדינטות גיאוגרפית באמצעות WKT לפי SFA. במקרה זה, מיקרוסופט צריכה לדחות את הסטנדרטים של defacto.

אם מיקרוסופט בוחרת לעשות זאת לבד ביישום זה, אז הייתי מציע לעשות זאת ברור לחלוטיןש- SQL Server מיישם את תקן OGC 05-126 באמצעות שיטות העבודה המומלצות של יוני 2005/06-135r1 וכי תקן זה שונה במובהק מ- OGC 99-049.

במקרה כזה, צריך שיהיה מצב תאימות OGC 99-049 שמיישם את הסכמת lon/lat. בעת שימוש בתקן OGC 99-049, ה- lon/lat הוא ה- נכון פרשנות לתקן, כפי שמעידים אישורי OGC על מוצרים מרחביים אחרים.

אי הכללת יישום תואם OGC 99-049 תיצור בעיה רצינית של יכולת פעולה הדדית שתמנע מהארגון שלי לאמץ שרת SQL.

אנו מבינים את בעיית ההדדיות ואנו בוחנים כיצד להקל על גישור הפער הזה. תן לי לנסות לפרט מדוע קיבלנו את ההחלטה הזו. כנקודת מוצא:

נראה כי אלה יחד מעוררים ספק בדרך הנכונה להתמודד עם זה. עם זאת, התקנים באים לידי ביטוי:

במהלך מליאת TC הסופרת לישיבת יוני 2005, הסכימו החברים הנוכחים כי:

1. בהמשך, עבור מפרטים חדשים, ערכי קואורדינטות יהיו רשומים בסדר הציר כפי שצוין על ידי מערכת ההתייחסות של הקואורדינטות (CRS).

2. בהמשך, כאשר RWG עובד על עריכות במפרט הקיים שאומץ הקשור ל- CRS ולציר הציר, ערכי הקואורדינטות יופיעו בסדר הציר שצוין על ידי מערכת ההתייחסות של הקואורדינטות (CRS).

  • לבסוף, רשימת ה- CRG של EPSG הסטנדרטית, שאנו מאמצים לסוג הגיאוגרפיה, באופן אחיד משתמש בסדר קווי אורך/קו אורך עבור כל מערכות הקואורדינטות הגאוגרפיות.

אנו מבינים כי הדבר יגרום לאנשים לבעיות, ואנו בוחנים דרכים להקל עליהם, אך אני מקווה שזה יעזור לך להבין את הנימוקים שלנו.

תן לי לחזור על זה שאני מבין שאנחנו עושים משהו קצת שונה משאר השחקנים ושזה גורם לכאב. אנו מקשיבים לקלט שאנו מקבלים על כך, ואנו בוחנים כיצד אנו יכולים להקל על ייבוא/ייצוא של נתונים בינינו לבין מערכות אחרות.

עם זאת, אני לא מסכים לגבי הסטנדרטים. אני חושב שהם אומרים מעט מאוד על קואורדינטות גיאוגרפיות, ומעט מה שהם אומרים מגבה אותנו.

ראשית, בואו נסתכל על OGC 99-049. למרות שאני מסכים שהם אינם מצהירים במפורש שהם אינם כוללים מערכות גיאוגרפיות, הם אומרים כי תכונות & quot מבוססות על גיאומטריה דו -ממדית עם אינטרפולציה לינארית בין הקודקודים & quot [תחילת סעיף 1]. זה מעיד עליי שהם עובדים על המטוס.

כמו כן, אם נניח שהמפרט מכסה קואורדינטות גיאוגרפיות, נגלה שהמפרט אינו מספיק. לְמָשָׁל.:

כמו כן, זכור כי למרות שאנו מנסים להישאר ככל האפשר ל-99-049 עם סוג הגיאוגרפיה שלנו, איננו מכוונים לתאימות שהיא רק מטרה קשה עם סוג הגיאומטריה שלנו. אנו נטען כי עמידה ב-99-049 למערכות גיאוגרפיות אינה הגיונית במיוחד, ונשמח לראות תקן המתייחס למערכות גיאוגרפיות.

לבסוף, לגבי 05-126, בעוד שהציטוט שאתה מביא הוא מדויק, אני לא רואה בו רלוונטי: אני לא חושב שמישהו טוען שבתחזיות מפה (ביותר), קו הרוחב מיוצג לאורך ציר ה- y של הציר עלילה. אני מוצא את זה מעניין שסעיף 6.4.2 קובע, & quotA מערכת התייחסות מרחבית, המכונה גם מערכת קואורדינטות, היא גיאוגרפית (קווי אורך ורוחב), מערכת קואורדינטות מוקרנת (X, Y) או גיאוצנטרית (X, Y, Z). & quot [הדגש שלי]

עם OGC 99-049, OGC אישר מערכות אחרות לשימוש עם מערכות קואורדינטות גיאוגרפיות באמצעות ציר lon/lat הזמנת מערכות גיאוגרפיות. גם אם הפרשנות שלך נכונה לפי אות התקן, היא שגויה בהתבסס על הנוהג, היישום והבדיקה של התקן. הוועדה הוכיחה את כוונתה ליישם תקן זה על מערכות גיאוגרפיות.

כן-אתה צודק לחלוטין ש- 99-049 אינו מספיק לקוידינטות גיאוגרפיות. אם גרמתי לזה להישמע כאילו האמנתי אחרת, אני מתנצל כי זו אף פעם לא הייתה כוונתי.

עם זאת, חוסר התאמה או עמימות אינן כוונה. אני מאמין שפעולות ה- OGC wrt 99-049 אכן מראות את הכוונה של OGC בעת יישום התקן הזה-ושום דבר שאמרת לא מראה אחרת (ציינת מה חשדתי-הסטנדרטים של OGC אפויים למחצה צריך הרבה עבודה כדי להיות & quotgood & quot). אלא אם כן אתה יכול להראות ש- OGC אישר מערכות אחרות בצורה לא נכונה או שהאישור שלהן כלל את החלקים הגיאוגרפיים של ההטמעות, אז אני חושב שהעמדה של מיקרוסופט עדיין שגויה.

הציטוט די רלוונטי. אני מציין שיש עמימות בתוך התקן במערכות גיאומטריות-לא רק במערכות גיאוגרפיות. כיצד תטפל באותם עמימות ו/או קונפליקטים? או, וחשוב מכך, האם תבחר יישום השונה משאר התעשייה?

אם אתה למעשה עובד לקראת תאימות 99-049 (מפרט תכונות פשוטות ל- SQL), הפגישה ביוני 2005 לא תחול (מכיוון שהיא תקן שמתאריך פגישה מראש-עדכונים אינם נחשבים כחדשים). אם אתה עובד לקראת 05-126/06-104r3 (מפרט יישום למידע גיאוגרפי-גישה פשוטה לתכונה), אז ייתכן שיחול פגישה ביוני 2005 מכיוון שהיא חָדָשׁ תקן שהעלה את 99-049.

אני עדיין מאמין שהפתרון הטוב ביותר הוא לאפשר ללקוחות לבחור אם הם רוצים להשתמש ב- 99-049 המיושן כפי שיושם על ידי שאר התעשייה או 06-104r3 כפי שהוא מיושם בקטמאי החל מה- CTP של נובמבר.

הקפד לשקף באיזה תקן אתה תומך במסמכי העזרה שלך, מכיוון שהם טוענים 06-104r3.

אני לא יכול להוסיף הרבה לדיון על סטנדרטים. במהלך השנים, כמנהל GIS, שמתי עליהם עין כדי לבדוק אם הם יועילו בארגון שעבדתי עבורו, אך מצאתי שהם שימושיים רק בתחום ההדדיות הבסיסית (כלומר בסיסית).

עכשיו מה שאני מצפה מקטמאי הוא פונקציונאליות וגמישות מנקודת מבט של מסד נתונים עצמאי. הבעיה שיש לי בהתבסס על הניסיון המוגבל שלי עם הזמנת קואורדינטות lat/long בסוג הגיאוגרפיה היא פחות ההזמנה (הערה: מפות Google משתמשות ב- Lat/Long - במה משתמשת Virtual Earth?) אבל שאין פונקציות זמינות למפה בין גיאומטריה וגיאוגרפיה או ממשק API גמיש לגישה לאובייקטים של הגיאומטריה/גיאוגרפיה. לדוגמה, אינני יכול לעדכן כרגע את הקואורדינטות של גיאומטריה/גיאוגרפיה למעט באמצעות פריצה של מחרוזות WKT (שהן מוגבלות דו -ממדית ואינן כוללות SRIDS). שים לב שאמרתי UPDATE ולא READ. כן, אנו יכולים לקרוא קואורדינטות באמצעות STPointN אך איננו יכולים לעשות משהו בקלות כמו לשדרג אובייקט דו -ממדי לתלת -ממד (או הפוך) אלא באמצעות WKT. שים לב, עיבוד אובייקטים כלשהו אינו דורש ידיעה אם הוא מעבד את הטבעת החיצונית או הפנימית של מצולע וחלק בשורה קווית מרובת חלקים.

חברי אנאנד קנן במפה אינפו שלח לי קצת T-SQL כדי להפוך את ה- XY, Lat/Long המסתמך על עיבוד מחרוזת WKT. הסתכלתי על כתיבת פונקציה דומה והגעתי לאותה מסקנה: זה ידרוש פריצה של מחרוזות (התסריט של אנאנד הגיע יום אחרי שהחלטתי שאני לא הולך לרדת למסלול הזה). זה מכוער, איטי ויש לו קשיים אחרים (למשל בינלאומיות ושימוש בפסיקים כנקודות עשרוניות באירופה וכו ').

כתבתי המון המון פונקציות נוספות משלי ב- PL/SQL של ​​אורקל בדיוק מכיוון שאני יכול לגשת לכל היבט של פורמט האחסון. אני לא רוצה לכתוב C# (כדי לקרוא/לכתוב WKB או WKT) ולפרוס את הקוד למסד הנתונים אם אוכל לעזור לו.

לכן, אם אנו תקועים עם הזמנה לטווח ארוך/טווח התביעה העיקרית שלי היא לראות הרבה יותר כוח שנפרס למאגר הנתונים לעיבוד T-SQL באמצעות ממשק API הרבה יותר עשיר שמכוסה כיום על ידי כל תקן OGC או SQL/MM. אני למשל לא רוצה לתאם חבילת GIS של לקוח שמנים (Fat-client לא אמור להיות גנאי) ותהליך מסד נתונים כדי לעבד נתונים.

טוב לראות אותך פה! :-) אבל אני חייב לא להסכים עם האמור לעיל: סדר הקואורדינטות להקרנת & quotLatitude / Longitude & quot (המכונה גם & quotlat / lon & quot) ב- KML שהוגדרה על ידי Google הוא lon / lat.

תן לי להחליף הילוכים כדי להגן על מיקרוסופט, שלדעתי מסיח את הדעת מסיח הדעת בשרשור הזה עם דיון על ניתוח OGC מוש. זאת לא הנקודה. הנקודה היא לגבי סוג חדש, GEOGRAPHY, שיכול להפוך את החיים לקלים יותר עבור חלק מהמשתמשים. זו משימה שונה ממשהו כמו GEOMETRY המסופק לשימוש עם נתוני GIS קיימים על ידי אנשי GIS מנוסים.

אני מרגיש שחלק מהאנשים שקוראים שרשור זה הם חדשים בנתונים גיאו -מרחביים ואינם מבינים שהמשפט האנגלי הנפוץ & quotLatitude / Longitude & quot & quot; אתה לא לבד אם זה מה שציפית.

זה לא נועד כביקורת, כי במקום זאת זהו טיעון מאוד מוצק לכך שהקואורדינטות מסדירות את המצב כפי שהוא בסוג הגיאוגרפיה אם זה אכן מיועד לאנשים שאינם אנשי GIS. אם הנקודה של גיאוגרפיה היא שיהיה משהו לסוגים שאינם GIS הסדר (lat, lon) הגיוני לחלוטין.

הדבר הטבעי ביותר בעולם עבור מישהו חדש בנתונים גיאו -מרחביים הוא לחשוב, לא, * לצפות * שכאשר מישהו יגיד שיש לו נתוני & quot כמו שהביטוי האנגלי נהוג לתאר אותו אומר שכן. אנשים המכירים נתונים גיאו -מרחביים מבינים שהביטוי האנגלי הוא רק ביטוי, לא תיאור מתמטי של סדר ציר קואורדינטות, אך בדרך כלל מתחילים אינם יודעים זאת. סעפת מכרה יותר GIS חובה למשתמשים חדשים מאשר כל שאר ספקי GIS יחד, כך שאני מבטיח לקוראים שזוהי תופעה אמיתית מאוד.

חלק ממה ש- SQL Server יכול לעשות על ידי הוספת הרחבות מרחביות הוא הרחבת התועלת של טכנולוגיה גיאו -מרחבית לקהל רחב בהרבה. ל- Microsoft יש את היכולת לעשות זאת מכיוון שספקים אחרים לא יכולים לעשות זאת. ליישב את הציפיות של מתחילים גיאו -מרחביים (גם אם הם עשויים להיות מומחים ב- DBMS ובכלל לא מתחילים בתכנות) עם התרגול המצטבר בתוך טרה -בייט של נתוני GIS הוא לא קל, אך שני סוגים הם חידוש שימושי שיכול לגרום לזה לקרות.

אם מיקרוסופט יכולה להפוך את החיים לקלים יותר לאנשים חדשים בנתונים גיאו -מרחביים באמצעות הזמנת קואורדינטות בגיאוגרפיה השונה מנתוני GIS מדור קודם, ובכן, זה בסדר גמור וסיבה מספיק טובה בשבילי. מה שזה לא יהיה, אנחנו נתמוך בזה. מיקרוסופט יישמה גם GEOMETRY למי שמשתמש בנתוני GIS כך שאין שום חיסרון למי שעובד עם נתוני GIS.

כפי שצרחתי בעבר, אם אתה משתמש בנתוני GIS מוטב שתאחסן אותם ב GEOMETRY כדי שלא תאבד מידע חיוני, כמו נתון, הנדרש לדיוק. השתמש ב- GEOMETRY עם נתוני GIS, בין אם מדובר בהקרנת & quotLatitude / Longitude & quot ובין אם לאו, ואין שום בעיה עם הזמנת קואורדינטות. לכן העצה שלי לסימון תהיה להשתמש ב- GEOMETRY ולשמח שיש לך את זה.

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

רק כדי שכולם יידעו שאני לא טוען באופן קפריזי שהסדר בגיאוגרפיה הוא בסדר כי אני לא מבין שהסדר (lon, lat) הוא אוניברסלי, תרשה לי להביא כמה דוגמאות ולתקן כמה תפיסות מוטעות שאולי התגנבו לשרשור הזה, החל מהרעיון שלכל אחד יהיה אכפת מה OGC עושה או תפיסה מוטעית לגבי מה GML או EPSG מציין.

ראשית, OGC & תקנים & quot הם כמעט חסרי תועלת בחיים האמיתיים. יצחק פגע בציפורן על ראשו כשכתב & quot כמו כן, אם נניח שהמפרט מכסה קואורדינטות גיאוגרפיות, אנו מגלים שהמפרט אינו מספיק חמור. & Quot נאמר היטב!

אם כבר מדברים על OGC ותקנים כמעט חסרי תועלת, זה מעלה GML, שתואר כידוע כ"אולי הפורמט הגרוע ביותר לנתונים גיאו-מרחביים, השני רק בכתב ברייל. "בהתחשב בכך ש- GML מייצג אולי פחות ממיליון מהנתונים הגיאו-מרחביים שם אל תחשוב שזה משנה בכלל מבחינה מעשית באיזה קואורדינטות של הזמנת GML משתמשת. אבל, אם אנחנו מפצלים שערות ומעמידים פנים ש- GML חשוב, אני חייב לא להסכים בכבוד להצעה ש- GML מציין (lat, lon) להזמין.

תקן GML קובע כי סדר הקואורדינטות צריך להיות בהתאם לזה של ההקרנה. לא מאוד שימושי מבחינה פרקטית. כך שלמרות ש- GML אכן כולל אמצעים לציון מפורש של סדר צירי הקואורדינטות, הוא אינו בוחר במפורש (lat, lon) על סדר (lon, lat). בכל מקרה השימוש ב- GML לנתוני lat/lon הוא כל כך נדיר שטרם ראינו קובץ GML בעולם האמיתי עם נתוני lat/lon ללא קשר לסדר הקואורדינטות.

לגבי EPSG, אני גם חולק על כך שבדרך כלל מתייחסים לזה כאל סדר (lat, lon): החבר'ה של EPSG עיצבו את המערכת שלהם כך שהצירים שבהם משתמשת מערכת קואורדינטות ממוספרים. מעולם לא שמעתי על מישהו (אורקל וכו ') המשקף את סדר הצירים האלה על ערכי קואורדינטות בפועל. אני יכול לראות את הטעם של שרת SQL עושה את זה כפי שהוא עושה ב GEOGRAPHY ואני מכבד את זה 100%, אבל אני חושב שכל ההצדקה שצריך היא שכך זה מתבצע ב- SQL Server, כמו איך שזה נעשה שם הופך ל- תקן עובדתי שנעקוב אחריו.

אתגרתי בעבר על כך שאמרתי כי (lon, lat) הזמנה היא & quotuniversal & quot; תרגול בנתוני GIS, אז תרשה לי לפרט מדוע אני קובע שזה המקרה: פורמטים בולטים אחרים שמשתמשים ב- (lon, lat) להזמנה עבור כך- תחזיות & quotLatitude / Longitude & quot:

1. קואורדינטות במערכות נתוני TIGER/Line המיוצרות על ידי לשכת המפקד האמריקאית נמצאות באורך רוחב. סדר הקואורדינטות הוא lon/lat.

2. קואורדינטות במערכות נתונים של NTAD המיוצרות על ידי הלשכה האמריקאית לתחבורה. סדר הקואורדינטות הוא lon/lat.

3. קואורדינטות ב- GDF ובמערכות נתונים אחרות המיוצרות על ידי TeleAtlas, חברת מיפוי אירופית גדולה, נמצאות ב- lat/lon. סדר הקואורדינטות הוא lon/lat.

4. קואורדינטות במערכות נתונים של VMap המיוצר על ידי משרד ההגנה האמריקאי עשויות להיות במספר מערכות קואורדינטות שונות. עבור מערכות קואורדינטות lat/lon סדר הקואורדינטות הוא lon/lat.

5. קואורדינטות ב- SHP (ESRI & quotshapefiles & quot), MIF (MapInfo) וקבצים אחרים המיוצרים על ידי חבילות GIS נוכחיות עשויים להיות במערכות קואורדינטות רבות ושונות. עבור מערכות קואורדינטות lat/lon סדר הקואורדינטות הוא lon/lat במערכות אלה.

6. קואורדינטות ב- DGN (Intergraph, Bentley), DWG (Autodesk ועוד רבות אחרות), DXF וקבצים דומים המיוצרים על ידי חבילות CAD נוכחיות עשויות להיות במערכות קואורדינטות רבות ושונות. עבור מערכות קואורדינטות lat/lon סדר הקואורדינטות הוא lon/lat.

7. קואורדינטות בערכי WKB / WKT המאוחסנים במאגרי מידע מסורתיים כגון Oracle, DB / 2, PostgreSQL ו- MySQL (שאין להם מקבילים לסוג הגיאוגרפיה אך בכל זאת משמשים לאחסון נתונים רבים במחזור הנוכחי) עשויים להיות בהרבה מערכות קואורדינטות שונות. עבור מערכות קואורדינטות lat/lon סדר הקואורדינטות הוא lon/lat.

8. תיאור קואורדינטות ברסטר (מסך כדור הארץ הווירטואלי מהווה דוגמא) כמעט תמיד עוקב אחר ציון XY, ​​אשר עבור rasters lat/lon כמעט תמיד פירושו lon/lat.

9. כאשר אנשים מתחילים להשתמש ב- SQL Server 2008 לאחסון נתונים מרחביים, רבים מהם ישתמשו בסוג הגיאומטריה למרות שהנתונים שלהם נמצאים ב- lat/lon, מסיבות שונות. עבור אנשים אלה סדר הקואורדינטות יהיה שוב lon/lat והסדר יישמר כאשר משתמשים בנתונים אלה במוצרי GIS.

וואו! . לזה אני מתכוון בשימוש & quotuniversal & quot, כפי שהפורמטים לעיל כוללים, מה? 99.99%? מהנתונים בחוץ. אם מישהו יכול להצביע אפילו על מאגר יחיד של נתוני & quotLatitude / Longitude & quot שמשתמשים (lat, lon) בהזמנה במקום (lon, lat), אני אודה לך מאוד לראות את כתובת האתר למאגר זה.

עם כל האמור לעיל, הבחירה של מיקרוסופט להזמין עבור GEOGRAPHY היא נקודתית אם אכן זה יכול לעזור לאנשים חדשים בנתונים גיאו -מרחביים שלוקחים את המשפט & quotLatitude / Longitude & quot מילולית, כפי שהם עושים בדרך כלל. עד כמה שאנשים גאים על השימוש ב- GIS עד היום, אפילו 100% מכל הנתונים האלה שם אינם 1% ממה שמגיע כשהטכנולוגיה הגיאו -מרחבית הולכת למיינסטרים, כך שמה שאנשים חדשים בתחום מצפים זה לא משהו שצריך להוזיל. .

אם זה הייתי אני כנראה הייתי מקובל ודבק במסדר (lon, lat). אבל אז אין ספק שהייתי לוקח על עצמי את המשימה להסביר שוב ושוב למאות מיליוני אנשים בעתיד (שאף אחד מהם לא יקרא שום תיעוד) שבאמצעות & quotLatitude / Longitude & quot באמת התכוונתי שהם יבינו שהם צריכים לשים את הקואורדינטות שלהם בסדר & quotLongitude / Latitude & quot. כל מי שמבקר את מיקרוסופט בבחירה זו צריך לחשוב פעמיים כיצד יתמודד עם זה.


בהנחה שהטור יצורף לאינדקס, הדברים הבאים צריכים להיות יעילים למדי.

עם שני מבקשים של 10 שורות ואז חזר מעין (עד) 20.

(כלומר אולי משהו כמו להלן)

או אפשרות אחרת (שמקטינה את מספר השורות שממוינות למקסימום 10)

הערה: תוכנית ההוצאה לפועל הייתה להגדרת הטבלה הפשוטה

מבחינה טכנית, אין צורך גם במיון בענף התחתון מכיוון שהוא גם הוזמן על ידי Diff, וניתן יהיה למזג את שתי התוצאות המסודרות. אבל לא הצלחתי להשיג את התוכנית הזאת.

The query has ORDER BY Diff ASC, YourCol ASC and not just ORDER BY YourCol ASC , because that was what ended up working to get rid of the Sort in the top branch of the plan. I needed to add the secondary column in (even though it won't ever change the result as YourCol will be the same for all values with the same Diff) so it would go through the merge join (concatenation) without adding a Sort.

SQL Server seems able to infer that an index on X seeked in ascending order will deliver rows ordered by X + Y and no sort is necessary. But it is not able to infer that travelling the index in descending order will deliver rows in the same order as Y-X (or even just unary minus X). Both branches of the plan use an index to avoid a sort but the TOP 10 in the bottom branch are then sorted by Diff (even though they are already in that order) to get them in the desired order for the merge.

For other queries/table definitions it may be trickier or not possible to get the merge plan with just a sort of one branch - as it relies on finding an ordering expression that SQL Server:


How to Best Implement nearest neighbour search in mysql?

I have 100k biz record each with lattitude and longitude. I see that MySQL actually support a data type called point. Should I use that instead?

Is it best to use point data type rather than regular float data type to store latitutude and longitude?

Eventually I want to find things like the first 100 restaurants closest to points 105,6 for example and my databases contains a lot of biz and points. Obviously computing the distance one by one for every records and for every points would be O(n) and hence sucks.

Notice that I am aware of a simpler solution described in How do Application Like Yelp Retrieve distance information from data base efficiently and will implement that my self too for a start. That's a good answer.

However, I think there is one creme of the crop answer that should outperform that right? In fact, storing location based on latitude and longitude and finding stuffs nearest to it is a very common problem I expect mysql to have a special design pattern for that. Does it have that?


צפו בסרטון: Установка Microsoft SQL Server 2008r2 (סֶפּטֶמבֶּר 2021).