پایگاه داده SpatiaLite در QGIS :شروع سریع

من استفاده از SpatiaLite را برای این راهنما انتخاب کردم، زیرا یک پایگاه داده مبتنی بر فایل است، نه یک پایگاه داده سرور مشتری کامل و بنابراین نصب بسیار آسان تر است. در واقع با QGIS، اصلاً نیازی به نصب نیست زیرا QGIS می‌تواند پایگاه‌های داده جدید SpatiaLite را به صورت بومی ایجاد کند. اگر واقعاً می خواهید از قدرت پایگاه داده های مکانی برای ذخیره و مدیریت داده های GIS خود استفاده کنید، احتمالاً می خواهید PostGIS را در مقطعی نصب کنید. یا به احتمال زیاد می خواهید از بخش فناوری اطلاعات خود بخواهید PostGIS را روی سرور شما نصب کند و اطلاعات اتصال را در اختیار شما قرار دهد. نصب پیچیده نیست، اما اکثر بخش های فناوری اطلاعات ترجیح می دهند کاربران نهایی را از سرور دور نگه دارند.

خوشبختانه، هم SpatiaLite و هم PostGIS ویژگی‌های ساده کنسرسیوم فضایی باز را برای مشخصات فضایی پیاده‌سازی می‌کنند و بنابراین توابع فضایی آنها تقریباً یکسان است و اکثر پرس‌و‌جوهای SQL روی هر دو با تغییرات بسیار کمی اجرا می‌شوند. QGIS همچنین با هر دو به روشی مشابه کار می کند، بنابراین می توانید امروز با SpatiaLite شروع کنید و بعداً بدون منحنی یادگیری به PostGIS بروید.

اگر قبلاً QGIS را نصب نکرده‌اید، می‌توانید به QGIS.org بروید و آخرین نسخه را دانلود کنید. در زمان نوشتن این مقاله جدیدترین نسخه 2.18 است و این نسخه ای است که در این مقاله از آن استفاده خواهم کرد، اما QGIS 3.0 در حدود 6 هفته دیگر منتشر می شود. خوشبختانه از نسخه پیش از انتشاری که دیدم، تغییرات اساسی در مدیریت DB ایجاد نخواهد شد و بنابراین من انتظار دارم که بتوانید هر یک از نسخه ها را دنبال کنید.

شما می توانید یک فایل فشرده حاوی اطلاعاتی که من برای این دوره استفاده خواهم کرد را از اینجا دانلود کنید . پس از دانلود فایل، آن را در هر جایی که برای شما مناسب است از حالت فشرده خارج کنید. من به آن دایرکتوری به عنوان دایرکتوری خانگی SpatiaLite اشاره می کنم.

ایجاد پایگاه داده SpatiaLite

1. به دایرکتوری spatialite_data در فهرست اصلی SpatiaLite خود بروید و روی فایل overview.qgs دوبار کلیک کنید تا پروژه Overview QGIS باز شود. شما باید چیزی شبیه به این را ببینید: 
2. روی اولین لایه در پنل لایه ها،  Linear Projects کلیک راست کرده و سپس Save As را انتخاب کنید…  باید کادر محاوره ای Save vector as… را ببینید. مطمئن شوید که قالب را روی SpatiaLite تنظیم کرده اید ، سپس با استفاده از دکمه Browse بعد از کادر متنی File name به دایرکتوری spatialite_data در فهرست اصلی SpatiaLite بروید و فایلی به نام quikstart.sqlite ایجاد کنید .
3. linear_projects را در کادر متن Layer name وارد کنید. قسمت بالای کادر محاوره ای باید چیزی شبیه به این باشد:
4. ما CRS را در WGS84 رها می‌کنیم و فرض می‌کنیم که همه چیز با قسمت پایین لایه برداری ذخیره به‌عنوان درست است  و روی دکمه OK در پایین کادر محاوره‌ای کلیک می‌کنیم. باید ببینید که یک لایه جدید به نام quikstart linear_projects MultiLineString در پانل لایه ظاهر می شود. نام لایه پیش فرض ترکیبی از نام پایگاه داده، نام لایه ای که ارائه کردیم و نوع هندسه است.
5. مراحل 2-4 را برای 4 لایه باقیمانده در لایه لایه تکرار کنید، با این استثنا که در مرحله 2 فقط باید مرورگر را به فایل quikstart.sqlite هدایت کنید. اگر فایلی با نامی که ارائه می کنید وجود نداشته باشد، QGIS ایجاد می کند. یکی به صورت خودکار در غیر این صورت QGIS به سادگی یک لایه جدید در فایل موجود ایجاد می کند. از نام های لایه buowl_habitat ، baea_nests ، raptor_nests و gbh_rookeries استفاده کنید.

توجه: هر دو SpatiaLite و QGIS از ویژگی های ساده OGC برای استاندارد SQL پیروی می کنند. انواع هندسه عبارتند از Point و MultiPoint، LineString و MultiLineString، و Polygon و MultiPolygon. پیشوند چند نشان می دهد که هندسه برای یک ویژگی واحد آرایه ای از آن نوع هندسه است. بنابراین چندین هندسه خط با یک مجموعه از مقادیر ویژگی و غیره وجود دارد. به طور کلی، یک شکل فایل نقطه ای به نوع هندسه نقطه ای تبدیل می شود، در حالی که شکل فایل های خط و چند ضلعی به انواع هندسه MultiLineString و MultiPolygon تبدیل می شوند.

این تمام چیزی است که وجود دارد! شما به تازگی یک پایگاه داده SpatiaLite با 5 لایه در آن ایجاد کرده اید. ما از این پایگاه داده برای بقیه دوره استفاده خواهیم کرد. انگیزه آن کاری بود که چندین سال پیش به عنوان متخصص GIS برای یک شرکت مشاوره محیطی در کلرادو انجام دادم. خود داده ها جعلی هستند، اما انگیزه این داده ها واقعی است. این یک پروژه دنیای واقعی با داده های دنیای واقعی را نشان می دهد که می توانیم برای پاسخ به سوالات دنیای واقعی از آن استفاده کنیم. و بالاخره GIS چیست جز ابزاری برای پاسخ به سؤالات درباره جهان؟

ما این داده‌ها را عمیقاً بررسی خواهیم کرد، اما فرض اصلی این است که مشتری در صنعت نفت و گاز داریم که در حال ساخت و نگهداری خطوط لوله (و پشتیبانی از جاده‌ها، خطوط برق و غیره) در یک میدان گاز طبیعی است. آنها باید مطمئن شوند که هیچ گونه مقررات زیست محیطی را نقض نمی کنند، بنابراین شرکت شما را برای نظارت بر گونه های حیات وحش استخدام می کنند که ممکن است مسائل نظارتی را برای آنها ایجاد کند تا به آنها در برنامه ریزی و اجرای مشاغل خود با حداقل تأثیر بر محیط زیست کمک کند.

بارگیری داده ها از SpatiaLite

قبل از اینکه به شما نشان دهیم چگونه داده ها را از SpatiaLite در QGIS بارگیری کنید، باید لایه هایی را که قبلاً آنجا هستند حذف کنیم. می توانید این کار را با کلیک راست روی هر لایه در پنل لایه ها به صورت جداگانه و انتخاب Remove از منوی زمینه انجام دهید. همچنین می‌توانید همه لایه‌های آن‌ها را به یکباره انتخاب کرده و در یک عملیات حذف کنید.

چهار راه برای بارگیری داده ها از پایگاه داده SpatiaLite در QGIS وجود دارد.

1. در پنل مرورگر به مکانی که پایگاه داده quikstart.sqlite را ذخیره کرده اید بروید و روی آن دوبار کلیک کنید. باید کادر محاوره ای Select vector layers to add… را ببینید. می توانید لایه های جداگانه را انتخاب کنید یا همه لایه ها را انتخاب کنید. دکمه OK را فشار دهید تا لایه های انتخاب شده در نقشه بارگذاری شوند.
2. در پنل مرورگر آیکون SpatiaLite را پیدا کنید و روی آن کلیک راست کنید، سپس New Connection… را انتخاب کنید و به فایل quikstart.sqlite که در قسمت اول ایجاد کردیم بروید و آن را باز کنید. باید یک > کوچک را در سمت چپ ورودی SpatiaLite در پنل مرورگر مشاهده کنید. که نشان می دهد می توان آن ورودی را گسترش داد. بر روی > کلیک کنید تا آن را گسترش دهید و سپس ورودی quikstart.sqlite را نیز گسترش دهید تا لایه های جداگانه در پایگاه داده را ببینید. می‌توانید روی آن لایه‌ها یک به یک دوبار کلیک کنید تا آنها را به نقشه اضافه کنید، یا می‌توانید لایه‌هایی را که می‌خواهید برجسته کنید و آنها را به پنل لایه بکشید .
3. با کلیک بر روی نماد SpatiaLite  در نوار ابزار Manage Layers (معمولاً در لبه سمت چپ برنامه یافت می شود، یا با رفتن به گزینه Add SpatiaLite Layer در منوی Layer (لایه -> افزودن لایه ) کادر گفتگوی Add SpatiaLite Layer(s) را باز کنید. -> Add SpatiaLite Layer….) روی دکمه New کلیک کنید و به فایل quikstart.sqlite که در قسمت قبل ایجاد کردیم بروید و آن را باز کنید. برای ایجاد اتصال به پایگاه داده روی Connect کلیک کنید و سپس لایه هایی را که می خواهید انتخاب کنید. اضافه کنید و روی دکمه افزودن کلیک کنید همچنین می‌توانید فیلتری را از این کادر محاوره‌ای تنظیم کنید تا ویژگی‌های گنجاندن در لایه را محدود کنید.
4. در منوی Database مورد DB Manager را انتخاب کنید. روی SpatiaLite در درخت اتصال راست کلیک کرده و New Connection را انتخاب کنید . به فایل quikstart.sqlite بروید و آن را باز کنید. می‌توانید روی لایه‌هایی که می‌خواهید به پروژه اضافه کنید دوبار کلیک کنید یا روی آن‌ها کلیک راست کرده و Add to canvas را انتخاب کنید. توجه داشته باشید که پانل سمت راست پانل Tree شامل سه تب است. اولی اطلاعاتی را در مورد لایه از جمله انواع هندسه، نام‌های فیلد، و هر گونه محرک پایگاه داده برای لایه نشان می‌دهد. تریگرها توابع SQL هستند که در پاسخ به رویدادهای پایگاه داده مانند افزودن یا به روز رسانی داده ها اجرا می شوند. تب Table جدول ویژگی و پیش نمایش را نشان می دهدبرگه هندسه ویژگی ها را نشان می دهد.

مقدمه ای بر SQL

پانل مدیریت DB همچنین دارای یک پنجره SQL  ( ) است که به شما امکان می دهد نتایج یک پرس و جوی SQL را در پایگاه داده SpatiaLite بنویسید، اجرا کنید و مشاهده کنید. این توانایی است که به SpatiaLite انعطاف پذیری و قدرت آن را می دهد و آن را از یک فرمت ذخیره سازی داده صرف جدا می کند. ما بخش باقی مانده از این راهنمای شروع را برای نوشتن پرس و جوهای SQL در برابر این داده ها صرف خواهیم کرد.

ساده ترین دستور SQL از الگوی SELECT * FROM جدول تبعیت می کند. کاراکتر * یک مکان نگهدار برای تمام فیلدهای جدول است.

SELECT * FROM baea_nests را در کادر ورودی query تایپ کنید و روی دکمه Execute (F5) کلیک کنید و باید یک جدول نتایج را در پنجره پایین با تمام فیلدهای نمایش داده شده مشاهده کنید.

هر چیز دیگری که با پرس و جوهای SELECT انجام می دهیم از این ساخته می شود.

حالا SELECT nest_id, lastsurvey, laststat, recentspec FROM raptor_nests  را در کادر query تایپ کنید و آن را اجرا کنید. توجه داشته باشید که این کار فیلدهای جدول نتیجه را فقط به آنهایی که در لیست فیلدها نشان داده شده اند محدود می کند.

همچنین می‌توانیم جدول نتایج را با استفاده از یک عبارت WHERE و به دنبال آن یک عبارت شرطی محدود کنیم که مشخص می‌کند کدام ویژگی‌ها برگردانده می‌شوند. اجازه دهید یک عبارت WHERE اضافه کنیم تا با افزودن موارد زیر به عبارت SELECT قبلی، لیست را به لانه‌های فعال محدود کنیم: WHERE lateststat=’ACTIVE NEST’   . هنگام اجرا، جدول نتایج باید به شکل زیر باشد:

می‌توانیم جدول نتیجه را با پیوستن به عبارات شرطی در عبارت WHERE با عملگرهای منطقی AND، OR و XOR محدود کنیم. به عنوان مثال، اجازه دهید متن AND Retenspec=’Red-tail Hawk’ را به عبارت فعلی خود اضافه کنیم تا پرس و جوی کامل SQL به صورت زیر باشد:

SELECT nest_id, lastsurvey, lateststat, specific FROM raptor_nests WHERE laststat=’ACTIVE NEST’ AND latestspec=’Red-tail Hawk’

همچنین می‌توانیم پایگاه داده را هدایت کنیم تا جدول نتایج را با عبارت ORDER BY مرتب کند. به عنوان مثال برای مرتب کردن جدول بر اساس تاریخ آخرین بررسی، ORDER BY lastsurvey را به بیانیه فعلی اضافه کنید. اگر می‌خواهید آن‌ها را بر اساس آخرین تاریخ نظرسنجی (به ترتیب نزولی) مرتب کنید، کلمه کلیدی DESC را به انتهای ترتیب براساس بیانیه اضافه کنید. همچنین می توانید با اضافه کردن آنها به دستور ORDER BY که با کاما از هم جدا شده اند، چندین فیلد را برای مرتب سازی اضافه کنید. برای مثال:

nest_id, lastsurvey, laststat, lastspec FROM raptor_nests WHERE laststat=’ACTIVE NEST’ AND recentspec=’Red-tail Hawk’ ORDER BY lastsurvey DESC, nest_id

می‌توانیم با استفاده از عبارت GROUP BY اطلاعاتی را که توسط یک فیلد خلاصه شده است، دریافت کنیم. به عنوان مثال عبارت SQL

آخرین آمار را انتخاب کنید، شمارش کنید(آمار اخیر) از گروه raptor_nests بر اساس آخرین آمار 

یک جدول نتیجه را نشان می دهد که تعداد لانه ها را با هر مقدار اخیر نشان می دهد. و عبارت SQL

مشخصات اخیر، آخرین آمار، شمارش (آخرین آمار) از گروه raptor_nests بر اساس مشخصات اخیر، آخرین آمار، انتخاب کنید 

همان اطلاعات را برمی گرداند اما بر اساس گونه ها نیز تقسیم می شود. اگر ستونی که خلاصه می‌کنید عددی است، می‌توانید از توابع مجموع دیگری مانند sum()، min()، max() و غیره استفاده کنید.

فقط برای سرگرمی، ببینید آیا می توانید جدول نتایج زیر را دوباره ایجاد کنید.

همچنین توجه داشته باشید که رابط QGIS DBManager به شما این امکان را می دهد که یک پرس و جو را با نامی ذخیره کنید که می توانید در تاریخ بعدی آن را بازیابی کنید. همانطور که ممکن است حدس بزنید، پرس‌وجوهای SQL می‌توانند بسیار طولانی و پیچیده باشند و این می‌تواند زمان زیادی را برای تایپ کردن با پرس‌و‌جوهای پرکاربرد صرفه‌جویی کند.

اضافه کردن Spatial به SQL

امیدواریم که شما در حال مشاهده قدرت SQL برای استخراج اطلاعات مورد نیاز از داده های جدولی خود هستید، و این قطعاً فقط نوک کوه یخ است. کل دوره‌های کالج در SQL وجود دارد و می‌تواند کاملاً درگیر شود، به‌خصوص زمانی که چندین عضویت و بند گروه‌بندی گنجانده شده باشد، اما اگر با SQL خوب باشید، تقریباً همیشه می‌توانید دقیقاً اطلاعاتی را که نیاز دارید به دست آورید.

گسترش یک پایگاه داده معمولی برای کاربردهای جغرافیایی شامل 2 مرحله است. اول از همه، شما به راهی برای ذخیره بخش هندسی یک ویژگی جغرافیایی نیاز دارید، و دوم، به برخی از توابع نیاز دارید که بتواند هندسه ها را پردازش کند. اکثر پایگاه های داده فضایی هندسه را به صورت دودویی در یک ستون هندسه در پایگاه داده ذخیره می کنند و SpatiaLite نیز از این قاعده مستثنی نیست. کنسرسیوم‌های فضایی باز ویژگی‌های ساده برای مشخصات SQL مجموعه‌ای از توابع استاندارد را برای برخورد با داده‌های مکانی و SpatiaLite فراهم می‌کند، همچنین PostGIS بسیار نزدیک به این استاندارد است.

به عنوان مثال، اجازه می‌دهیم از تابع AsGeoJSON() برای برگرداندن هندسه در قالب GeoJSON استفاده کنیم که یک قالب مبتنی بر متن است که قابل خواندن توسط انسان است، بنابراین می‌توانید مختصات واقعی که چند ضلعی را تشکیل می‌دهند را ببینید.

SELECT گونه، فعالیت، AsGeoJSON(GEOMETRY) FROM gbh_rookeries

تابع AsGeoJSON می تواند پارامتر اختیاری دومی را انتخاب کند که دقت مقادیر مختصات را کنترل می کند. برای مثال می‌توانید از AsGeoJSON(GEOMETRY، 5) برای محدود کردن مختصات به 5 رقم اعشار استفاده کنید.

اگر می‌خواهید مختصات را در سیستم مرجع فضایی دیگری ببینید، می‌توانید از تابع ST_Transform() برای تبدیل آن به یک سیستم مرجع فضایی دیگر استفاده کنید. به عنوان مثال در دستور SQL

SELECT گونه، فعالیت، AsGeoJSON( ST_Transform(GEOMETRY, 26913) , 1) FROM gbh_rookeries

هندسه قبل از تبدیل شدن به GeoJSON به UTM Zone 13N NAD 83 تبدیل شده است که دارای کد EPSG 26913 است تا بتوانیم مختصات را در CRS ببینیم. می توانید لیست کامل کدهای EPSG را در   spatialreference.org/ref/epsg/ مشاهده کنید . تابع ST_Transform (به صورت پررنگ) یک شی هندسی را برمی گرداند که سپس می تواند به عنوان پارامتر به هر تابع دیگری که به شی هندسی نیاز دارد ارسال شود. این توانایی تو در تو توابع فوق العاده قدرتمند است. به عنوان مثال، بسیاری از توابع که مقادیر فاصله را به عنوان ورودی می گیرند، انتظار دارند که این مقادیر در واحدهای فاصله سیستم مرجع فضایی هندسه باشد. اما اگر بتوانید هندسه را به یک سیستم مرجع فضایی متفاوت تبدیل کنید، می توانید از واحد فاصله متفاوت استفاده کنید.

به عنوان مثال، تابع ST_Buffer یک هندسه و یک فاصله را می گیرد و یک هندسه بافر از فاصله مشخص شده را برمی گرداند. اگر بخواهیم بافرهای 1/2 مایلی برای داده‌های آشیانه عقاب ایجاد کنیم، با مشکل مواجه می‌شویم زیرا داده‌های آشیانه عقاب به عنوان نقاطی در مختصات جغرافیایی (طول/طول جغرافیایی) ذخیره می‌شوند و در حالی که می‌توان به راحتی درجه‌های عرض جغرافیایی را به مایل تبدیل کرد. طول یک درجه از طول جغرافیایی تنها برابر با یک درجه از عرض جغرافیایی در خط استوا است. هر چه به سمت شمال جلوتر می روید، یک درجه از طول جغرافیایی کمتر می شود تا زمانی که در قطب شمال به صفر نزدیک شود.

خوشبختانه ما می‌توانیم از دستور SQL زیر
SELECT nest_id, status, ST_Buffer(ST_Transform(GEOMETRY, 26913), 804 FROM baea_nest
برای ایجاد بافرهایی در UTM Zone 13N NAD 83 استفاده کنیم که 804 متر هستند (1/2 مایل قطر را تغییر می‌دهند) ابتدا قبل از ایجاد بافر این پرس و جو منجر به جدولی به شکل زیر می شود.

ممکن است فکر کنید “معمولا بزرگ”. هندسه باینری است و حتی در جدول ظاهر نمی شود. این چه سودی برای من دارد؟». خوب، من می خواهم به شما بگویم. QGIS می تواند نتایج حاصل از هر پرس و جوی را که حاوی یک فیلد هندسی به عنوان یک لایه در نقشه باشد نمایش دهد. کافی است کادر Load as new layer را علامت بزنید و با انتخاب فیلد در کادر ستون Geometry به QGIS بگویید که کدام فیلد هندسه را شامل می شود ، نامی برای لایه بگذارید و روی Load Now کلیک کنید! دکمه.

هنگامی که لایه در QGIS بارگذاری می شود، می توان از آن مانند هر لایه دیگر از جمله به عنوان ورودی توابع ژئوپردازش استفاده کرد. حتی می‌توان آن را به‌عنوان یک فایل واقعی در هر فرمت فایل یا مرجع فضایی که می‌خواهید با کلیک راست روی لایه در پنل لایه و انتخاب Save as… از منوی زمینه ذخیره کرد. این “لایه مجازی” چندین مزیت مهم دارد. ابتدا فضای اضافی روی دیسک اشغال نمی‌کند، بلکه صرفاً بر اساس یک جستجوی SQL از داده‌های نقطه‌ای است. دوم، به دلیل اینکه بر اساس یک پرس و جوی SQL است، لایه بافر زمانی که لایه نقطه زیرین تغییر می کند، به طور خودکار به روز می شود. اگر ویژگی‌ها اضافه یا حذف شوند یا داده‌ها تغییر کنند، این کار فقط باید در لایه نقطه انجام شود و این تغییرات در لایه بافر منعکس می‌شود که با کلیک بر روی دکمه رفرش اشاره دارد.

کمی زمان بگذارید و لایه‌های مجازی را برای بافرهای لانه شکارچی (1/4 مایل)، بافرهای زیستگاه جغد نقب (300 متر) و بافرهای پروژه خطی (بسته به عرض ردیف اضافه کنید. نکته: می‌توانید فاصله بافر عددی را با فیلد جایگزین کنید. نام فیلدی که دارای عرض ROW است).

SpatiaLite همچنین شامل توابع مفیدی برای اندازه گیری طول و مساحت است و این توابع را می توان در پرس و جوی SQL برای ایجاد یک فیلد مجازی استفاده کرد. به عنوان مثال پرس و جو زیر شامل طول هر پروژه خطی (بر حسب کیلومتر) بدون اشغال فضای روی دیسک است و پرس و جو درست خواهد بود حتی اگر هندسه زیربنایی ویرایش شود.

SELECT پروژه، نوع، row_width، ST_Length(ST_Transform(GEOMETRY, 26913)) / 1000 FROM linear_projects 

همچنین می‌توانید از این فیلدهای مجازی در توابع خلاصه در عبارات با عبارت GROUP BY استفاده کنید. به عنوان مثال پرس و جو زیر کل طول پروژه های خطی را بر حسب کیلومتر برای هر نوع خلاصه می کند.

نوع SELECT، مجموع (ST_Length(ST_Transform(GEOMETRY, 26913)) / 1000) FROM linear_projects GROUP BY

می‌توانید کل گروه توابع فضایی را که می‌توانید در SpatiaLite استفاده کنید، اینجا ببینید . پیشنهاد می‌کنم به آن‌ها نگاهی بیندازید و سپس ببینید که آیا می‌توانید برای جمع‌بندی تعداد کل هکتار از زیستگاه جغدهای حفاری که توسط فیلد اخیراِستات خلاصه شده است، پرسشی ارائه کنید.

پیوندهای فضایی

کارهای زیادی می توانید با SQL در جستارهای جدول تک انجام دهید، ما کلاس های ویژگی مجازی، محاسبه اندازه گیری ها و خلاصه کردن مقادیر را دیده ایم. اما قدرت واقعی SQL در توانایی آن در انجام پرس و جو با دو یا چند جدول است. پیوستن چند جدول مستلزم آن است که هر فیلد با نام جدول (یا یک نام مستعار برای سادگی) پیشوند شود. همچنین یک بند join وجود دارد که شرایطی را مشخص می کند که ویژگی های دو جدول به هم متصل می شوند.

در جداول غیر فضایی، بند join معمولاً بر اساس فیلدهایی با مقادیر مشترک است. به عنوان مثال فاکتورها را می توان به یک مشتری متصل کرد زیرا سوابق صورتحساب هر کدام حاوی شناسه مشتریان است. با جداول فضایی، اتصالات اغلب بر اساس روابط فضایی مانند “لانه در 1/2 مایلی پروژه ساخت و ساز است” یا “خط لوله تالاب را قطع می کند” است.

از آنجایی که ما دو هندسه داریم، می‌توانیم کارهایی مانند محاسبه فاصله بین ویژگی‌ها، میزان همپوشانی بین ویژگی‌ها، و غیره را انجام دهیم که انعطاف‌پذیری زیادی را در انجام تحلیل فضایی فقط با یک پرس‌وجو SQL فراهم می‌کند.

ساختار اصلی یک اتصال فضایی از پرس و جو زیر پیروی می کند

r.nest_id، r.recentspec، r.recentstat، r.lastsurvey، p.project، p.type، ST_Distance(r.GEOMETRY، p.GEOMETRY) را به عنوان dist FROM raptor_nests انتخاب کنید r. p.GEOMETRY، 0.005) 

بعد از عبارت FROM کلمه کلیدی JOIN و به دنبال جدولی که به آن ملحق می شود قرار می گیرد. پس از آن کلمه کلیدی ON و عبارت join قرار می گیرد. عبارت join می تواند هر عبارتی باشد که به درست یا نادرست ارزیابی می شود و تنها ترکیبی از ویژگی هایی که به درستی ارزیابی می شوند در مجموعه نتایج گنجانده می شوند. در این مورد ما از تابع PtDistWithin استفاده می کنیم (r.geometry, p.geometry, 0.005) اگر فاصله بین هندسه لانه رپتور و هندسه پروژه های خطی کمتر از 0.005 درجه باشد، این تابع true برمی گرداند.

توجه داشته باشید که جدول raptor_nests در عبارت FROM و جدول linear_projects در عبارت JOIN به ترتیب r و p هستند. اینها نام مستعار هستند و برای پیشوند نام فیلدها استفاده می شوند تا به صراحت مشخص شود که فیلد از کدام جدول است. شما می توانید از کل نام جدول به عنوان پیشوند استفاده کنید اما این به سرعت عبارات SQL بسیار طولانی ایجاد می کند.

انواع مختلفی از JOIN وجود دارد. پیش‌فرض یک JOIN داخلی است که در آن مجموعه نتایج فقط شامل مواردی می‌شود که شرط پیوستن درست است. LEFT JOIN حداقل یک نتیجه را برای هر رکورد در سمت چپ Join، در این مورد جدول raptor_nest ، برمی گرداند ، اما اگر در جدول linear_projects مطابقت نداشته باشد، آن فیلدها در نتیجه دارای مقادیر NULL خواهند بود. RIGHT JOIN مشابه است اما شامل یک رکورد برای هر ویژگی در linear_projects می شود و اگر ویژگی raptor_nest متناظری وجود نداشته باشد، این فیلدها در نتیجه NULL خواهند بود. FULL JOIN حداقل یک نتیجه از هر ویژگی در هر دو جدول را شامل می شود، اما اگر مطابقت نداشته باشد، فیلدهای مربوطه NULL خواهند بود.

نمونه های زندگی واقعی

همانطور که ممکن است حدس بزنید، تعداد تقریباً نامتناهی پرس و جو وجود دارد که می توانند برای پرسیدن سؤالات خاص در مورد این داده ها ایجاد شوند. نمی توان از همه احتمالات عبور کرد، بنابراین کاری که من می خواهم انجام دهم، برای تحریک تفکر شما در مورد انواع سوالاتی که می توانید با پرس و جوهای SQL فضایی به آنها پاسخ دهید، شامل چند نوع واقعی سوال است که مشتری یا مدیر پروژه شما ممکن است بپرسد. بخش GIS برای پاسخ دادن و نشان دادن پرس و جوی SQL که می تواند برای ارائه آن اطلاعات استفاده شود. قبل از اینکه به راه حل من نگاه کنید، سعی کنید به این فکر کنید که چگونه می توانید یک پرس و جوی SQL را برای پاسخ به سؤال فرموله کنید. در اغلب فرآیندهای GIS، اغلب راه‌های متعددی برای رسیدن به یک پاسخ وجود دارد و SQL تفاوتی ندارد. ممکن است چندین راه برای نوشتن یک پرس و جو برای دریافت پاسخ یکسان وجود داشته باشد،

1. مدیر پروژه شما لیستی از لانه‌های Swainsons Hawk فعال می‌خواهد که بر پروژه‌های مشتریان تأثیر می‌گذارند تا بتوانند کارکنان میدانی را برای تأیید وضعیت فعلی خود به بیرون بفرستند. او می‌خواهد آنها را بر اساس تاریخ آخرین نظرسنجی مرتب کنند تا بتوانند لانه‌هایی را که بدون نظرسنجی طولانی‌ترین زمان را طی کرده‌اند، اولویت‌بندی کنند. r.nest_id، r.recentspec، r.recentstat، r.lastsurvey، p.project، p.type، ST_Distance(r.GEOMETRY، p.GEOMETRY) FROM raptor_nests r به پروژه‌های_خطی بپیوندید p در PtDistWithin(r.GEOMETRY، p. هندسه، 0.005) WHERE r.recentstat = ‘ACTIVE NEST’ AND r.recentspec = ‘Swainsons Hawk’ ORDER BY lastsurvey, nest_id, project 
2. در ابتدای فصل تودرتو، مشتری شما فهرستی از تمام پروژه‌ها را می‌خواهد که تعداد لانه‌هایی که ممکن است بر روی آن تأثیر بگذارند، حساب کند. علاوه بر این، او می‌خواهد با این فرض که لانه‌هایی که در سال گذشته فعال بودند، امسال بیشتر فعال باشند، شمارش با وضعیت سال‌های قبل تجزیه شود. SELECT p.project, p.type, r.recentstat, count(r.recentstat) FROM raptor_nests r JOIN linear_projects p ON PtDistWithin(r.GEOMETRY, p.GEOMETRY, 0.005) GROUP BY p.project, r.recentstat
3. کارکنان میدانی گزارش می‌دهند که یک لانه عقاب فعال جدید در -104.83504، 40.19849 واقع شده است و مدیر پروژه فهرستی از تمام پروژه‌هایی را می‌خواهد که تحت تأثیر قرار می‌گیرند، ابتدا توسط نزدیک‌ترین افراد به لانه سفارش داده شود. پروژه، نوع، ST_Distance(GEOMETRY، ST_PointFromText(‘POINT(-104.83584 40.19849)’)) را به عنوان dist FROM linear_projects انتخاب کنید WHERE dist<0.01 ORDER BY dist
   
   توجه داشته باشید که وقتی مقدار بازگردانده شده توسط تابع فاصله را می دهیم. می تواند از آن مقدار در بندهای WHERE و ORDER BY استفاده کند.
4. مشتری شما می خواهد بداند که چند درصد از هر پروژه تحت تأثیر زیستگاه نقب جغد قرار گرفته است و می خواهد ابتدا بر اساس نوع پروژه مرتب شود. این یکی کمی مشکل است، بنابراین اگر متوجه شوید یک ستاره طلایی دریافت خواهید کرد. SELECT p.type, p.project, Round(sum(ST_Length(ST_Intersection(b.GEOMETRY, p.GEOMETRY)))/ST_Length(p.GEOMETRY)*100, 1) as pcnt FROM buowl_habitat b JOIN linear_projects pON b.GEOMETRY، p.GEOMETRY) گروه بر اساس پروژه ترتیب بر اساس نوع، پروژه
   

اگر پیشنهاداتی برای سؤالات دیگر در مورد این مجموعه داده دارید، لطفاً آنها را به نظرات این وبلاگ اضافه کنید و من سعی خواهم کرد راه حلی ارائه دهم.

بعد کجا بریم

امیدواریم تا کنون، برخی از مزایای استفاده از پرس و جوهای SQL با داده های GIS خود را مشاهده کرده باشید. چند دقیقه وقت بگذارید و به مثال‌های بالا برگردید و به این فکر کنید که چند مرحله برای دریافت این پاسخ‌ها در یک سیستم GIS رومیزی مبتنی بر منوی سنتی لازم است و چه تعداد فایل میانی ایجاد می‌شوند و باید مدیریت شوند. همچنین در نظر بگیرید که پشتیبانی از کاربران راه دور چقدر ساده تر خواهد بود اگر بتوانید یک درخواست SQL را برای آنها ایمیل کنید که می توانند آن را کپی کرده و در ویرایشگر SQL در پایگاه داده خود جایگذاری کنند، به جای اینکه سعی کنید گام به گام در کل فرآیند با آنها صحبت کنید. از طریق تلفن

اگر می خواهید در مورد SQL به طور کلی اطلاعات بیشتری کسب کنید، منابع زیادی در دسترس است. بسیاری از آنها به صورت رایگان در اینترنت در دسترس هستند. همچنین کتاب‌ها و دوره‌هایی در Udemy.com و دیگر پلتفرم‌ها وجود دارد که می‌توانید خریداری کنید. اکثر آنها هیچ یک از جنبه های فضایی SQL را ذکر نمی کنند، اما زمینه خوبی را برای استفاده از SQL برای جستجوهای جدولی فراهم می کنند و قبل از پرواز باید راه رفتن را یاد بگیرید.