Көбірек

PostGIS сұранысы: қиылысатын барлық жолдар бір-бірінен 1 сағаттан кейін

PostGIS сұранысы: қиылысатын барлық жолдар бір-бірінен 1 сағаттан кейін


Мен postgres (postgis) қолданамын және геометрия қиылысатын барлық жолдарды қайтаратын және бір-бірінен 1 сағат ішінде уақыт белгісі бар сұранысты орындағым келеді.

SQL операторы қалай көрінер еді?

кесте келесідей:

id {бүтін} атау {символдар өзгеретін} шекаралар {геометрия} уақыт белгілері {уақыт белдеуі бар уақыт белгісі} // төмендегі кесте мысалында мақсатты түрде қалдырылған шектер (қажет емес) идентификатор атауының уақыт белгілері --- + -------- - + -------------------------------- 1 бір «2010-09-24 21: 10: 39.515 + 00 «2 two» 2010-09-16 09: 21: 09.362 + 00 «3 three» 2010-07-08 00: 00: 46.549 + 00 «…

№1 редакциялау

Мұнда қиылысу сәйкестігін қоспағанда мысал келтіруге болады: әр жол үшін маған 1 сағат ішінде кез-келген басқа жолдар беріңіз:

t id | бойынша myTable тәртібінен * таңдаңыз t ---- + ------------------------------- 9 | 2011-07-15 18: 20: 20.05 + 02 10 | 2011-07-15 19: 05: 00.05 + 02 11 | 2011-07-15 19: 40: 20.05 + 02 13 | 2011-07-15 20: 31: 01.05 + 02 14 | 2011-07-15 20: 35: 11.05 + 02 (5 жол) қажетті сұраудың нәтижесі: id | сәйкестік | origTime | matchTime ---- + ---------- + ---------------------------- + ---- -------------------------- 9 | 10 | 2011-07-15 18: 20: 20.05 + 02 | 2011-07-15 19: 05: 00.05 + 02 10 | 9 | 2011-07-15 19: 05: 00.05 + 02 | 2011-07-15 18: 20: 20.05 + 02 10 | 11 | 2011-07-15 19: 05: 00.05 + 02 | 2011-07-15 19: 40: 20.05 + 02 11 | 10 | 2011-07-15 19: 40: 20.05 + 02 | 2011-07-15 19: 05: 00.05 + 02 11 | 13 | 2011-07-15 19: 40: 20.05 + 02 | 2011-07-15 20: 31: 01.05 + 02 11 | 14 | 2011-07-15 19: 40: 20.05 + 02 | 2011-07-15 20: 35: 11.05 + 02 13 | 11 | 2011-07-15 20: 31: 01.05 + 02 | 2011-07-15 19: 40: 20.05 + 02 13 | 14 | 2011-07-15 20: 31: 01.05 + 02 | 2011-07-15 20: 35: 11.05 + 02 14 | 11 | 2011-07-15 20: 35: 11.05 + 02 | 2011-07-15 19: 40: 20.05 + 02 14 | 13 | 2011-07-15 20: 35: 11.05 + 02 | 2011-07-15 20: 31: 01.05 + 02 (10 қатар)

Шешім туралы:

m1.id идентификатор ретінде, m2.id матч_ид ретінде, m1.startDate origTime ретінде, m2.startDate ретінде matchTime таңдаңыз myTable m1 ішінен st_intersects (m1.geom, m2.geom) ішіндегі myTable m2 қосылыңыз және (m1.startDate - m2). startDate <= интервал '1 сағат' немесе - (m1.startDate - m2.startDate) <= interval '1 hour') және m1.id! = m2.id

Postgis ST_Intersects сұранысы бар кеңістіктік индексті қолданбайды

Менде қала маңы кестесі бар, ал әр қала маңында геомен мәні бар, олар мультиполигонды картада бейнелейді. Әр үйдің кестесінде тағы бір кесте бар, онда әр үйдің картадағы нүктесінің геом мәні бар.

Екі геом бағанасы да индекс бойынша келтірілген, ал қала маңындағы кестеде атаулар бағаны да индекстелген. Қала маңы кестесінде 8k + жазба, ал үй кестесінде 300k + жазба бар.

Енді менің міндетім - қала маңындағы барлық үйлерді табу 'FOO'.

3,5 секунд, 486 жазбаны қайтарады.

№ 2 СҰРАҚ: (ST_INTERSECTS префиксі, функциясы _, индексті қолданбауын анық сұрау үшін)

Сұрау жоспарының нәтижесі: (№1 сұраныспен бірдей)

1,7 с, 486 жазбаны қайтарады.

№ 3 СҰРАҚ: (ST_Intersects функциясының алдында шекара терезесінің қабаттасуын тексеру үшін & amp & amp операторын пайдалану)

сұранысты орындау 0,15 секундты құрап, 486 жазбаны қайтарып берді.

Шамасы, №3 сұрау тек кеңістіктік индекстен пайда табады, бұл өнімділікті айтарлықтай жақсартады. Алайда синтаксис ұсқынсыз және біршама кеңейтілгенге дейін қайталанады. Менің сұрағым:


Қалайсың «координатаның 5 миль радиусындағы барлық жолдар», сондықтан бұл емес дәл K-жақын көрші (KNN) проблемасы. Байланысты, бірақ сіздің ісіңіз қарапайым. «Менің координаттарыма жақын 10 жолды табыңыз» KNN проблемасы болар еді.

Координаттарыңызды география мәндеріне ауыстырыңыз:

Сонымен қатар, қарапайым геометрия түрін қолдануға болады. Қарастырыңыз:
4.2.2. Геометрия деректерінің типі бойынша география қашан қолданылады

Содан кейін бізде келесідей кесте бар:

Сізге бар керегі ST_DWithin () - және а кеңістіктік көрсеткіш тез жасау үшін:

Немесе сіз өзіңіздің бастапқы бағандарыңызды қолдана аласыз және функционалды индекс жасай аласыз. Осы және басқа да егжей-тегжейлі жауаптағы dba.SE жауаптары:

Алдымен CVY пәрменін пайдаланып CSV пішімделген файлыңыздан кесте жасауыңыз керек (егер файлға PostgreSQL қол жетімді болса) сервер) немесе файл серверге жергілікті болмаса, psql ішіндегі copy пәрмені. Егер сізде қиындық туындаса, SO-дағы басқа Q + A мысалдарын қараңыз.

Деректеріңізді кестеге енгізгеннен кейін сіз өзіңіздің бойлық пен ендік бағандарыңызды PostGIS география түріне түрлендіріп, географиялық түрдегі кестеңізге (POINT, 4326) бағанды ​​қосып, содан кейін сол бағанды ​​(мұнда gps деп аталады) сәйкесінше толтырыңыз. құндылықтар:

Тиімді іздеуге мүмкіндік беру үшін бағанға индекс қосыңыз:

Енді сіз жолдарды берілген жерден 5 миль қашықтықта таба аласыз, мысалы. (-72.657, 42.0657), келесідей:

ST_DWithin () география бағанында метрмен жұмыс істейтінін ескеріңіз, сондықтан сіз радиусыңызды мильмен 1609 метрге мильге көбейтуіңіз керек.


Екі үлкен кеңістіктік кестелер арасындағы қиылысқан сұранысты оңтайландыру

Мен кеңістіктегі екі кестенің қиылысын жақсартуға тырысып жатқан жоқпын және кесте дизайны, сұраныстар немесе dba конфигурациясы туралы кеңестер алғым келеді.

Кестелер:

Teste.recorte_grade кестесінде дәл қазір 1 655 569 жол бар, бірақ бұл 9 миллион жолдық кестенің осы сынамасы үшін жасалған кіші үлгі.

Teste2.uso_2012 кестесінде 177 888 жол бар, және оларда бұрын-соңды болмайтын мәліметтер бар.

Мәселе:

Мен қалайтыным - екі кестенің арасындағы қиылысудың ауданы мен тор коды, негізінен осы сұраныстың нәтижесі:

Алайда, бұл сұраныс көпке созылды 16 сағат оның орындалуын тоқтату туралы шешім қабылдаған кезде нәтижесіз. Егер суб-үлгіде ұзақ уақыт қажет болса, толық деректер жиынтығымен елестетіңіз.

Екі кесте де бұрын вакуумдық талдаудан өткен.

Мұны әр уақытта бір тор коды үшін бірнеше сұраныста бөліп алған дұрыс шығар деп ойладым. Сондықтан мен хэш индексін жасадым.

Бұл teste2.uso_2012 кестесіндегі деректердің таралуы:

Жеке тор кодтары бойынша бірнеше сұраныстың нәтижелері:

teste.recorte_2012 және teste2.uso_2012 - кесте, мұнда uso_2012 1 баған кем.

Көріп отырғаныңыздай, бұл өте перспективалы емес көрінеді. Бұл процесті жылдамдатуға ұсыныс бар ма?

Мен 177 888 қатарға цикл жасап, олардың қиылыстары мен олардың әрқайсысының ауданын алу үшін сақталған процедураны құруды ойлаймын. Бұл жақсы идея ма?

Конфигурациялар:

Сервер туралы ақпарат:

  • PostgreSQL 9.2.14
  • CENTOS 6.4 шығарылымы
  • 8GB SRAM
  • V7000 САҚТАУ
  • INTEL (R) XEON (R) CPU E5-2620 2 ГГц
  • POSTGIS = «2.0.2 r10789» GEOS = «3.3.6-CAPI-1.7.6» PROJ = «4.8.0, 2012 ж. 6 наурыз» GDAL = «GDAL 1.9.2, 2012/10/08» шығарылған LIBXML = «2.7.6» RASTER

Сервер басқа дерекқорлармен бөлісілген, бірақ мен сұраныстарды орындайтын кезімде ешқандай ауыр процесс қатар жүрмеді.

Менде 100 км шыңдары бар өте күрделі ерекшеліктер бар. Postgres нұсқасы туралы тек DBA ғана инфрақұрылымды жаңарта алады, мен олардың қатарына кірмеймін.


4.4. ГАЖ деректерін алу

Мәліметтер базасынан SQL немесе Shape файл жүктеушісі / дамбері көмегімен шығаруға болады. SQL бөлімінде біз кеңістіктегі кестелерде салыстырулар мен сұраулар жасауға болатын кейбір операторларды талқылаймыз.

4.4.1. SQL қолдану

Мәліметтер базасынан деректерді шығарудың ең қарапайым әдісі SQL таңдау сұрауын қолдану және алынған бағандарды бөлшектелетін мәтіндік файлға тастау болып табылады:

Алайда, қайтарылған өрістердің санын қысқарту үшін қандай-да бір шектеулер қажет болатын кездер болады. Атрибутқа негізделген шектеулер болған жағдайда, жай кеңістіктік емес кестемен бірдей SQL синтаксисін қолданыңыз. Кеңістіктегі шектеулер жағдайында келесі операторлар қол жетімді / пайдалы:

Бұл оператор бір геометрияның шекара терезесі екіншісінің шекарамен қиылысатындығын айтады.

Бұл операторлар екі геометрияның геометриялық бірдей екендігін тексереді. Мысалы, егер 'POLYGON ((0 0,1 1,1 0,0 0))' 'POLYGON ((0 0,1 1,1 0,0 0))' 'болса (ол).

Бұл оператор сәл аңқау, тек геометрияға арналған шектеу терезелерінің бірдей екендігін тексереді.

Әрі қарай, сіз осы операторларды сұрауларда қолдана аласыз. SQL командалық жолында геометриялар мен өрістерді көрсету кезінде «GeomFromText ()» функциясын қолдану арқылы жол кескіндерін геометрияға айналдыру қажет екенін ескеріңіз. Мәселен, мысалы:

Жоғарыдағы сұрау «ROADS_GEOM» кестесіндегі геометрия осы мәнге тең болған жалғыз жазбаны қайтарады.

«& Amp & amp;» операторын қолданғанда, салыстыру мүмкіндігі ретінде BOX3D немесе GEOMETRY көрсетуге болады. GEOMETRY-ді көрсеткен кезде, салыстыру үшін оның шекті терезесі қолданылады.

Жоғарыда келтірілген сұрау салыстыру мақсатында көпбұрыштың шекара терезесін қолданады.

Кеңінен таралған кеңістіктік сұраныс, мүмкін, деректер картасының деректері үшін «карта жақтауын» алу үшін, клиенттің бағдарламалық жасақтамасында, мысалы, деректер шолғышында және веб-картада қолданылатын «кадрға негізделген» сұраныс болуы мүмкін. Фреймге арналған «BOX3D» нысанын пайдаланып, мұндай сұрау келесідей болады:

BOX3D проекциясын анықтау үшін SRID-дің қолданылуына назар аударыңыз. -1 мәні көрсетілген SRID-ді көрсетпеу үшін қолданылады.

4.4.2. Думперді пайдалану

Pgsql2shp кестелік дамбері дерекқорға тікелей қосылып, кестені (сұраныспен анықталуы мүмкін) пішін файлына айналдырады. Негізгі синтаксис:

Пәрмен жолының параметрлері:

Нәтижені белгілі бір файл атауына жазыңыз.

Қосылу үшін дерекқор хост.

Деректер базасында қосылатын порт.

Мәліметтер базасына қосылу кезінде қолданылатын пароль.

Мәліметтер базасына қосылу кезінде қолданылатын пайдаланушы аты.

Бірнеше геометриялық бағаннан тұратын кестелер үшін пішін файлын жазған кезде қолданылатын геометрия бағаны.

Екілік жүгіргіні қолданыңыз. Бұл операцияны жылдамдатады, бірақ кестеде геометрияға жат емес атрибуттар мәтінге берілмеген жағдайда жұмыс істемейді.

Шикі режим. Гид өрісін тастамаңыз немесе баған атауларынан қашыңыз.

Артқа үйлесімділік үшін: ескі (1.0.0 дейінгі) постгис дерекқорынан демпинг кезінде 3 өлшемді пішім файлын жазыңыз (әдепкі бойынша, бұл жағдайда 2 өлшемді пішінді файл жазу керек). Postgis-1.0.0 + бастап өлшемдер толығымен кодталған.


Жиынтық

Нәтиже таңдалған group_id үшін бір бағанмен бұрылғанын қалайсыз. Пайдаланыңыз айқас кесте () жылдам нәтижелерге қол жеткізу үшін қосымша кестелік модульден. Егер сіз онымен таныс болмасаңыз, алдымен мына жерде оқыңыз:

Жоқ элементтер - NULL. Сіз мұны ішкі сұраныстағы () санауымен COALESCE көмегімен ұстай алмайсыз. Оның орнына сыртқы сұраныста COALESCE қолданыңыз. Ұнату:

Нәтижесінде ешқандай қатарсыз сағаттар әлі жоғалып кеткенін ескеріңіз. Нәтижесінде бос жолдар қажет болса, уақытты толтыру үшін OUTER JOIN пайдаланыңыз. Ұнату:


PostgreSQL-дегі құрама индекстегі бағандардың реті (және сұраныс реті)

Менде 50К қатардан тұратын кесте бар. Бұл іс жүзінде PostGIS кестесі.

Сұрау 4 бөлімнен тұрады (1 міндетті) (3 қосымша)

  1. қиылысу терезесі (географиялық тіктөртбұрыш), 4 лат, ұзын (мен st_intersects қолданамын) [Міндетті]
  2. Күн өрісіндегі күндер диапазоны (мин, макс)
  3. Қазіргі уақытта IN (.) Пайдаланатын файл типі (8 мәтіндік мәнге дейінгі жиынтық), бірақ қажет болса, мен оны уақытша кестеге айналдыра аламын. Мен көптеген адамдарды IN-ді ұнатпайтынын көремін.
  4. Ел (мәтін мәні).

Мен шамамен 100 - 4000 қайтарылған қатар күтемін

Егер мен кестеде құрама индекс жасасам, алдымен қай бағанды ​​қолдануым керек. Жақсы түйіршіктелген жері болуы мүмкін (деректер бүкіл әлемге таралады). Қазіргі уақытта менде оны GIST индексі бар.

Басқа индекстер BTREE болады.

Менің түйсігім ұсақ түйіршіктерді қолданыңыз және әрине, дейді. Мысалы. Тек шамамен 12 файл түрі бар, сондықтан индекс үшін өте үлкен шелектер болар еді.

PostgreSQL және PostGIS гурулары (жүйенің ішкі элементтерін білетін) не айтады?

Осы сұрақты өткір қоюға рұқсат етіңіздер.

  1. Мен біреудің маған керек жұмысты орындағанын қаламаймын. Мен сіздің уақытыңызды тым құрметтеймін. Сондықтан мен түсіндіруге талдауға кейінірек жетемін.
  2. Мен іздеген барлық нұсқаулар мен кеңестер мен нұсқаулар болды.
  3. Мен бұл керемет жазбаны оқыдым: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes индекстер туралы
  4. Әдетте мен 4 жеке индекстерді жасаймын (геобокс, ел атауы, file_type және күн), бірақ құрама сұраныстың не істейтінін көргім келеді.

Осы болжамдардың кез-келгені дұрыс емес болса, маған айтыңыз. (Мен құрама индекстер идеясына жаңадан келдім)

  1. Тапсырыс маңызды. Бірінші индекс ретінде жолдарды көбірек кесетін индексті таңдаңыз (менің жағдайымда жай көпбұрыш немесе көп көпбұрыш болатын орналасу (география) жақсы болады).
  2. Кейде сұраулар индексті өткізіп жібереді. Бірақ мен (# 1, # 2, # 3, # 4) кілтімен күрделі сұраныс жасасам, тіпті егер пайдаланушы # 1, # 3 сұрайтын нәрсе жасаса да, жоспарлаушы бірыңғай құрама сұранысты қолданады, өйткені олар тапсырыс береді сақталады.
  3. Әдетте мен үш BTREE сұрауын және бір GIST (география түрі үшін) жасар едім. PostGIS көптеген индекстер түрінен құрама құруды қолдамайды. Сондықтан маған GIST құрама индексін қолдану керек болады. Бірақ бұл зиянын тигізбеуі керек.
  4. Егер мен қосымша құрама немесе бір мәнді индекстер жасасам, жоспарлаушы ең ақылдысын таңдай алатындай ақылды.
  5. Ел атауы шамамен 250 түрлі мәнге ие болуы мүмкін және орналасуымен (геобокс) қатты байланысты, бірақ егер жол өлшемін қысқартудың келесі жақсы индексі file_type болса, мен оны келесіге пайдалануым керек. Мен пайдаланушылар сұраныстар жиынтығында елді немесе күнді жиі пайдаланады деп ойламаймын.
  6. Мен 4 кілттен тұратын құрама индекс құрамын деп уайымдамаймын, бұл индекс деректерінің көлемін едәуір арттырады. Яғни егер бір кілт индексі өнімділіктің 90% -ын құрайтын болса, оны күрделі етіп жасау үшін тағы 3 элемент қосқаныңыз зиян емес. Керісінше, мен екі индексті де шынымен жасауым керек. Бірыңғай география индексі, сонымен қатар құрама индекс және жоспарлаушыға қайсысы жақсы екенін анықтап беріңіз, сонда ол индекс кестесінің көлемін ескереді.

Тағы да, мен ешкімнен менің шешімімнің дизайнын жасауды сұрамаймын, мен басқалардың жұмысына көңіл бөлмеймін. Бірақ маған PostGreSQL құжаттамасы іске асыру туралы айтпайтын материалдар керек

[Әзірге ТҮСІНДІРУ нәтижесі жоқ, себебі мен осы 25К қатарлы кестені 24М қатарлы кестеден жасауым керек. Бұл менің ойлағаннан көп уақытты алады. Мен заттарды 1000 элементтер тобына топтастырамын және пайдаланушы сұранысына 25K қатар кестесіне жол беремін. Бірақ менің келесі сұрағым MASTER 25M қатарындағы кестеге өтіп, заттарды шығарып алу үшін сол сұраудың нәтижелерін қолдануды қамтиды, және дәл осы жерде құрама индекс өнімділігі HIT болады].

Талдаудың нәтижелерін түсіндіріңіз (мен бірде-бір индекс қоймадым, ал мен көріп отырған жылдамдықтан маған қажет екенін білмеймін).


4.7. Mapserver пайдалану

SVN қайта қарау ( 8935 )

Миннесота Mapserver - бұл OpenGIS веб-карта сервері сипаттамасына сәйкес келетін интернет-карта бейнелеу сервері.

4.7.1. Негізгі пайдалану

SVN қайта қарау ( 8935 )

PostGIS-ті Mapserver көмегімен пайдалану үшін Mapserver-ті қалай теңшеу керектігін білу қажет, бұл осы құжаттаманың шеңберінен тыс. Бұл бөлім PostGIS-тің нақты мәселелерін және конфигурация туралы егжей-тегжейлі қарастырады.

PostGIS-ті Mapserver көмегімен пайдалану үшін сізге қажет:

0.6 нұсқасы немесе одан кейінгі PostGIS.

Mapserver 3.5 немесе одан жаңа нұсқасы.

Mapserver PostGIS / PostgreSQL деректеріне кез келген басқа PostgreSQL клиенті сияқты - libpq көмегімен қол жеткізеді. Демек, жүйеде libpq PostgreSQL клиенттік кітапханалары болған жағдайда, Mapserver-ті PostGIS серверіне қосылу мүмкіндігі бар кез-келген машинада орнатуға болады.

Mapserver-ті кез-келген опциямен, соның ішінде «-with-postgis» конфигурациясының көмегімен құрастырыңыз және орнатыңыз.

Mapserver картасына PostGIS қабатын қосыңыз. Мысалға:

Жоғарыдағы мысалда PostGIS-ке арналған директивалар келесідей:

PostGIS қабаттары үшін бұл әрдайым «постгис».

Деректер қорының қосылымы стандартты кілттер мен мәндердің жиынтығы болып табылатын «байланыс жолы» арқылы басқарылады (& lt & gt стандартты мәндерімен):

user = & ltusername & gt password = & ltpassword & gt dbname = & ltusername & gt hostname = & ltserver & gt port = & lt5432 & gt

Бос байланыс жолы әлі де күшінде, және кез-келген кілт / мән жұбын қалдыруға болады. Кем дегенде, сіз дерекқордың атауын және пайдаланушының атын қосуға мүмкіндік бересіз.

Бұл параметрдің формасы «& ltcolumn & gt from & lttablename & gt» болып табылады, мұнда баған картаға шығарылатын кеңістіктік баған болып табылады.

Фильтр SQL сұранысының «WHERE» кілт сөзінен кейінгі логикаға сәйкес келетін жарамды SQL жолы болуы керек. Мәселен, мысалы, 6 немесе одан да көп жолақты жолдарды көрсету үшін «num_lanes & gt = 6» сүзгісін қолданыңыз.

Сіздің кеңістіктік деректер базаңызда сіз салатын кез-келген қабаттар үшін кеңістіктік (GiST) индекстердің болуын қамтамасыз етіңіз.

Егер сіз Mapserver көмегімен өз қабаттарыңызға сұраныс жасайтын болсаңыз, сізге «қатысты индекс» қажет болады.

Mapserver сұраныстар жасаған кезде әрбір кеңістіктік жазба үшін бірегей идентификаторларды қажет етеді, және Mapserver-тің PostGIS модулі PostgreSQL oid мәнін осы бірегей идентификаторларды қамтамасыз ету үшін пайдаланады. Мұның жанама әсері мынада, сұраулар кезінде жазбаларға жылдам кездейсоқ қол жетімділікті жасау үшін индекс индексі қажет.

«Ой индексін» құру үшін келесі SQL пайдаланыңыз:

4.7.2. Қойылатын сұрақтар

SVN қайта қарау ( 8935 )

Мен өзімнің карта файлымда EXPRESSION қолданған кезде, шарт ешқашан шындыққа айналмайды, тіпті егер менің кестеде мәндер бар екенін білсем де.

Пішіндік файлдардан айырмашылығы, PostGIS өрісінің аттарына EXPRESSIONS сілтемесін қолдану керек кіші әріп .

Мен Shape файлдары үшін қолданатын СҮЗГІ менің PostGIS кестесінде жұмыс істемейді.

Пішіндік файлдардан айырмашылығы, PostGIS қабаттарына арналған сүзгілер SQL синтаксисін пайдаланады (олар SQL операторына қосылады, PostGIS коннекторы Mapserver-те қабаттарды салуға арналған).

Менің PostGIS деңгейім Shape файл деңгейіне қарағанда әлдеқайда баяу, бұл қалыпты ма?

Жалпы, PostGIS қабаттары баламалы Shape файлдарының қабаттарына қарағанда 10% баяу болады деп күтіңіз, бұл қосымша мәліметтер базасын қосуға, деректерді түрлендіруге және мәліметтер базасы мен Mapserver арасындағы транзитке байланысты.

Егер сіз сурет салудың маңызды проблемаларын тапсаңыз, онда сіздің үстеліңізде кеңістіктік индекс құрмаған шығарсыз.

Менің PostGIS қабаты өте жақсы, бірақ сұраныстар өте баяу. Не дұрыс емес?

Сұраулар тез болуы үшін кеңістіктегі кесте үшін бірегей кілт және сол бірегей кілтте индекс болу керек.

DATA жолында mapserver үшін қандай бірегей кілтті USOR UNICQUE сөйлемімен қолдану керектігін көрсетуге болады:

Егер сіздің кестеңізде айқын бірегей баған жоқ болса, онда сіз өзіңіздің бірегей бағаныңыз үшін PostgreSQL «oid» жолын пайдаланып бірегей бағанды ​​«жалған» жасай аласыз. «oid» әдепкі бойынша бірегей баған, егер сіз оны жарияламасаңыз, онда сұраныстың жылдамдығын арттыру сіздің кеңістіктегі кесте туралы индекс құру мәселесі болып табылады.

4.7.3. Кеңейтілген пайдалану

SVN қайта қарау ( 8935 )

USS псевдо-SQL сөйлемі Mapserver-ке неғұрлым күрделі сұраныстардың нәтижелерін түсінуге көмектесетін ақпарат қосу үшін қолданылады. Нақтырақ айтсақ, көрініс немесе ішкі таңдау бастапқы кесте ретінде пайдаланылғанда (DATA анықтамасында «FROM» оң жағында орналасқан нәрсе) mapserver үшін әр жол үшін бірегей идентификаторды автоматты түрде анықтау қиынға түседі, сонымен қатар SRID кесте үшін. ПАЙДАЛАНУ тармағы келесі екі ақпаратпен бірге mapserver-ті ұсына алады:

Mapserver картаға сұраныстар жасау кезінде жолды анықтау үшін әр жол үшін ерекше идентификаторды қажет етеді. Әдетте, бұл обьектілерді бірегей идентификатор ретінде пайдаланады, бірақ көріністер мен ішкі таңдауларда автоматты түрде бағана болмайды. Егер сіз Mapserver сұранысының функционалдығын пайдаланғыңыз келсе, сіз өзіңіздің көзқарасыңызға немесе ішкі таңдауыңызға бірегей баған қосып, оны ЕСЕПТІ ПАЙДАЛАНУ арқылы жариялауыңыз керек. Мысалы, сіз осы мақсат үшін кестенің қосымша мәндерінің бірін немесе нәтижелер жиынтығы үшін бірегей екендігіне кепілдік беретін кез-келген бағанды ​​нақты таңдай аласыз.

Егер сіз картаға сұраныс жасасаңыз, USING операторы қарапайым DATA мәлімдемелері үшін де пайдалы болуы мүмкін. Бұрын картаға сұраныстарды жеделдету үшін сұраныстарға қабілетті қабаттарда қолданылатын кестелер туралы бағанға индекс қосу ұсынылды. Алайда, USING сөйлемімен mapserver-ке кестедегі негізгі кілтті карта сұраныстары үшін идентификатор ретінде пайдалану туралы айтуға болады, содан кейін қосымша индекс қажет емес.

«Картаны сұрау» дегеніміз - картаға басу арқылы сол жерде орналасқан карта ерекшеліктері туралы ақпарат сұрау. DATA анықтамасында «карта сұраулары» мен SQL сұрауын шатастырмаңыз.

PostGIS геометрияға қай кеңістіктік сілтеме жүйесін қолданып, дұрыс деректерді mapserver-ке қайтару үшін қажет. Әдетте бұл ақпаратты PostGIS мәліметтер базасындағы «геометрия_бағандары» кестесінен табуға болады, алайда бұл ішкі таңдаулар мен көріністер сияқты жылдам жасалынатын кестелер үшін мүмкін емес. Сонымен, SRID-ді ҚОЛДАНУ опциясы DRA анықтамасында дұрыс SRID-ді көрсетуге мүмкіндік береді.

Ескерту

MapGerver PostGIS қабаттарына арналған талдаушы өте қарапайым және бірнеше облыстарда регистрге сезімтал. Барлық SQL кілт сөздері мен сіздің барлық USING сөйлемдеріңіз бас әріппен жазылғандығына және сіздің USER UNIQUE сөйлеміңіз сіздің USRING SRID сөйлеміңізден бұрын екеніне көз жеткізіңіз.


4 жауап

Көп жағдайда индекстің сұрыпталу тәртібі екіталай. Postgres артқа қарай жылдам сканерлей алады. Бірақ бірнеше бағандардағы диапазондағы сұраулар үшін а үлкен айырмашылық. Жақын байланысты:

Индексіндегі id_phi бірінші бағанының сұрыптау реті маңызды емес. Ол тексерілгендіктен теңдік (=), ол бірінші орында тұруы керек. Сіз мұны дұрыс түсіндіңіз. Осы жауап туралы толығырақ:

Postgres уақыт өте келе id_phi = 0 мәніне өтіп, сәйкес индекстің келесі екі бағанын қарастыра алады. Бұлар сұралады төңкерілген сұрыптау ретті ауқымының шарттары (& lt =, & gt =). Менің индексімде біліктілік қатарлары бірінші орында. B-Tree индексімен ең жылдам әдіс болуы керек 1 :

  • Сіз start_date_time & lt = бір нәрсе алғыңыз келеді: индекс ең ерте уақыт белгісін алады.
  • Егер ол талапқа сай болса, 3-бағанды ​​да тексеріңіз.
    Бірінші қатар талапқа сай болмайынша қайталаңыз (супер жылдам).
  • Сіз end_date_time & gt = бір нәрсе алғыңыз келеді: индекс ең алдымен ең соңғы уақыт белгісін алады.
  • Егер ол талапқа сай болса, біріншісі болмайынша жолдарды ала беріңіз (өте жылдам).
    2-баған үшін келесі мәнмен жалғастырыңыз.

Postgres алға қарай сканерлей алады немесе артқа. Индексті қалай оқыдыңыз, соны оқып шығуыңыз керек барлық алғашқы екі бағанға сәйкес жолдар, содан кейін сүзгі үшіншіде. Тарауды міндетті түрде оқып шығыңыз Көрсеткіштер мен тапсырыс нұсқаулықта. Бұл сіздің сұрағыңызға өте жақсы сәйкес келеді.

Алғашқы екі бағанда неше жол сәйкес келеді?
Кестенің уақыт диапазонының басталуына жақын бірнеше старт_күні. Бірақ барлығы дерлік кестенің хронологиялық соңында id_phi = 0 болатын жолдар! Сондықтан жұмыс уақыты кейінірек нашарлайды.

Жоспарлаушының бағалауы

Жоспарлаушы сіздің сұрауыңыз үшін жолдарды = 62682 деп есептейді. Олардың ешқайсысы талапқа сай келмейді (жолдар = 0). Кесте бойынша статистикалық мақсатты көбейтсеңіз, жақсырақ бағалауға болады. 2.000.000 жол үшін.

. төлеуі мүмкін Немесе одан да жоғары. Осы жауап туралы толығырақ:

Менің ойымша, бұл сізге id_phi үшін қажет емес (тек аз ғана мәндер, біркелкі бөлінген), бірақ уақыт белгілері үшін (көптеген айқын мәндер, біркелкі бөлінбеген).
Менің ойымша, бұл жақсартылған индексте маңызды емес.

CLUSTER / pg_repack / pg_squeeze

Егер сіз оны тезірек алғыңыз келсе, кестедегі жолдардың физикалық ретін оңтайландыруға болады. Егер сіздің үстеліңізді тек қана құлыптауға мүмкіндігіңіз болса (мысалы, жұмыс уақытында), кестеңізді қайта жазып, индекске сәйкес жолдарды CLUSTER көмегімен тапсырыс беріңіз:

Немесе pg_repack немесе кейінгі pg_squeeze-ді қарастырайық, мұны үстелдегі эксклюзивті құлыпсыз жасай алады.

Қалай болғанда да, нәтиже аз блоктарды кестеден оқып шығу керек және бәрі алдын-ала сұрыпталған. Бұл физикалық сұрыптау ретін фрагменттерге кестеге жазып, уақыт өте келе нашарлайтын бір реттік әсер.


PostgreSQL-те LiDAR деректерімен жұмыс істеу

Патчтар - бұл Pointcloud деректерін PostgreSQL кестелерінде сақтаудың тиімді тәсілі, мұнда әр патч бірнеше жақын нүктелерден тұрады. Бұл мәліметтер үшін жолдар санын едәуір азайтуға көмектеседі және кестедегі операцияларды жылдамырақ етеді. Патчтар мен нүктелер схемасы мәліметтер базасындағы pointcloud_formats кестесінде сипатталған. Сонымен қатар, pointcloud кеңейтімі нүктелермен де, патчтармен де жұмыс істеуге мүмкіндік береді, оның ішінде биіктіктер, интенсивтілік немесе координаталар сияқты жеке атрибуттар әр нүкте үшін немесе патчтардың шектік қорап өлшемдері. Егер сіз көбірек оқығыңыз келсе, Пол Рэмсидің OpenGEO-дағы осы презентациясы шынымен де нүктелер мен патчтарға арналған мысалдармен жақсы түсініктемелерге ие.

Барлық келесі қадамдар pgpointcloud контейнерінде жүзеге асырылады, ол үшін сіз келесі командаларды қолдана аласыз -

Біз LiDAR мәліметтерін ғимараттың биіктігі мен биіктігін шығару үшін OSM ғимаратының іздерімен біріктіреміз. Сонымен, pgpointcloud контейнері ішінен келесі пәрменді пайдаланып OSM деп аталатын кестеге алдымен жоғарыда сипатталған OSM пішінді файл деректерін жүктейік.

Қалған қадамдардың көпшілігінде біз PostgreSQL сұрауларын қолданамыз. Сіз келесі пәрменді пайдаланып мәліметтер базасына қол жеткізе аласыз және барлық сұраныстарды дерекқор ішінен іске қосасыз.

Енді біз құрылыстың биіктігі мен биіктігін алуға дайынбыз және егжей-тегжейлі процесс төменде сипатталған. Ғимараттардың биіктіктерін алу үшін шолу процедурасы алдымен OSM ғимарат іздері ішіндегі барлық LiDAR нүктелерін анықтап, содан кейін ғимараттың биіктігін осы нүктелер жинағынан алу болып табылады. Ғимараттың биіктігін алу үшін шолу алдымен әр ғимараттың ізінің сызбасы бойынша жердің биіктігін бағалап, оны ғимараттың биіктігінен алып тастайды.

  1. Кеңістіктік индекс құру үшін лидар кестесін өңдеңіз.
    а. Әр нүктенің атрибуттарын патчпен қарау. Патчтар мен нүктелердің форматтары XML форматында pointcloud_formats деп аталатын жеке кестеде сипатталған.
    Pointcloud_formats * FROM таңдаңыз
    б. Кестедегі бағандар тізімін қараңыз. Сіз патчтардың па бағанында сақталғанын байқайсыз.
    d лидар
    в. Кестедегі патчтардың және жалпы нүктелердің санын қарау -
    САНЫ (*), ҚОСЫМШАСЫ (PC_NumPoints (pa)) FROM lidar
    г. Лидар кестесінде жылдамырақ сұраныстар үшін кеңістіктік индекс жасаңыз (жылдамдық 40 рет байқалады) -
    ТІЗІМДІ ПАЙДАЛАНУ ҮШІН ИНДЕКС lidar_envelope_pkey құру (PC_EnvelopeGeometry (pa))
  2. Кеңістік индексін құру үшін osm кестесін өңдеңіз.
    а. Қаланың шекарадан тыс нүктелерін төмендегі сұрау арқылы жоюға болады. (Lng_min, lat_min, lng_max, lat_max) қызықтыратын шекті терезенің өлшемдерімен ауыстырыңыз, мұндағы lng бойлықты, ал lat ендікті білдіреді.
    Osm.gid IN қай жерде osm.gid IN (SELECT a.gid from osm a WHERE NOT ST_Intersects (a.geom, ST_MakeEnvelope (lng_min, lat_min, lng_max, lat_max, 4326))))
    б. Осм геомериялық баған геомын лидар кестесінің сол SRID-ге (кеңістіктік сілтеме жүйесі) түрлендіруге кеңес беремін. UTM аймақтары кішігірім аймақ пен өлшеулерді шешуге өте ыңғайлы, сондықтан геомды EPSG: 26910 түріне айналдырамыз, бұл LiDAR деректері үшін бірдей сілтеме.
    ALTER TABLE osm ALTER COLUMN geom TYPE геометриясы (MultiPolygon, 26910) ST_Transform ПАЙДАЛАНУ (geom, 26910)
    GIST (geom) ПАЙДАЛАНУЫ бойынша osm_26910_pkey индексін жасаңыз
  3. Ғимараттың әрбір ізінде орналасқан нүктелердің дақтарынан тұратын жаңа баған жасаңыз.
    а. Патчтарды сақтау үшін osm-ге жаңа баған қосу үшін алдымен d лидар көмегімен лидардағы патч түрін тексеріңіз. Әдетте бұл pcpatch (1) болар еді. Патчтарды сақтау үшін осмге жаңа баған қосыңыз
    БАСҚАРЫСЫН ҚОСУ pa pcpatch (1)
    б. Әрбір ғимарат ізінің көпбұрышына қабаттасқан pointcloud патчын сақтаңыз. Төмендегі сұрау алдымен әр ғимарат ізін қиып өтетін барлық патчтарды анықтайды, содан кейін олардың біріктірілуін есептейді және соңында іздердің ізімен қиылысу патчының қиылысын табады. Бұл сұрау бірнеше сағат алады деп күтіңіз.
    OSM SET pa = sq.pa FROM (AS жамауымен (SELECT o.gid AS gid, o.geom AS geom, l.pa AS pa FROM from lidar AS l JOIN osm AS o ON PC_INTERSECTS (l.pa, o.geom) )) SELECT gid, PC_INTERSECTION (PC_UNION (pa), geom) AS pa FROM patches from GROUP BY gid, geom) AS sQ WHERE osm.gid = sq.gid
  4. Біз ғимараттың биіктігін ғимарат ізінің барлық нүктелерінің биіктігі үшін статистика ретінде сипаттайтын боламыз. Статистика сіздің пайдалану жағдайыңызға байланысты таңдалуы мүмкін. Мұнда мен барлық нүктелерден максималды, орташа және медианалық биіктіктерді есептеймін. LiDAR деректеріндегі Z-мәнінен гөрі оның метамәліметтерінде көрсетілген белгілі бір деректер деңгейінің жоғарылауы болып табылады (бұл жағдайда NAD83). Тікелей максимумды қабылдаудың орнына, ең жоғары деңгейдің асыра бағалануына әкеліп соқтыратын мүмкіндікті азайту үшін 99,9 пайыздық мән деп есептеледі.
    а. Осмға тиісті бағандарды қосу -
    КЕСТЕ ӨЗГЕРТУ osm БАҒАНЫ ҚОСУ z_avg ЕКІ НӘШІЛДІК NULL, БАҒАНЫ ҚОСУ z_median ҚОС ҚОСЫМШАЛЫҚ NULL, БАҒАНЫ ҚОСУ z_max ЕКІ НАҚТЫ НҰЛ
    б. Биіктік статистикасын есептеңіз және сақтаңыз
    OMD SET z_avg = sq.z_avg, z_median = sq.z_median, z_max = sq.z_max FROM (WITH patch AS (SELECT o.gid AS gid, PC_GET (PC_EXPLODE (o.pa), 'Z') AS pt_z FROM osm AS o) SELECT gid, AVG (pt_z) AS z_avg, PERCENTILE_CONT (0.5) GROUP IN (ORDER BY pt_z) AS z_median, PERCENTILE_CONT (0.999) GROUP WITH (ORDER BY pt_z) AS z_max WH OST) .gid = sq.gid
  5. Құрылыс сызбасын анықтап, оны LiDAR мәліметтерімен біріктіру арқылы жер биіктігін алыңыз.
    а. Osm_outline деп аталатын жаңа кестені osm кестесінің көшірмесі ретінде ғимараттың көпбұрыштарының айналасындағы 2м және 1м буфер арасындағы айырмашылықты ескере отырып құрылған құрылымның құрылымын өңдеу үшін жасаңыз.
    OSM_outline КЕСТЕСІН ЖАСАУ GID, osm_id, geom FROM osm
    UMD osm_outline SET geom = буфер FROM (SELECT o.gid, ST_MULTI (ST_DIFFERENCE (ST_MULTI (ST_Buffer (o.geom, 2))), ST_MULTI (ST_BUFFER (o.geom, 1)))) AS buffer FROM osmout sq қайда osm_outline.gid = sq.gid
    GIST (geom) ПАЙДАЛАНУ osm_outline_pkey КӨСІМІ
    б. Контурмен қиылысатын pointcloud патчтарын сақтаңыз. Төмендегі сұрау алдымен әрбір контурмен қиылысатын барлық патчтарды анықтайды, содан кейін олардың біріктірілуін есептейді және соңында контурмен біріктіру патчының қиылысын табады. Бұл сұрау бірнеше сағат алады деп күтіңіз.
    КЕСТЕСІН ӨЗГЕРТУ osm_outline БАҒАНЫ ҚОСУ pa pcpatch (1)
    ЖАҢАРТУ osm_outline SET pa = sq.pa FROM (AS AS patch (SELECT o.gid AS gid, o.geom AS geom, l.pa AS pa FROM from lidar AS l JOIN osm_outline AS o ON PC_INTERSECTS (l.pa, o.geom) )) SELECT gid, PC_INTERSECTION (PC_UNION (pa), geom) AS pa FROM patches from GROUP BY gid, geom) AS sQ WHERE osm_outline.gid = sq.gid
    в. Ғимараттың контурымен қиылысатын нүктелік бұлттан Z минималды мәнін сақтаңыз. Тікелей минимумды қабылдаудың орнына, жердің биіктігін жете бағаламауға әкеп соқтыратын мүмкіндікті азайту үшін 1-ші пайыздық мән деп есептеледі.
    КЕСІПТІ ӨЗГЕРТУ osm_outline БАҒАНЫ ҚОСУ z_ground Екі еселенген дәлдік NULL
    UMD osm_outline SET z_ground = sq.z_min FROM (AS AS (SELECT o.gid AS gid, PC_GET (PC_EXPLODE (o.pa), 'Z') AS pt_z FROM osm_outline AS o) SELECT gid, PERCENTILE_CONT (0.01) GROUP) (ТАПСЫРЫС pt_z) AS z_min ЖАМАЛАРДАН GROUP BY gid) AS кв. ҚАЙДА osm_outline.gid = sq.gid
    г. Бастапқы осм кестесіне жер биіктігін қосыңыз және биіктіктерді іздер биіктігі мен жер биіктіктерінің айырмашылығы ретінде есептеңіз.
    АЛТОР КЕСТЕСІ osm БАҒАНЫ ҚОСУ z_ground ҚОС ЕСЕПТІЛІК NULL, БАҒАНДЫ ҚОСУ height_avg ЕКІ ЕСЕПТІЛІК NULL, БАҒАН ҚОСУ height_median ЕКІ ЕСЕП NULL, БАҒАНЫҢ биіктігі_max ЕКІ ЕСЕП NULL NULL
    Osm AS O SET z_ground = oo.z_ground FROM osm_outline oo қайда o.gid = oo.gid
    ЖАҢАРТУ osm SET height_avg = (z_avg - z_ground), height_median = (z_median - z_ground), height_max = (z_max - z_ground)

Міне бітті! You have successfully calculated the building elevations, ground elevations, and building heights for each building and all this data is stored in the osm table along with the building footprint geometry for each building.

If you would like to visualize the elevations and heights, you can export your data to a Shapefile. First exit the database using q , and then run the following command to export the osm table to the file named sanfrancisco_buildings.shp —

I imported the Shaepefile into QGIS, and used the Qgis2threejs plugin to create an interactive output as shown at the top of this article. The plugin allows showing both ground elevations and buildings in 3D, and the visualization can be accessed easily from any browser.

Interested in learning more about our technology? Head over to our веб-сайт or our Medium page to read about some of our recent projects!