نوشته های پریسا رشیدی نژاد

تفاوت geometry و geography در Postgres

پریسا رشیدی نژاد — Thu, 10 Sep 2020 17:09:35 +0430

دستگاه مختصات دکارتی و کرویتا حالا شده دیتایی رو بخواین در پایگاه داده ذخیره کنید که شامل مختصات جغرافیایی باشه؟از بین data type های معتبر که برای ذخیره کردن دیتای جغرافیایی در پایگاه داده ها ایجاد شدن، شاید دونستن فرق بین geography و geometry بتونه کار شما رو کمی راحت تر بکنه.تفاوت این دو به طور خلاصه، دستگاه مختصاتی هست که برای محاسبات استفاده میکنن. وقتی نوع داده رو geometry تعریف میکنیم محاسبات روی دستگاه مختصات کروی (Spherical coordinate system) و برحسب زاویه انجام میشه. اما وقتی از نوع داده ی geography استفاده میکنیم، محاسبات روی دستگاه دکارتی و بر حسب متره! مواقعی که میخوایم مقایسه ایی انجام بدیم مثل مقایسه مساحت چند ضلعی ها یا فاصله ی چندین نقطه از هم، اگه خروجی مون زاویه باشه کارمون به مراتب سخت تر از وقتی هست که خروجی مون متر باشه.برای روشن تر شدن موضوع، من میخوام با استفاده از تابع ST_Distance فاصله ی دو نقطه ی مترو شهید بهشتی و شرکت سان که با دایره قرمز مشخص شدن، رو محاسبه کنم.فاصله ی شرکت سان تا مترو شهید بهشتیدر حالت اول از نوع داده geometry استفاده میکنم، که گفتیم خروجی رو برحسب زاویه میده:SELECT ST_Distance( ST_GeometryFromText('POINT(51.427141 35.730271)', 4326), -- مترو شهید بهشتی ST_GeometryFromText('POINT(51.422646 35.732535)', 4326) --شرکت سان ); -- output : 0.00503296344115198 در حالت دوم از نوع داده geography استفاده میکنم:SELECT ST_Distance (ST_GeographyFromText('POINT(51.427141 35.730271)'), -- مترو شهید بهشتی ST_GeographyFromText('POINT(51.422646 35.732535)') --شرکت سان ); -- output: 477.98556905 پیداست که فاصله ی این دو نقطه ۴۷۷ متره! درحالی که در حالت اول از عدد 0.0050329 که خروجی کوئری مون بود این رو متوجه نمی شدیم.چه طور geometry رو به geography تبدیل کنیم؟برای اینکار اول باید مقدار EPSG رو با استفاده از تابع ST_Transform برابر ۴۳۲۶ کنیم. SELECT Geography(ST_Transform(geom,4326)) AS geog FROM table_name; چه طور geography رو به geometry تبدیل کنیم؟SELECT geography_field:: geometry AS geometry FROM table_name; برای فیلد geography چه طور ایندکس جغرافیایی بذاریم؟CREATE INDEX test_geography_geog_gix ON table_name USING GIST (geog);چه مواقعی مجبوریم از نوع داده geometry استفاده کنیم؟ اگه از نوع داده geometry استفاده کنیم،به دلیل کروی بودن دستگاه مختصات، ممکنه محاسبات و تحلیل ها پیچیده تر و کندتر انجام بشن. اما وقتی پراکندگی دیتا به وسعت کره ی زمین هست و شامل کشورها و قاره های مختلف میشه مجبوریم حتما از geometry استفاده کنیم.اما اگه دیتامون مختص فقط یک کشور هست استفاده از geography مشکلی رو ایجاد نمیکنه.بررسی یک مشکل:اگه فاصله ی دو شهر Tokyo و Los Angeles رو در دستگاه دکارتی یعنی بر اساس geography حساب کنیم(خط صورتی رنگ) ، به نتیجه ی اشتباهی منجر میشه.نتیجه ی درست و واقعی وقتی حاصل میشه در دستگاه مختصات کروی(خط قرمز رنگ) باشیم.یه مثال از حالتی که نمیتونیم از نوع داده geography استفاده کنیم.SELECT ST_Distance( ST_GeometryFromText('Point(-118.4079 33.9434)'), -- LAX ST_GeometryFromText('Point(139.733 35.567)')) -- NRT (Tokyo/Narita) AS geometry_distance, ST_Distance( ST_GeographyFromText('Point(-118.4079 33.9434)'), -- LAX ST_GeographyFromText('Point(139.733 35.567)')) -- NRT (Tokyo/Narita) AS geography_distance; --output: geometry_distance : 258.146005837336 (correct answer) geography_distance : 8833954.76996256 (wrong answer) تصور کنید یه هواپیما بخواد از شهر Tokyo به Los Angeles بره، قطعا مسیر صورتی که در دستگاه دکارتی هست رو انتخاب نمیکنه :) یکی از محدودیت هایی که استفاده از نوع داده ی geography داره، فانکشن های محدودی هست که این نوع داده رو پشتیبانی میکنن.ST_AsText(geography) returns textST_GeographyFromText(text) returns geographyST_AsBinary(geography) returns byteaST_GeogFromWKB(bytea) returns geographyST_AsSVG(geography) returns textST_AsGML(geography) returns textST_AsKML(geography) returns textST_AsGeoJson(geography) returns textST_Distance(geography, geography) returns doubleST_DWithin(geography, geography, float8) returns booleanST_Area(geography) returns doubleST_Length(geography) returns doubleST_Covers(geography, geography) returns booleanST_CoveredBy(geography, geography) returns booleanST_Intersects(geography, geography) returns booleanST_Buffer(geography, float8) returns geography 1ST_Intersection(geography, geography) returns geography 1اگه این مطلب براتون جالب بود و دوست دارین در موردش بیشتر بدونین، پیشنهاد می کنم این لینک رو حتما مطالعه کنید.

فوت و فن های استفاده از foreign table در postgres

پریسا رشیدی نژاد — Sun, 23 Aug 2020 14:13:01 +0430

گاهی وقتا احتیاج پیدا میکنیم دیتا رو از جدول هایی که داخل یه دیتابیس یا یه سرور دیگه ساختیم، به دیتابیس خودمون منتقل کنیم.یکی از بهترین راه حل ها برای ایجاد ارتباط بین دو تا دیتابیس یا دو تا سرور در Postgres، استفاده از دیتای خارجی(foreign data) هست.توی این پست، حالتی رو بررسی می کنیم که پایگاه داده مبدا postgres یا sql server باشه. مفهوم دیتای خارجی به صورت خلاصه، این میشه که برای دیتایی که توی دیتابیس مبدا هست یه مسیر ورودی به دیتابیس مقصد درست میکنیم تا بتونه انتقال پیدا کنه(مراحل اول تا سوم که در ادامه توضیح میدم).و بعد یه اسکیما شبیه اسکیمایی که توی مبدا داشته براش میسازیم(مرحله چهارم که در ادامه توضیح میدیم).یه کاربرد جالب دیگه ایی جدول خارجی، که علاوه بر انتقال دیتا داره، اجرا کردن دستورات sql از سرور مقصد روی سرور مبدا هست. در واقع هر query رو میشه روی سرور مقصد اجرا کرد.بریم مراحل کار رو با query ببینیم:قدم اول: ایجاد extension مربوط به دیتای خارجی:۱- در حالتی که دیتابیس مبدا postgres باشه.CREATE EXTENSION IF NOT EXISTS postgres_fdw;۲- در حالتی که دیتابیس مبدا sql server باشه.CREATE EXTENSION IF NOT EXISTS tds_fdw;قدم دوم: معرفی سرور مبدا ۱- در حالتی که دیتابیس مبدا postgres باشه: CREATE SERVER "SERVER_NAME" FOREIGN data wrapper postgres_fdw options ( dbname 'DATABASE_NAME', host 'HOST_NAME', port '5432' );۲- در حالتی که دیتابیس مبدا sql server باشه:CREATE SERVER "SERVER_NAME" FOREIGN DATA WRAPPER tds_fdwOPTIONS (servername 'HOST_NAME', port '1433',database 'DATABASE_NAME' , msg_handler 'notice');گزینه ی msg_handler که برابر با notice گذاشتیم در حالتی که دیتابیس مبدا SQL Server هست، بهمون کمک میکنه تا مراحل کار رو مشاهده کنیم و اگر مشکلی بود راحت تر متوجهش بشیم.پیشنهاد میکنم شما هم حتما ازش استفاده کنید.قدم سوم: ایجاد user mappingتوی این مرحله username و password کاربری که به دیتای مبدا دسترسی داره رو به دیتابیس مقصد معرفی میکنیم. نکته مهم: کاربری که توی این مرحله به عنوان user mapping تعریف میشه، حتما باید دسترسی grant روی جدول داشته باشه. CREATE USER MAPPING FOR USER_NAME SERVER "SERVER_NAME" options (USER 'USER_NAME', PASSWORD 'PASSWORD');قدم چهارم: یه اسکیما دقیقا مثل اسکیمای دیتا توی جدول مبدا درست میکنیم.میخوایم وقتی دیتا از راه رسید یه جایی برای موندن داشته باشه! فرض کنید اسکیمای دیتامون توی مبدا به این صورته که یه id داره و یه name. پس جدول خارجی ما هم به این صورت میشه:CREATE FOREIGN TABLE my_foreign_data ( id integer, name text) SERVER "SERVER_NAME" OPTIONS ( schema_name 'public', table_name 'source_table' );توی این مرحله اگر سرور مبدا SQL Server هست و در واقع داریم از tds_fdw استفاده میکنیم، میتونیم به جای اینکه اسم جدول رو به داخل table_name بدیم، از آپشن query استفاده کنیم و این طوری میتونیم روی دیتای اصلی عملیایی که میخوایم رو انجام بدیم و بعد منتقلش کنیم. مثلا من فقط میخوام رکوردهایی که id شون کمتر از ۱۰۰۰ هست رو توی جدول مقصد داشته باشم.CREATE FOREIGN TABLE my_foreign_data ( id integer, name text) SERVER "SERVER_NAME" OPTIONS ( schema_name 'public', query 'select * from source_table where id < 1000; ', row_estimate_method 'showplan_all' );میتونیم خروجی جدول خارجی رو با دستور زیر توی یه جدول در سرور مقصد نگهداریم.select * into tbl_my_foreign_data from my_foreign_data;توی آپشن query (که فقط روی tds_fdw فعال هست.) علاوه بر select کردن میتونید هر دستور DDL دیگه ایی رو هم اجرا کنید. مثلا یه جدول جدید بسازید یا یه جدول رو آپدیت و حذف کنید و چون توی این حالت query ما رکوردی رو سمت جدول خارجی بر نمیگردونه، لازم نیست ستون براش تعریف کنیم. مثال زیر جدول test رو روی سرور مبدا ایجاد میکنه:CREATE FOREIGN TABLE my_foreign_data () -- اینجا لازم نیست ستون ها رو تعریف کنیم SERVER "SERVER_NAME" OPTIONS OPTIONS (row_estimate_method 'showplan_all' ,query 'CREATE TABLE [test]( [Id] [bigint] NOT NULL);';بر اساس مشکلی که در این لینک گزارش شده، حتما باید از row_estimate_method 'showplan_all' استفاده کنید. وگرنه query تون دوبار روی سرور اجرا میشه و بار دوم حتما خطا میده چون جدول وجود داره! راستی برای اینکه دستوری که داخل query نوشتیم روی سرور اجرا بشه حتما باید جدول خارجی رو select کنیم.select * from my_foreign_data;

آموزش رج زدن نقشه به روش PostGIS!

پریسا رشیدی نژاد — Fri, 21 Aug 2020 23:03:50 +0430

اصطلاح رج زدن به معنی به پوییدن نقطه به نقطه یک سطحه، همون طور که در قالی بافی، دار قالی رو پر از گره های مرتب و به قاعده میکنیم و روی هم میچینیم تا یه اثر زیبا خلق بشه. حالا قالیبافی چه ربطی به PostGIS داره؟فرض کنید میخوایم روی سطح ایران ( یا هر منطقه ی خاص جغرافیایی دیگه ایی)، نقاطی با فاصله های منظم از هم دیگه ایجاد کنیم، یه جوریایی میشه برعکس انتخاب نقاط تصادفی و بدون قاعده. وقتی استفاده میشه که فاصله ی نقاط از هم دیگه و اینکه کل منطقه رو پوشش میدن برامون مهمه. توی GIS هم مثل قالیبافی، به اینکار میگن رج زنی.کجا استفاده میشه؟من خودم دو جا توی کارم ازش استفاده کردم، یکی اینکه میخواستم روی سرویس آدرس یابی شرکت map.ir تست کیفیت انجام بدم. این سرویس این طوری کار میکنه که شما یه مختصات جغرافیایی بهش میدین و جزییات اون مختصات مثل اینکه توی کدوم شهره، توی کدوم محله است، نزدیک ترین خیابان اصلی بهش چیه و ... رو توی خروجی بهتون میده. پس من احتیاج داشتم تعداد زیادی نقطه داشته باشم، که کل ایران رو پوشش بده و جایی نباشه که نقطه ایی انتخاب نکرده باشیم و تست نشده باقی بمونه.دومین کاربردی که برای من داشته، به دست آوردن آب و هوای نقطه به نقطه ی ایران بود. در واقع این نقاطی که بدست آوردم یه نماینده برای آب و هوای بقیه ی نقاط نزدیک خودشون به شعاع مثلا ۵۰ متری بودن. مراحل انجام اینکار رو توی گیت هاب با توضیحات گذاشتم، اما میخواستم یه کم بیشتر توی این پست توضیحش بدم.(البته کلا کار سختی نیست) https://github.com/ParisaRashidi/postgres_geo_functions قدم اول یه جدول برای ذخیره ی مختصات نقاطمون ایجاد میکنیم:create table tbl_grid ( lat double precision, long double precision );قدم دوم که اصلی ترین قسمته کاره، ایجاد متد زیر در پست گرسه: https://gist.github.com/ParisaRashidi/463f369d50c5764025eca375e3fce79d با اجرا کردن این متد جدولی که ابتدای کار برای ذخیره کردن مختصات نقاط ایجاد کردیم، پر میشه.برای ورودی متد به مختصات دو تا نقطه احتیاج داریم که بقیه ی نقاط رو داخل این چارچوب بچینیم.(این نقاط، توی تصویر بالا به اسم point1 , point2 مشخص شدن)وارد سایت map.ir بشین و مطابق دو تصویر زیر با راست کلیک کردن مختصات نقطه ی اول و همین طور دوم رو کپی کنید.گزینه ی اینجا کجاست مختصات نقطه به همراه آدرسش رو بهتون میده اولین عددی که نوشته شده طول و دومین عدد عرض جغرافیایی هست.بدین ترتیب چهار تا از ورودی های تابع ما برای چهارچوب (BBOX) کل ایران به این صورت میشه:latitudePoint1 = 25.482951 latitudePoint2 = 39.095963 longitudePoint1 = 45.131836 longitudePoint2 = 63.193359میمونه دو تا ورودی آخر تابع که طول و عرض فاصله ی نقاط از همدیگه هستن، مثلا من عدد ۱۰ رو قرار میدم و تابع رو اجرا میکنم.select point_distributor(25.284438,38.065392, 45.307617,63.105469,10, 10);که چون خروجی تابع void هست هیچی برنمیگردونه اما با اجرا کردن این دستور خروجی تابع که جدول tbl_grid هست رو میتونید ببینید:select st_setsrid(st_makepoint(long,lat),4326) from tbl_grid;البته برای اینکه بتونید از تابع st_makepoint استفاده کنید، قبلش باید اکستنشن postgis رو به پست گرس اضافه کرده باشین.CREATE EXTENSION IF NOT EXISTS postgis;

دسترسی به عارضه های ثبت شده در openstreetmap

پریسا رشیدی نژاد — Sun, 16 Aug 2020 19:08:35 +0430

اگه شما یه بیزینس مکان محور دارین، یا به هر نحوی با نقشه ی متن باز اوپن استریت مپ کار میکنین و دارین یه کار تحلیلی روی عارضه های جغرافیایی انجام میدین، احتمالا سوالاتی در مورد کیفیت و کمیت دیتای موجود در این بستر براتون پیش اومده!به طور مثال: * کاش میشد محله های استان فارس رو به صورت یک جا بتونم ببینم. اسامی شون رو هم احتیاج دارم. * چه طور میتونم ببینم رستوران ها در ایران کجا قرار دارن؟ شماره تلفن همشون رو میخوام.* توزیع پراکندگی روستاهای ایران.و اینکه میخوایم نتیجه ی این سوال ها همیشه بر اساس دیتای osm به روز باشن و دیتاهای جدید رو هم بتونیم ببینیم.توی این مقاله میخوام روشی رو معرفی بکنم که با حداقل دانش جغرافیایی و برنامه نویسی پاسخ این سوالات رو در قالب یه نقشه خوشگل و تر و تمیز و همینطور یه فایل json کاربردی داشته باشین تا با خلاقیت خودتون کارای آماری روی این دیتا انجام بدین و مهم تر از اون قبل از اینکه برید سراغ استفاده از osm یه دید کلی نسبت به اینکه آیا میتونه نیازتون رو برآورده بکنه یا نه داشته باشین.سایت overpass-trubo که درواقع یه API برای ارتباط با داده های osm هست از یه زبان کوئری نویسی استفاده میکنه که میخوایم سه چالشی که در بالا مطرح شد رو با کمک این سایت حل کنیم. قدم اول: متن سوال رو به زبان قابل فهم برای osm ترجمه میکنیم.منظور از متن سوال کلمات محله، روستا، رستوران، ایران و استان فارس هست. برای اینکار مثلا عبارت restaurant رو اینجا وارد میکنیم و بعد از enter کردن ما رو به صفحه ی مربوط به رستوران راهنمایی میکنه.وارد کردن کلمه ی restaurant برای پیدا کردن مقدار key-value آنعبارت amenity = restaurant در کوئری مورد استفاده قرار میگیرهمقادیر key-value برای رستوران، محله یا روستا به این شکل میشه:restaurant: amenity = restaurantvillage: place = village neighbourhood: place = neighbourhood حالا باید یه مقدار یکتا برای کشور ایران و استان فارس پیدا کنیم. برای اینکار وارد نقشه osm میشیم و روی ایران اونقدر زوم میکنیم که با راست کلیک کردن گزینه ی query feature برامون فعال بشه. وقتی فعال شد و روش کلیک کردیم از سمت چپ عبارت ISO3166 که به ازای هر کشور یکتاست رو به همراه مقدارش که IR هست کپی میکنیم. برای پیدا کردن مقدار یکتای استان فارس هم به همین ترتیب روی استان فارس زوم میکنیم و مراحل رو برای این استان هم انجام میدیم.با استفاده از این key-value در کوئری فیلتر کشور ایران را قرار میدهیمiran: "ISO3166-1" = "IR"Fars province: "ISO3166-2"="IR-14اگه این کار رو انجام ندیم در واقع کوئری مون رو محدود نکردیم و برای کل جهان اجرا میشه :| قدم دوم: در template زیر متغیر هایی که بدست آوردیم رو قرار میدیم و دستور به دست اومده رو در اینجا کپی میکنیم. بعد گزینه ی RUN رو از بالای صفحه انتخاب میکنیم:[out:json][timeout:250];area[ISO KEY-VALUE]->.boundaryarea;(nwr["ITEM KEY-VALUE"](area.boundaryarea););out body;>;out skel qt;مثلا برای مورد اول که رستوران های ایران رو میخواستیم به جای ISO KEY-VALUE و ITEM KEY-VALUE در کد بالا به ترتیب اطلاعات زیر رو که بدست آوردیم قرار میدیم."ISO3166-1" = "IR" "amenity" = "restaurant" وقتی اجرا شد با استفاده از آپشن Data خروجی کوئری مون رو به صورت json و با استفاده از آپشن map به صورت گرافیکی و روی نقشه میبینیم. همچنین میتونیم در فرمت های مختلف که ممکنه به این دیتا نیاز داشته باشیم دانلودش کنیم.پراکندگی رستوران ها در ایراناطلاعات محله ها در استان فارساطلاعات روستاهای ایران