Vairāk

PostGIS vaicājums: Visas rindas viena stundas laikā viena no otras krustojas

PostGIS vaicājums: Visas rindas viena stundas laikā viena no otras krustojas


Es izmantoju postgres (postgis), un es vēlos izpildīt vaicājumu, kas 1 stundas laikā viens no otra atgriež visas rindas, kuru ģeometrija krustojas un kurām ir laika zīmogs.

Kā izskatītos SQL priekšraksts?

tabula izskatās šādi:

id {integer} nosaukums {raksturs mainīgs} robežas {ģeometrija} laikspiedols {laika zīmogs ar laika joslu} // zemāk esošajā tabulas piemērā apzināti atstātas robežas (nav nepieciešams) id nosaukuma laika zīmogs --- + -------- - + -------------------------------- 1 viens "2010-09-24 21: 10: 39.515 + 00 "2 divi" 2010-09-16 09: 21: 09.362 + 00 "3 trīs" 2010-07-08 00: 00: 46.549 + 00 "…

Rediģēt # 1

Šeit nav piemērs iekļaut krustojumu atbilstību: katrai rindai norādiet citas rindas, kas ir mazāk par 1 stundu:

atlasiet * no myTable secības pēc t id | 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 rindas) nepieciešamā vaicājuma rezultāts: id | matchid | 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 rindas)

Par ko:

atlasiet m1.id kā id, m2.id kā match_id, m1.startDate kā origTime, m2.startDate kā matchTime no myTable m1 iekšējā savienojuma myTable m2 vietnē st_intersects (m1.geom, m2.geom) un (m1.startDate - m2. startDate <= intervāls '1 stunda' vai - (m1.startDate - m2.startDate) <= intervāls '1 stunda') un m1.id! = m2.id

Postgis ST_Intersects vaicājums neizmanto esošo telpisko indeksu

Man ir priekšpilsētu tabula, un katram priekšpilsētai ir ģeogrāfiskā vērtība, kas kartē attēlo tā daudzpoliju. Ir vēl viena māju tabula, kur katrai mājai kartē ir sava punkta ģeogrāfiskā vērtība.

Abas ģeom kolonnas tiek indeksētas, izmantojot būtību, un priekšpilsētu tabulā ir indeksēta arī nosaukuma kolonna. Priekšpilsētas tabulā ir 8k + ieraksti, savukārt māju tabulā ir 300k + ieraksti.

Tagad mans uzdevums ir atrast visas mājas priekšpilsētā ar nosaukumu “FOO”.

3.5s, atgriežot 486 ierakstus.

2. vaicājums: (prefikss ST_INTERSECTS funkcija ar _, lai skaidri lūgtu neizmantot indeksu)

Vaicājuma plāna rezultāts: (tieši tāds pats kā vaicājums Nr. 1)

1.7s, atgriežot 486 ierakstus.

3. vaicājums: (Operatora & amp & amp izmantošana, lai pievienotu robežu lodziņa pārklāšanās pārbaudi pirms funkcijas ST_Intersects)

vaicājuma izpilde ilga 0,15 sekundes, atgriežot 486 ierakstus.

Acīmredzot tikai vaicājums Nr. 3 gūst labumu no telpiskā indeksa, kas ievērojami uzlabo veiktspēju. Tomēr sintakse ir neglīta un zināmā mērā atkārtojas. Mans jautājums ir:


Tu gribi "visas rindas 5 jūdžu rādiusā no koordinātas", tā tas ir tieši K-tuvāko kaimiņu (KNN) problēma. Saistīts, bet jūsu lieta ir vienkāršāka. "Atrodiet 10 rindas, kas ir vistuvāk manām koordinātām" būtu KNN problēma.

Konvertējiet koordinātas uz ģeogrāfiskām vērtībām:

Alternatīvi jūs varētu izmantot vienkāršāku ģeometrijas veidu. Apsveriet:
4.2.2. Kad izmantot ģeogrāfijas datu tipu virs ģeometrijas datu veida

Tad mums ir šāda tabula:

Viss, kas tev nepieciešams, ir ST_DWithin () - un a telpiskais indekss lai tas būtu ātri:

Or varat izmantot sākotnējās kolonnas un izveidot funkcionālu rādītāju. Šī un cita informācija šajā cieši saistītajā atbildē vietnē dba.SE:

Vispirms jums jāizveido tabula no CSV formatētā faila, izmantojot komandu COPY (ja fails ir pieejams PostgreSQL serveris) vai komandu copy psql, ja fails nav lokāls serverim. Ja jums ir kādas problēmas, skatiet citus jautājumus Q + A SO.

Kad dati ir iekļauti tabulā, jums jāpārvērš garuma un platuma kolonnas par PostGIS ģeogrāfijas veidu, pievienojot kolonnu sava veida ģeogrāfijas tabulai (POINT, 4326) un pēc tam aizpildot šo kolonnu (šeit saukta GPS) ar atbilstošo vērtības:

Pievienojiet indeksu šai kolonnai, lai varētu veikt efektīvus meklējumus:

Tagad jūs varat atrast rindas 5 jūdžu attālumā no norādītās vietas, piem. (-72.657, 42.0657) šādi:

Ņemiet vērā, ka ģeogrāfijas kolonnā ST_DWithin () darbosies metros, tāpēc rādiuss jāturpina jūdzēs ar 1609 metriem jūdzē.


Krustojuma vaicājuma optimizēšana starp divām milzīgām telpiskām tabulām

Man ir grūti mēģināt uzlabot divu telpisko tabulu krustojumu, un es vēlētos saņemt padomus par tabulas dizainu, vaicājumiem vai dba konfigurācijām.

Galdi:

Tabulā teste.recorte_grade šobrīd ir 1 655 569 rindas, taču šis 9 miljonu rindu tabulas testam ir izveidots apakšparaugs.

Tabulā teste2.uso_2012 ir 177 888 rindas, un tas ir visi dati, kas tai jebkad būs.

Problēma:

Viss, ko es vēlos, ir katra krustojuma starp abām tabulām laukums un režģa kods, galvenokārt šī vaicājuma rezultāts:

Tomēr šis vaicājums ilga apmēram 16 stundas bez rezultāta, kad nolēmu atcelt tā izpildi. Ja ar apakšparaugu pagāja tik ilgi, iedomājieties, izmantojot pilnu datu kopu.

Abas tabulas iepriekš tika vakuumā analizētas.

Es domāju, ka varētu būt laba ideja katru reizi to atdalīt vairākos vaicājumos vienam režģa kodam. Tāpēc es izveidoju hash indeksu.

Šis ir datu sadalījums tabulā teste2.uso_2012:

Šeit ir daži vaicājumu rezultāti atsevišķiem režģa kodiem:

teste.recorte_2012 un teste2.uso_2012 ir gandrīz tā pati tabula, kur uso_2012 ir par 1 kolonnu mazāk.

Kā redzat, tas nešķiet ļoti daudzsološi. Vai ir kāds ieteikums paātrināt šo procesu?

Es domāju izveidot glabātu procedūru, lai izveidotu 177 888 rindu cilpu un iegūtu tieši katras no tām krustojumus un laukumu. Vai tā ir laba ideja?

Konfigurācijas:

Informācija par serveri:

  • PostgreSQL 9.2.14
  • CENTOS RELEASE 6.4
  • 8 GB SRAM
  • UZGLABĀŠANA V7000
  • INTEL (R) XEON (R) CPU E5-2620 2 GHz
  • POSTGIS = "2.0.2 r10789" GEOS = "3.3.6-CAPI-1.7.6" PROJ = "Rel. 4.8.0, 2012. gada 6. marts" GDAL = "GDAL 1.9.2, izlaists 2012.10.08." LIBXML = "2.7.6" RASTER

Serveris tiek koplietots starp citām datu bāzēm, taču tajā pašā laikā, kad es izpildīju vaicājumus, paralēli nedarbojās smags process.

Man ir dažas īpašas funkcijas, kas ir ļoti sarežģītas ar gandrīz 100 000 virsotnēm. Par Postgres versiju tikai DBA var atjaunināt infrastruktūru, un es neesmu viens no tiem.


4.4. GIS datu izgūšana

Datus var iegūt no datu bāzes, izmantojot vai nu SQL, vai arī Shape failu iekrāvēju / pašizgāzēju. Sadaļā par SQL mēs apspriedīsim dažus pieejamos operatorus, lai veiktu salīdzinājumus un vaicājumus telpiskajās tabulās.

4.4.1. SQL izmantošana

Visvienkāršākais veids, kā izvilkt datus no datu bāzes, ir SQL atlases vaicājuma izmantošana un iegūto kolonnu ievietošana parsējamā teksta failā:

Tomēr reizēm būs nepieciešami kaut kādi ierobežojumi, lai samazinātu atgriezto lauku skaitu. Uz atribūtiem balstītu ierobežojumu gadījumā vienkārši izmantojiet to pašu SQL sintaksi kā parasti ar telpisku tabulu. Telpisko ierobežojumu gadījumā ir pieejami / noderīgi šādi operatori:

Šis operators paskaidro, vai vienas ģeometrijas ierobežojošais lodziņš krustojas ar citas ģeometrisko lodziņu.

Šie operatori pārbauda, ​​vai divas ģeometrijas ir ģeometriski identiskas. Piemēram, ja 'POLYGON ((0 0,1 1,1 0,0 0))' ir tas pats, kas 'POLYGON ((0 0,1 1,1 0,0 0))' (tas ir).

Šis operators ir nedaudz naivāks, pārbauda tikai to, vai ģeometrijai piesaistošās rūtiņas ir vienādas.

Pēc tam vaicājumos varat izmantot šos operatorus. Ņemiet vērā, ka, norādot ģeometrijas un lodziņus SQL komandrindā, virkņu attēlojumi ir skaidri jāpārvērš ģeometrijās, izmantojot funkciju "GeomFromText ()". Piemēram, piemēram:

Iepriekš minētais vaicājums atgriezīs vienu ierakstu no tabulas "ROADS_GEOM", kurā ģeometrija bija vienāda ar šo vērtību.

Izmantojot "& amp & amp" operatoru, kā salīdzināšanas funkciju varat norādīt vai nu BOX3D, vai arī GEOMETRY. Norādot GEOMETRY, salīdzinājumam tiks izmantota tā norobežojošā lodziņa.

Iepriekš minētais vaicājums salīdzināšanas nolūkos izmantos daudzstūra ierobežojošo lodziņu.

Visizplatītākais telpiskais vaicājums, iespējams, būs vaicājums "balstīts uz rāmjiem", kuru klienta programmatūra, piemēram, datu pārlūkprogrammas un tīmekļa kartētāji, izmanto, lai satvertu "kartes rāmja" vērtībā parādāmus datus. Izmantojot rāmim objektu "BOX3D", šāds vaicājums izskatās šādi:

Ievērojiet SRID lietojumu, lai norādītu BOX3D projekciju. Vērtība -1 tiek izmantota, lai norādītu, ka nav norādīts SRID.

4.4.2. Pašizgāzēja izmantošana

Tabulas pašizgāzējs pgsql2shp izveido tiešu savienojumu ar datu bāzi un tabulu (iespējams, definētu ar vaicājumu) pārveido par formas failu. Pamata sintakse ir:

Komandrindas opcijas ir:

Uzrakstiet izvadi uz noteiktu faila nosaukumu.

Datu bāzes resursdators, ar kuru izveidot savienojumu.

Ports, ar kuru izveidot savienojumu datu bāzes resursdatorā.

Parole, kas jāizmanto, izveidojot savienojumu ar datu bāzi.

Lietotājvārds, kas jāizmanto, izveidojot savienojumu ar datu bāzi.

Galdiem ar vairākām ģeometrijas kolonnām - ģeometrijas kolonna, kas jāizmanto, rakstot formas failu.

Izmantojiet bināro kursoru. Tas padarīs operāciju ātrāku, bet nedarbosies, ja tabulā kādam NON-geometry atribūtam trūkst teksta.

Neapstrādāts režīms. Nenometiet gid lauku un neaizbēgiet no kolonnu nosaukumiem.

Lai nodrošinātu savietojamību ar atpakaļejošu datumu: uzrakstiet trīsdimensiju formas failu, kad tiek izgāzts no vecajām (pirms 1.0.0) Postgis datu bāzēm (tādā gadījumā noklusējums ir rakstīt divdimensiju formas failu). Sākot ar postgis-1.0.0 +, izmēri ir pilnībā kodēti.


Pagrieziens

Jūs vēlaties, lai rezultāts būtu pagriezts ar vienu kolonnu katrā atlasītajā grupas_id. Izmantot krustkarte () no papildu tablefunc moduļa, lai iegūtu ātrākus rezultātus. Ja jums tas nav pazīstams, vispirms izlasiet šeit sniegtos pamatnorādījumus:

Trūkst elementu NULL. Jūs to nevarat uztvert, izmantojot iekšējo vaicājumu COALESCE ap count (). Tā vietā ārējā vaicājumā izmantojiet COALESCE. Patīk:

Ņemiet vērā, ka rezultātā joprojām trūkst stundu bez rindām. Izmantojiet OUTER JOIN, lai izveidotu pilnu laika režģi, ja vēlaties, lai rezultātā būtu tukšas rindas. Patīk:


Kolonnu secība saliktā indeksā PostgreSQL (un vaicājumu secība)

Man ir galds ar 50K rindām. Tā faktiski ir PostGIS tabula.

Vaicājumam ir 4 daļas (1 obligāta) (3 pēc izvēles)

  1. krustojuma lodziņš (ģeogrāfiskais taisnstūris) ar 4 latiem garu (es izmantoju st_intersects) [Obligāti]
  2. Datumu diapazons (min, max) datuma laukā
  3. Faila tips (līdz 8 teksta vērtību kopa) pašlaik tiek izmantots IN (.), Bet, ja nepieciešams, es varu izveidot šo temp tabulu. Es redzu, ka daudziem cilvēkiem IN nepatīk.
  4. Valsts (teksta vērtība).

Es sagaidu apmēram 100 - 4000 atgrieztas rindas

Ja tabulā izveidoju salikto indeksu, kura kolonna vispirms jāizmanto. Smalkgraudains, iespējams, ir tā atrašanās vieta (dati ir izplatīti visā pasaulē). Pašlaik man tas ir kā GIST indekss.

Pārējie indeksi būtu BTREE.

Mana intuīcija saka, ka izmantojiet smalkgraudainu, un kurss ir pēdējais. Piem., Ir tikai aptuveni 12 failu tipi, tāpēc tie būtu ļoti lieli indeksa segmenti.

Ko saka PostgreSQL un PostGIS guru (kas zina sistēmas iekšējo daļu)?

Ļaujiet man saasināt šo jautājumu.

  1. Es negribu, lai kādam būtu jādara darbs, kas man jādara. Es pārāk cienu jūsu laiku. Tāpēc es ķeršos pie paskaidrojuma analīzes vēlāk.
  2. Es meklēju tikai dažus norādījumus, padomus un vadlīnijas.
  3. Es izlasīju šo lielisko mazo norīkošanu: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes par rādītājiem
  4. Parasti es izveidoju 4 atsevišķus indeksus (ģeogrāfisko lodziņu, valsts nosaukumu, faila tipu un datumu), bet vēlos redzēt, ko darīs saliktais vaicājums.

Pastāsti man, vai kāds no šiem pieņēmumiem ir nepareizs. (Salikto indeksu ideja man ir diezgan jauna)

  1. Kārtība ir svarīga. Kā pirmo indeksu izvēlieties to, kurš visvairāk samazinās rindas uz leju (manā gadījumā vislabāk derētu atrašanās vieta (ģeogrāfija), kas ir vienkāršs daudzstūris vai daudzstūris).
  2. Dažreiz vaicājumi izlaiž rādītājus. Bet, ja es izveidoju saliktu vaicājumu ar atslēgu (# 1, # 2, # 3, # 4), pat tad, ja lietotājs izveido kaut ko, kas prasa # 1, # 3, plānotājs joprojām izmantos vienu salikto vaicājumu, jo viņi pasūta tiek uzturēts.
  3. Parasti es izveidotu trīs BTREE vaicājumus un vienu GIST (ģeogrāfijas tipam). PostGIS neatbalsta savienojuma izveidošanu no vairākiem indeksu veidiem. Tāpēc man būs jāizmanto GIST saliktais indekss. Bet tam nevajadzētu kaitēt lietām.
  4. Ja es tomēr izveidoju dažus papildu saliktos vai vienas vērtības indeksus, plānotājs ir pietiekami gudrs, lai izvēlētos inteliģentāko.
  5. Valsts nosaukumam var būt aptuveni 250 dažādas vērtības, un tas, protams, ir cieši saistīts ar atrašanās vietu (ģeokasti), bet, ja nākamais labākais indekss rindas lieluma samazināšanai ir file_type, man tas jāizmanto nākamais. Es nedomāju, ka lietotāji vaicājumu kopās bieži izmanto valsti vai datumu.
  6. Man NAV jāuztraucas par 4 taustiņu saliktā indeksa izveidi, tas ievērojami palielinās indeksa datu lielumu. T.i. ja vienas atslēgas indekss būtu 90% no veiktspējas palielināšanas, tas nekaitē pievienot vēl 3 vienumus, lai tas būtu salikts. Un otrādi, man patiešām vajadzētu izveidot abus indeksus. Viens ģeogrāfijas indekss, kā arī salikts indekss, un ļauj plānotājam izdomāt, kurš ir labākais, un tas ņems vērā indeksa tabulas lielumu.

Es atkal nevienu neprasu, lai viņš izstrādā manu risinājumu, es nemīlu citu darbu. Bet man ir nepieciešami sīkumi, kurus PostGreSQL dokumentācija man nepasaka par ieviešanu

[Iemesls, kāpēc man vēl nav PARĀDĪJUMA, ko parādīt, ir tāds, ka man šī 25K rindu tabula ir jāizveido no 24M rindu tabulas. Tas prasa vairāk laika, nekā es domāju. Es sagrupēju lietas 1000 vienumu grupās un ļauju lietotāja vaicājumiem salīdzināt 25K rindu tabulu. Bet mans nākamais jautājums būs saistīts ar šī vaicājuma rezultātu izmantošanu, lai pārietu uz MASTER 25M rindu tabulu un izvilktu lietas, un tieši šeit saliktā indeksa veiktspēja patiešām HIT].

PASKAIDROT ANALĪZES rezultātus (es neievietoju nevienu salikto indeksu, un, ņemot vērā ātrumu, es nezinu, vai man tas ir nepieciešams).


4.7. Izmantojot Mapserver

SVN pārskatīšana ( 8935 )

Minesotas Mapserver ir interneta tīmekļa kartēšanas serveris, kas atbilst OpenGIS Web Mapping Server specifikācijai.

4.7.1. Pamata lietojums

SVN pārskatīšana ( 8935 )

Lai izmantotu PostGIS kopā ar Mapserver, jums būs jāzina, kā konfigurēt Mapserver, kas ir ārpus šīs dokumentācijas darbības jomas. Šajā sadaļā tiks aplūkoti specifiski PostGIS jautājumi un konfigurācijas informācija.

Lai izmantotu PostGIS kopā ar Mapserver, jums būs nepieciešams:

PostGIS versija 0.6 vai jaunāka.

Mapserver versija 3.5 vai jaunāka.

Mapserver piekļūst PostGIS / PostgreSQL datiem tāpat kā jebkuram citam PostgreSQL klientam - izmantojot libpq. Tas nozīmē, ka Mapserver var tikt instalēts jebkurā datorā ar tīkla piekļuvi PostGIS serverim, ja vien sistēmā ir libpq PostgreSQL klienta bibliotēkas.

Apkopojiet un instalējiet Mapserver ar visām vēlamajām opcijām, ieskaitot konfigurācijas opciju "- ar-ar-postgis".

Mapserver kartes failā pievienojiet PostGIS slāni. Piemēram:

Iepriekš minētajā piemērā PostGIS specifiskās direktīvas ir šādas:

PostGIS slāņiem tas vienmēr ir "postgis".

Datu bāzes savienojumu regulē “savienojuma virkne”, kas ir standarta šādu atslēgu un vērtību kopa (ar noklusējuma vērtībām & lt & gt):

user = & ltusername & gt password = & ltparole & gt dbname = & ltusername & gt resursdatora nosaukums = & ltserver & gt ports = & lt5432 & gt

Tukša savienojuma virkne joprojām ir derīga, un jebkuru no atslēgu / vērtību pāriem var izlaist. Vismaz jūs parasti norādīsit datubāzes nosaukumu un lietotājvārdu, ar kuru sazināties.

Šī parametra forma ir "& ltcolumn & gt from & lttablename & gt", kur kolonna ir telpiskā kolonna, kas jānorāda kartē.

Filtram jābūt derīgai SQL virknei, kas atbilst loģikai, kas SQL vaicājumā parasti seko atslēgvārdam WHERE. Tā, piemēram, lai renderētu tikai ceļus ar 6 vai vairāk joslām, izmantojiet filtru “num_lanes & gt = 6”.

Telpiskajā datu bāzē pārliecinieties, vai ir izveidoti telpiskie (GiST) indeksi visiem slāņiem, kurus jūs zīmēsit.

Ja vaicāsit par slāņiem, izmantojot Mapserver, jums būs nepieciešams arī "oid indekss".

Mapserver pieprasa unikālus identifikatorus katram telpiskajam ierakstam, veicot vaicājumus, un Mapserver modulis PostGIS šo unikālo identifikatoru nodrošināšanai izmanto PostgreSQL oid vērtību. Blakusparādība ir tā, ka, lai vaicājumu laikā veiktu ātru izlases piekļuvi ierakstiem, ir nepieciešams indekss par oid.

Lai izveidotu "oid indeksu", izmantojiet šo SQL:

4.7.2. Bieži uzdotie jautājumi

SVN pārskatīšana ( 8935 )

Kad kartes failā izmantoju EXPRESSION, nosacījums nekad neatgriežas kā patiess, lai gan es zinu, ka tabulā pastāv vērtības.

Atšķirībā no formas failiem, uz PostGIS lauku nosaukumiem ir jāatsaucas EXPRESSIONS, izmantojot mazie burti .

FILTRS, kuru izmantoju saviem formas failiem, nedarbojas PostGIS tabulā ar tiem pašiem datiem.

Atšķirībā no formas failiem, PostGIS slāņu filtros tiek izmantota SQL sintakse (tie tiek pievienoti SQL priekšrakstam, ko PostGIS savienotājs ģenerē slāņu zīmēšanai Mapserver).

Mans PostGIS slānis velk daudz lēnāk nekā mans Shape failu slānis, vai tas ir normāli?

Parasti sagaidiet, ka PostGIS slāņi būs par 10% lēnāki nekā līdzvērtīgi Shape files slāņi, pateicoties papildu pieskaitāmajām izmaksām, kas saistītas ar datu bāzes savienojumiem, datu pārveidošanu un datu tranzītu starp datu bāzi un Mapserver.

Ja atrodat būtiskas problēmas ar zīmēšanas veiktspēju, iespējams, ka uz galda neesat izveidojis telpisko indeksu.

Mans PostGIS slānis ir lieliski izstrādāts, bet vaicājumi ir patiešām lēni. Kas vainas?

Lai vaicājumi būtu ātri, jums ir jābūt unikālai telpiskās tabulas atslēgai un šīs unikālās atslēgas indeksam.

Jūs varat norādīt, kādu unikālu atslēgu kartes serverim izmantot kopā ar klauzulu USING UNIQUE savā DATA rindā:

Ja jūsu tabulā nav precīzi unikālas kolonnas, varat "viltot" unikālu kolonnu, unikālajai kolonnai izmantojot PostgreSQL rindu "oid". "oid" ir noklusējuma unikālā kolonna, ja jūs to nedeklarējat, tāpēc vaicājuma ātruma palielināšana ir indeksa izveidošana jūsu telpiskās tabulas oid vērtībā.

4.7.3. Uzlabota lietošana

SVN pārskatīšana ( 8935 )

Pseido-SQL klauzula LIETOŠANA tiek izmantota, lai pievienotu kādu informāciju, lai palīdzētu karšu serverim saprast sarežģītāku vaicājumu rezultātus. Konkrētāk, ja skata vai apakšizvēlne tiek izmantota kā avota tabula (DATA definīcijā pa labi no “FROM”), karšu serverim ir grūtāk automātiski noteikt katras rindas unikālo identifikatoru un arī SRID par galdu. Klauzula LIETOŠANA var nodrošināt karšu serveri ar šīm divām informācijas daļām šādi:

Mapserver prasa katras rindas unikālu ID, lai identificētu rindu, veicot karšu vaicājumus. Parasti tas izmantotu oid kā unikālo identifikatoru, taču skatiem un apakšizlasēm automātiski nav oid kolonnas. Ja vēlaties izmantot Mapserver vaicājumu funkcionalitāti, skatam vai apakšizlasei jāpievieno unikāla kolonna un jāpaziņo tā, izmantojot USING UNIQUE. Piemēram, šim nolūkam varat skaidri atlasīt vienu no tabulas oid vērtībām vai jebkuru citu kolonnu, kas tiek garantēta kā unikāla rezultātu kopai.

Izraksts USING var būt noderīgs pat vienkāršiem DATA paziņojumiem, ja veicat karšu vaicājumus. Iepriekš, lai paātrinātu karšu vaicājumu darbību, tika ieteikts pievienot indeksu tabulu kolonnā oid, kuras tiek izmantotas vaicājumos spējīgos slāņos. Tomēr ar klauzulu USING ir iespējams pateikt serverim, lai kartes vaicājumu identifikators izmantotu jūsu tabulas galveno atslēgu, un pēc tam vairs nav nepieciešams papildu indekss.

"Kartes pieprasīšana" ir darbība, noklikšķinot uz kartes, lai pieprasītu informāciju par kartes funkcijām šajā atrašanās vietā. Nejauciet "karšu vaicājumus" ar SQL vaicājumu DATA definīcijā.

PostGIS ir jāzina, kuru telpisko atsauču sistēmu ģeometrijas izmanto, lai pareizos datus atgrieztu karšu serverī. Parasti šo informāciju ir iespējams atrast PostGIS datu bāzes tabulā "geometry_columns", taču tas nav iespējams tabulām, kuras tiek veidotas lidojumā, piemēram, apakšizlasēm un skatiem. Tātad opcija USING SRID = ļauj DATA definīcijā norādīt pareizo SRID.

Brīdinājums

Mapserver PostGIS slāņu parsētājs ir diezgan primitīvs un dažās jomās ir reģistrjutīgs. Esiet piesardzīgs, lai pārliecinātos, vai visi SQL atslēgvārdi un visas LIETOŠANAS klauzulas ir rakstītas ar lielajiem burtiem un vai klauzula USING UNIQUE ir pirms jūsu LIETOŠANAS SRID klauzulas.


4 Atbildes 4

Vairumā gadījumu indeksa kārtošanas kārtība ir mazsvarīga. Postgres var skenēt atpakaļ tikpat ātri, cik ātri. Bet diapazona vaicājumiem vairākās kolonnās tas var izveidot a milzīgs atšķirība. Cieši saistīta:

Indeksa pirmās kolonnas id_phi kārtošanas secība nav būtiska. Tā kā tas ir pārbaudīts vienlīdzība (=), tam vajadzētu būt pirmajam. Jums ir taisnība. Vairāk šajā saistītajā atbildē:

Postgres var pāriet uz id_phi = 0 pēc laika, un ņemiet vērā nākamās divas atbilstošā indeksa kolonnas. Tie tiek apšaubīti diapazona apgrieztās kārtības nosacījumi (& lt =, & gt =). Manā indeksā kvalifikācijas rindas ir pirmās. Tam vajadzētu būt ātrākajam iespējamajam veidam ar B-Tree indeksu 1 :

  • Jūs vēlaties start_date_time & lt = something: indeksam vispirms ir agrākais laikspiedols.
  • Ja tas atbilst, pārbaudiet arī 3. sleju.
    Recurse, līdz pirmā rinda nespēj kvalificēties (ļoti ātri).
  • Jūs vēlaties end_date_time & gt = something: indeksam vispirms ir jaunākais laikspiedols.
  • Ja tas atbilst, turpiniet ienest rindas, līdz pirmā nedarbojas (ļoti ātri).
    Turpiniet ar 2. slejas nākamo vērtību.

Postgres var vai nu skenēt uz priekšu vai atpalicis. Kā jums bija indekss, tas ir jālasa visi rindas, kas atbilst pirmajām divām kolonnām un pēc tam filtru trešajā. Noteikti izlasiet nodaļu Indeksi un PASŪTĪJUMS rokasgrāmatā. Tas diezgan labi atbilst jūsu jautājumam.

Cik rindu sakrīt pirmajās divās kolonnās?
Tikai daži, kuru sākuma_datuma_laiks ir tuvu tabulas laika diapazona sākumam. Bet gandrīz visi rindas ar id_phi = 0 tabulas hronoloģiskajā galā! Tātad sniegums pasliktinās līdz ar vēlāku sākuma laiku.

Plānotāja aplēses

Plānotājs aprēķina rindas = 62682 jūsu vaicājuma piemēram. No tiem neviens nevar pretendēt (rindas = 0). Jūs varētu iegūt labākus aprēķinus, ja palielināsiet tabulas statistikas mērķi. 2.000.000 rindām.

. varētu maksāt. Vai pat augstāk. Vairāk šajā saistītajā atbildē:

Es domāju, ka tas jums nav vajadzīgs id_phi (tikai dažas atšķirīgas vērtības, vienmērīgi sadalītas), bet laika zīmogiem (daudz atšķirīgu vērtību, nevienmērīgi sadalītas).
Es arī nedomāju, ka ar uzlaboto indeksu tam ir liela nozīme.

CLUSTER / pg_repack / pg_squeeze

Ja vēlaties to ātrāk, tomēr varat pilnveidot tabulas rindu fizisko secību. Ja jūs varat atļauties bloķēt savu galdu tikai (piemēram, ārpus darba laika), pārrakstiet savu galdu un kārtojiet rindas atbilstoši indeksam, izmantojot CLUSTER:

Vai arī apsveriet pg_repack vai vēlāku pg_squeeze, kas to var izdarīt bez ekskluzīvas bloķēšanas uz galda.

Katrā ziņā efekts ir tāds, ka no tabulas ir jāizlasa mazāk bloku un viss ir iepriekš sakārtots. Tas ir vienreizējs efekts, kas laika gaitā pasliktinās, rakstot uz galda, sadrumstalojot fizisko kārtošanas kārtību.


Darbs ar LiDAR datiem PostgreSQL

Plāksteri ir efektīvs veids, kā saglabāt pointcloud datus PostgreSQL tabulās, kur katrs plāksteris sastāv no vairākiem tuvumā esošiem punktiem. Tas palīdz ievērojami samazināt datu rindu skaitu un paātrina operācijas ar tabulu. Plāksteru un punktu shēma ir aprakstīta datu bāzes tabulā pointcloud_formats. Turklāt pointcloud paplašinājums piedāvā funkcionalitāti darbam gan ar punktiem, gan plāksteriem, ieskaitot atsevišķu atribūtu, piemēram, katra punkta augstumu, intensitātes vai koordinātu iegūšanu, vai plāksteru ierobežojošo lodziņu apjomu. Ja vēlaties uzzināt vairāk, šai Paul Ramsey prezentācijai OpenGEO ir patiešām labi paskaidrojumi ar punktu un ielāpu piemēriem.

Visas turpmākās darbības tiek ieviestas pgpointcloud konteinerā, kurā varat izmantot šādas komandas:

Mēs pievienosim LiDAR datus ar OSM ēkas pēdām, lai iegūtu ēkas augstumus un augstumus. Tātad, vispirms ielādēsim iepriekš aprakstītos OSM shapefile datus tabulā ar nosaukumu osm, izmantojot šādu komandu no pgpointcloud konteinera -

Lielākajā daļā atlikušo darbību mēs izmantosim PostgreSQL vaicājumus. Jūs varat piekļūt datu bāzei, izmantojot šo komandu, un palaist visus vaicājumus no datu bāzes.

Tagad mēs esam gatavi iegūt ēkas augstumus un augstumus, un detalizēts process ir aprakstīts zemāk. Pārskata process, lai iegūtu ēkas augstumus, ir vispirms identificēt visus LiDAR punktus ēkas pēdās no OSM, un pēc tam iegūt ēkas augstumu no šīs punktu kolekcijas. Pārskats, lai iegūtu ēkas augstumus, vispirms ir jānovērtē zemes augstums gar katras ēkas pēdas kontūru un jāatskaita no ēkas augstuma.

  1. Iepriekš apstrādājiet lidar tabulu, lai izveidotu telpisko indeksu.
    a. Skatiet katra plākstera punkta atribūtus. Plāksteru un punktu formāti ir aprakstīti XML formātā atsevišķā tabulā ar nosaukumu pointcloud_formats.
    Atlasiet * FROM pointcloud_formats
    b. Skatīt tabulas kolonnu sarakstu. Jūs ievērosiet, ka plāksteri tiek glabāti kolonnā pa.
    d lidar
    c. Plāksteru skaita un kopējo punktu skatīšana -
    SELECT Count (*), Sum (PC_NumPoints (pa)) NO lidar
    d. Izveidojiet telpisko indeksu lidar tabulā, lai iegūtu ātrāku vaicājumu (novērots 40 reizes ātrāks) -
    CREATE INDEX lidar_envelope_pkey ON lidar USING GIST (PC_EnvelopeGeometry (pa))
  2. Iepriekš apstrādājiet osm tabulu, lai izveidotu telpisko indeksu.
    a. Punktus ārpus pilsētas norobežojošās lodziņa var noņemt, izmantojot zemāk esošo vaicājumu. Nomainiet (lng_min, lat_min, lng_max, lat_max) ar interesējošās norobežojošās rūtiņas apjomu, kur lng attiecas uz garumu un lat uz platumu.
    Dzēst no osm WHERE osm.gid IN (Atlasiet a.gid FROM osm a WHERE NOT ST_Intersects (a.geom, ST_MakeEnvelope (lng_min, lat_min, lng_max, lat_max, 4326))
    b. Es iesaku pārveidot osm ģeomērijas kolonnas geom uz to pašu SRID (telpisko atskaites sistēmu) no lidar tabulas. UTM zonas ir lieliski piemērotas neliela laukuma un mērījumu apstrādei, tāpēc mēs pārveidojam geom par EPSG: 26910, kas ir tā pati atsauce uz LiDAR datiem.
    ALTER TABLE osm ALTER COLUMN ģeom TIPA ģeometrija (MultiPolygon, 26910), IZMANTOJOT ST_Transform (geom, 26910)
    CREATE INDEX osm_26910_pkey ON OSM, IZMANTOJOT GIST (geom)
  3. Izveidojiet jaunu kolonnu osm, lai saturētu punktu plāksterus, kas atrodas katrā ēkas pēdā.
    a. Lai pievienotu jaunu kolonnu osm, lai saglabātu plāksterus, vispirms pārbaudiet plāksteru veidu lidar, izmantojot d lidar. Parasti tas būtu pcpatch (1). Pievienojiet jaunu kolonnu osm, lai saglabātu ielāpus
    ALTER TABLE osm ADD COLUMN pa pcpatch (1)
    b. Uzglabājiet pointcloud plāksteri, kas pārklāj katru ēkas pēdu daudzstūri. Tālāk esošajā vaicājumā vispirms tiek identificēti visi plāksteri, kas krustojas ar katru ēkas nospiedumu, pēc tam aprēķina to savienojumu un visbeidzot atrod savienojuma plākstera krustojumu ar pēdu. Paredzams, ka šis vaicājums aizņems dažas stundas.
    UPDATE osm SET pa = sq.pa FROM (AR ielāpiem AS (SELECT o.gid AS gid, o.geom AS geom, l.pa AS pa NO lidar AS l PIEVIENOT osm AS o PC_INTERSECTS (l.pa, o.geom )) SELECT gid, PC_INTERSECTION (PC_UNION (pa), geom) AS pa FROM ielāpi GROUP BY gid, geom) AS sq WHERE osm.gid = sq.gid
  4. Mēs aprakstīsim ēkas augstumu kā statistiku visu ēkas augstuma punktu augstumam. Statistiku var izvēlēties, pamatojoties uz jūsu lietošanas gadījumu. Šeit es aprēķinu maksimālo, vidējo un vidējo augstumu no visiem punktiem. Piezīme nekā Z-vērtība LiDAR datos ir augstums, kas piešķirts noteiktam punktam, kas norādīts tā metadatos (šajā gadījumā NAD83). Tā vietā, lai tieši ņemtu maksimumus, tā tiek lēsta kā 99,9. procentiles vērtība, lai samazinātu iespējamo iznākumu, kā rezultātā tiek pārvērtēts augstākais augstums.
    a. Pievienojiet atbilstošās kolonnas osm -
    MAINĪT TABULU osm PIEVIENOT SLEJU z_avg DUBULAS PRECIZITĀTES NULL, PIEVIENOT SLEJU z_median DUBULAS PRECIZITĀTES NULL, PIEVIENOT SLEJU z_max DOUBLE PRECISION NULL
    b. Aprēķiniet un saglabājiet statistiku par augstumu
    UPDATE osm SET z_avg = sq.z_avg, z_median = sq.z_median, z_max = sq.z_max FROM (AR plāksteriem AS (SELECT o.gid AS gid, PC_GET (PC_EXPLODE (o.pa), 'Z') AS pt_z NO osm) AS o) SELECT gid, AVG (pt_z) AS z_avg, PERCENTILE_CONT (0.5) GRUPĀ (ORDER BY pt_z) AS z_median, PERCENTILE_CONT (0.999) GROUP (ORDER BY pt_z) AS z_max NO ielāpiem GROUP BY gid) AS .gid = sq.gid
  5. Iegūstiet zemes augstumu, identificējot ēkas kontūru un savienojot to ar LiDAR datiem.
    a. Izveidojiet jaunu tabulu ar nosaukumu osm_outline kā osm tabulas kopiju, lai apstrādātu izveidoto ēkas kontūru, ņemot vērā starpības starp 2m un 1m buferi ap ēkas daudzstūriem.
    CREATE TABLE osm_outline AS SELECT gid, osm_id, geom FROM osm
    UPDATE osm_outline SET geom = buferis FROM (SELECT o.gid, ST_MULTI (ST_DIFFERENCE (ST_MULTI (ST_Buffer (o.geom, 2)), ST_MULTI (ST_BUFFER (o.geom, 1)))) AS buferis FROM osm_outline AS o) AS kv WHERE osm_outline.gid = sq.gid
    RADĪT RĀDĪTĀJU osm_outline_pkey ON OSM_outline, IZMANTOJOT GIST (geom)
    b. Uzglabājiet pointcloud ielāpus, kas krustojas ar kontūru. Tālāk esošajā vaicājumā vispirms tiek identificēti visi plāksteri, kas krustojas ar katru kontūru, pēc tam aprēķina to savienojumu un visbeidzot atrod savienojuma plākstera krustojumu ar kontūru. Paredzams, ka šis vaicājums aizņems dažas stundas.
    MAINĪT TABULU osm_outline PIEVIENOT kolonnu pcpatch (1)
    ATJAUNINĀT osm_outline SET pa = sq.pa FROM (AR ielāpiem AS (SELECT o.gid AS gid, o.geom AS geom, l.pa AS pa FROM lidar AS l PIEVIENOT osm_outline AS o PC_INTERSECTS (l.pa, o.geom )) SELECT gid, PC_INTERSECTION (PC_UNION (pa), geom) AS pa FROM ielāpus GROUP BY gid, geom) AS sq WHERE osm_outline.gid = sq.gid
    c. Uzglabājiet minimālo Z vērtību no pointcloud, kas krustojas ar ēkas kontūrām. Tā vietā, lai tieši ņemtu minimumus, tā tiek lēsta kā 1. procentiles vērtība, lai samazinātu izņēmuma iespējamību, kā rezultātā zemes augstumu nenovērtē.
    MAINĪT TABULU osm_outline PIEVIENOT kolonnu z_ground DIVKĀRTAS PRECIZITĀTES NULL
    UPDATE osm_outline SET z_ground = sq.z_min FROM (AR ielāpiem 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) GRUPĀ (PASŪTĪT Pt_z) AS z_min NO ielāpiem GROUP BY gid) AS sq WHERE osm_outline.gid = sq.gid
    d. Pievienojiet zemes augstumu sākotnējai osm tabulai un aprēķiniet augstumus kā starpību starp pēdas un zemes augstumiem.
    MAINĪT TABULU osm PIEVIENOT SLEJU z_ground DUBULAS PRECIZITĀTES NULL, PIEVIENOT SLEJU
    ATJAUNINĀT osm AS o SET z_ground = oo.z_ground NO OSM_outline oo WHERE o.gid = oo.gid
    UPDATE osm SET augstuma_avg = (z_avg - z_ground), height_median = (z_median - z_ground), height_max = (z_max - z_ground)

Tieši tā! Jūs esat veiksmīgi aprēķinājis ēkas augstumus, zemes augstumus un ēkas augstumus katrai ēkai, un visi šie dati tiek saglabāti osm tabulā kopā ar ēkas pēdu ģeometriju katrai ēkai.

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? Dodieties pie mūsu website or our Medium page to read about some of our recent projects!