Več

Sintaksa PostGIS (funkcija ()).*

Sintaksa PostGIS (funkcija ()).*


Med branjem dokumentacije o uporabi funkcije PostGIS ST_PixelAsCentroids () me je to nekoliko zmedlo(ST_PixelAsPoints (rast, 1)).*sintakso in kot novinec v SQL -u me je zanimalo, če lahko kdo razloži to konvencijo in zakaj se uporablja?

Očitno se uporablja kot metoda za razvrščanje zapletenih rezultatov, vendar niti ne vem, kako se imenuje, da ga pravilno poguglam, da bi to formalno razumel.

Zavedam se, da je ta objava morda bolj primerna za forum SQL na Stacku, vendar bom videl, kaj pričara GIS Stack.


Funkcija vrne tabelo z vnesenimi stolpci (shema) in vrsticami (vrednosti). The.*na koncu pomeni "vsi stolpci iz tabele."SELECT * FROM f (x)bi dala podoben rezultatSELECT (f (x)).*.

Načrtovalec poizvedb lahko v zgornjih primerih zaradi vsakega stolpca enkrat pokliče funkcije z več stolpci za PostgreSQL. Zato je priporočljivo uporabiti skupni izraz tabel (CTE) ali podpoizvedbo, da preprečite to vedenje, na primer napišite:

SELECT (mf).* FROM (SELECT my_func (x) AS mf FROM some_table OFFSET 0) pod;

ali v PostgreSQL 9.3

SELECT mf.* FROM some_table LEFT JOIN LATERAL my_func (some_table.x) AS mf ON true;

To je bolj vprašanje PostgreSQL in ne vprašanje PostGIS, vendar je dobro.

Funkcije, kot soST_PixelAsPoints,ST_PixelAsPolygons, inST_PixelAsCentroidivrne niz sestavljenih podatkovnih tipov zapisov, ki imajo več polj. To je zelo podobno ST_Dump, opisanemu tukaj. Za dostop do vseh vrnjenih polj iz teh sestavljenih podatkovnih tipov morajo biti v obliki(funkcija (x)).*, kot je opisano v priročniku PostgreSQL.

Upoštevajte, dafunkcija (x).*ne bo delovalo in je pogosta težava.


Najbližja težava soseda v Postgisu 2.0 z uporabo indeksa GIST (funkcija)

Poskušam uporabiti novo funkcijo Postgis 2.0 & lt- & gt (Geometry Distance Centroid), da za vsako vrstico svoje tabele (cosn1) izračunam razdaljo do najbližjega poligona istega razreda.

Poskušal sem uporabiti naslednjo kodo:

Potem pa spoznam opozorilo:

Opomba: Indeks se zažene le, če je ena od geometrij konstanta (ne v podpoizvedbi/cte). npr. ‘SRID = 3005POINT (1011102 450541) ’ :: geometrija namesto a.geom

To pomeni, da indeksa sploh ne bomo uporabili, poizvedba pa bo trajala skoraj enako dolgo kot pred uporabo:

Mi lahko kdo nakaže rešitev, ki mi omogoča izboljšanje uspešnosti moje poizvedbe?

En odgovor

Nekateri testi na mojem stroju so pokazali, da ta operater & lt- & gt ne deluje pravilno. Nisem prepričan, da je to napaka, vendar je poročal o ničelni razdalji pri geometrijo, ki se ne prekriva.

Preizkusil sem poštene tradicionalne optimizacije poizvedb SQL. Ker so ti nepričakovani rezultati z & lt- & gt operaterjem zamenjali z st_centroid. Hitrost je dosegla veliko boljše rezultate.

Upam, da bo semantika s st_overlaps ostala enaka. Vsaj tako sem razumel iz dokumentacije o & lt- & gt

Za druge vrste geometrije se vrne razdalja med centroidi omejevalne škatle s plavajočo vejico.

5.5k poligoni so pospešili

5 sekund brez prostorskega indeksiranja.

Vidim, da nekateri ljudje uporabljajo DISTINCT ON za združevanje, ne pa skupina by obstaja, da bi odpravili podvojene podatke.

Vaša poizvedba s standardnimi optimizacijami SQL brez uvedene napake st_centroid


4.5. Linije

A vrstica je pot med lokacijami. Ima obliko urejenega niza dveh ali več točk. Ceste in reke so običajno predstavljene kot žice. Vrstica naj bi bila zaprto če se začne in konča na isti točki. Rečeno je, da je preprosto če se ne križa ali dotika samega sebe (razen na svojih končnih točkah, če je zaprt). Niz vrstic je lahko oboje zaprto in preprosto.

Ulično omrežje za New York (nyc_streets) je bilo naloženo prej v delavnici. Ta niz podatkov vsebuje podrobnosti, kot sta ime in vrsta. Ena ulica v resničnem svetu je lahko sestavljena iz številnih nizov, od katerih vsaka predstavlja segment ceste z različnimi lastnostmi.

Naslednja poizvedba SQL bo vrnila geometrijo, povezano z enim nizom vrstic (v stolpcu ST_AsText).

Nekatere posebne prostorske funkcije za delo z vrsticami so:

ST_Length (geometry) vrne dolžino vrstice

ST_StartPoint (geometrija) vrne prvo koordinato kot točko

ST_EndPoint (geometrija) vrne zadnjo koordinato kot točko

ST_NPoints (geometrija) vrne število koordinat v nizu vrstic

Torej je dolžina našega niza:


Spatial Pridružite se s PostGIS

Ogledali smo si nabor podatkov in preverili CRS nabora podatkov. Zdaj je vse nastavljeno. Za združitev obeh naborov podatkov lahko uporabimo različne prostorske relacije, vključno s ST_Within, ST_Contains, ST_Covers ali ST_Crosses. V tem primeru uporabljamo ST_Within, da ugotovimo, katera točka je znotraj katerega poligona.

Rezultat je ta tabela s seznami Airbnb z dodatnim stolpcem, ki označuje, kateri statistični področni kodi pripada vsaka točka, kot je prikazano v spodnji tabeli.

Super! Prostorska povezava bi lahko bila vaš končni rezultat, če bi šele ugotovili, kam pripada vsaka točka. Morda pa bomo morali povzeti rezultat prostorskega združevanja, če želimo izvedeti vpogled v porazdelitev nabora podatkov.


Seznanimo se z vmesnikom PgAdmin GUI, ki ga bomo uporabili v tej vadnici. Naslednji GIF prikazuje vmesnik. Ustvarjamo tudi razširitev PostGIS, ki prinaša prostorske funkcije, ki jih bomo uporabili kasneje.

Lahko preprosto ustvarimo razširitev PostGIS z naslednjim ukazom, ki je izveden v orodju PgAdmin Query.

Po zagonu tega ukaza imamo na voljo veliko novih prostorskih funkcij.

Ustvarimo tudi bazo podatkov z uporabo PgAdmin4. Naslednja navodila GIF vam pokažejo, kako ustvariti novo bazo podatkov.


Poligoni

Poligoni so zgrajeni iz zaprtih nizov LineStrings. Poligon vsebuje vse zaprto območje in njegovo mejo, medtem ko se zaprti niz v meji imenuje zunanji obroč

V naslednjem primeru izračunamo površino vsake stavbe s funkcijo ST_Area (geom) v PostGIS.

Tu je nekaj poudarkov poligonskih funkcij v PostGIS:

    , ST_Polygon, ST_PolygonFromText → Za ustvarjanje geometrije poligona. → Vrne vrstico, ki predstavlja zunanji obroč geometrije POLYGON. Vrnite NULL, če geometrija ni poligon. Ne bo delovalo z MULTIPOLYGON-Če je geometrija poligon ali več poligon vrne število obročev. - Vrne true, če je geometrija A popolnoma znotraj geometrije B.

Vaši prvi koraki z geografsko podatkovno vrsto

Geografske aplikacije so povsod: sistemi GPS in satelitske navigacije, zemljevidi, aplikacije za taksi, nepremičninski portali itd. Za vsako od njih je prostorska baza podatkov, ki shranjuje geografske podatke in podpira prostorske poizvedbe. V tem članku bomo predstavili PostGIS, glavnega odprtokodnega upravitelja prostorske baze podatkov.

PostGIS je razširitev prostorske baze podatkov za relacijsko bazo podatkov PostgreSQL. Dodaja podporo za geografske objekte, kar omogoča izvajanje poizvedb o lokaciji v SQL.

PostGIS doda PostgreSQL dve glavni vrsti podatkov: geografijo in geometrijo. Oboje omogoča shranjevanje točk v tabeli, pa tudi druge bolj zapletene oblike, kot so črte (črta je definirana z dvema točkama), večtočkovne črte (definirane z N točkami), poligoni (definirani z zaprto večtočkovno črto) in točke z določeno višino (določeno s tretjo koordinato). Ta razširjevalnik med drugim ponuja tudi nabor prostorskih funkcij za izračun razdalje, izračun površine, presečišče in vključitev. Vse te nove vrste podatkov in funkcije je mogoče uporabiti v kombinaciji z običajnimi relacijskimi podatki v SQL, kar poveča moč poizvedb.

V tem članku bomo obravnavali prve korake uporabe PostGIS z raziskovanjem funkcij, kot so geografski podatkovni tip in nekatere prostorske funkcije, kot so razdalja, območje in križišče. Za ponazoritev bomo vključili nekaj primerov SQL.

Začnimo z razlago osnovnega gradnika v prostorski bazi podatkov: par koordinat, ki predstavljajo eno samo točko na zemeljski površini.

The GEOGRAFIJA Vrsta podatkov

Prva značilnost PostGIS -a, ki se ga bomo naučili, je geografski tip podatkov. Začeli bomo z njegovo uporabo za predstavitev točke, določene z dvema koordinatama: zemljepisno širino in dolžino. Kot primer v tem članku bomo uporabili tabelo umetniško delo , ki hrani seznam najpomembnejših umetniških del na svetu in kje so razstavljena. Ta tabela bo imela naslednjo shemo:

Najprej bomo vnesli nekaj relacijskih podatkov za nekaj umetniških mojstrovin. Upoštevajte, da sta polja zemljepisne širine in dolžine še vedno plavajoče številke in se shranjujejo kot redni relacijski podatki, ne pa prostorski.

Če želimo zapolniti stolpec where_is, moramo poklicati PostGIS ST_POINT funkcijo. To vrne a geografijo podatkovne točke, kot vidimo v naslednji izjavi SQL:

Po tej posodobitvi imamo prvega geografijo podatkovne točke v bazi podatkov. Če poskušamo videti njegove vrednosti z SELECT, bomo videli samo šestnajstiške vrednosti:

Tukaj moramo pojasniti dve stvari. Prvi je, da ko smo uporabljali geografijo podatkovni tip, kategorijo smo izpustili. PostGIS je domneval, da mislimo na privzeto kategorijo, to je POINT. Vendar - in kot smo že omenili - lahko z uporabo geografijo podatkovni tip.

Druga postavka se nanaša na vrednosti v stolpcu where_is. Te vrednosti so notranje shranjene v PostGIS v obliki WKB (dobro znana binarna oblika) in ne moremo zlahka razumeti, kaj predstavljajo v tej obliki. Za razlago njihovega pomena potrebujemo nekaj programske opreme, na primer. Če uporabljamo QGIS, lahko na zemljevidu prikažemo štiri geografske točke. Oglejte si rezultate na naslednjem posnetku zaslona:

Dobro znano besedilo (WKT)

Prejšnji odstavek odpira vrata osrednjemu konceptu v standardu prostorske baze podatkov. Vsak prostorski element (na primer točko, črto ali poligon) lahko izrazimo ali definiramo z dvema različnima oblikama:

Tu si vzemimo minuto in se pogovorimo o formatu dobro znanega besedila. Kot smo videli v prejšnjem primeru, je vsak geografski element v PostGIS -u interno predstavljen kot niz šestnajstiških številk v formatu WKB. Očitno je to ljudem res težko razumeti. WKT je jasnejši in človeku prijazen način predstavljanja katerega koli geometrija/geografija element. Nekaj ​​primerov WKT je:

Poleg tega se imenuje funkcija ST_AsText () lahko dobite WKT katerega koli geometrije ali geografijo prostorski element, kot lahko vidimo v naslednjem primeru:

Obstaja tudi funkcija, imenovana ST_GeoFromText () . To je obratna funkcija glede na format WKT in vrača povezano geografijo.

Najpreprostejša prostorska funkcija: razdalja

V tem članku obravnavamo samo geografske vrste podatkov, ki temeljijo na zemljepisni širini in dolžini. Vendar nam PostGIS omogoča predstavitev in shranjevanje drugih prostorskih elementov (na primer geografske točke na podlagi zemljepisne širine, dolžine in nadmorske višine) in geometrije podatkovni tip (ki omogoča preprost prikaz prostorskih točk dveh ali treh koordinat).

Osnova za PostGIS geometrije podatkovni tip je ravnina. Najkrajša pot med dvema točkama na ravnini je ravna črta. To pomeni, da je mogoče izračune za geometrije (površine, razdalje, dolžine, križišča itd.) Izvesti z uporabo kartezijanske matematike in ravnih vektorjev.

Po drugi strani pa osnova za PostGIS geografijo podatkovni tip je krogla. Najkrajša pot med dvema točkama krogle je velik krožni lok. To pomeni, da morajo izračuni geografij (površin, razdalj, dolžin, križišč itd.) Temeljiti na krogli z uporabo bolj zapletene matematike. Poleg tega zemlja ni popolna krogla, zato je treba pri izračunih svet obravnavati kot sferoidno obliko. Preveč zapleteno! Nadaljujmo z uporabo PostGIS.

Vrnimo se k razdalja funkcijo za geografske točke. Naslednja poizvedba SQL izračuna razdaljo med našimi štirimi mojstrovinami in prikaže vse možne kombinacije.

Po definiciji so rezultati razdalje v PostGIS izraženi kot metri. Vsakdo, ki raje vidi rezultate v stopalih, naj pretvori iz metrov v stope z uporabo naslednjega preprostega matematičnega izraza:

Ne shranjujemo samo točk: črt, poligonov, več točk

The geografijo data type ima modifikator, ki določa, kakšen element bomo shranili v stolpec. V našem prvem primeru nismo podali modifikatorja, zato smo privzeto shranili POINT. Lahko pa shranimo LINIJE, POLIGONE in celo zapletene prostorske oblike, kot je MULTIPOINTS (niz točk). Na primer, če želimo shraniti poligon, ki predstavlja obod muzeja, lahko ustvarimo naslednjo tabelo:

Za ustvarjanje poligonske vrednosti bomo uporabili funkcijo PostGIS ST_GeoFromText. Kot smo že omenili, to vrne vrednost geometrije, ko dobi predstavitev WKT. WKT za poligon ima naslednjo obliko:

Upoštevajte, da sta prva in zadnja točka enaki za opredelitev poligona, obvezno je uporabiti zaprto večvrstično črto. Naslednji vložki SQL opredeljujejo muzejske zapise z obodom:

Opomba: Območja niso strogo resnična, vendar vključujejo muzejsko območje.

Izračun površine kompleksnih oblik

Zdaj, ko imamo tabelo s poligonom, ki določa obod vsakega muzeja, lahko uporabimo datoteko ST_AREA funkcija pridobivanja površine vsakega muzeja. To bomo storili z zelo preprosto poizvedbo.

Zanimivost pri tem je preprostost. Pomislite, kako zapleteno bi bilo izračunati površino nepravilne oblike. Tukaj to počnemo z lepo kratko poizvedbo:

Opomba: Območja muzeja so ocene, zato so vrednosti površin približne.

Kako se spoprijeti s pogoji pridružitve

Če pregledamo shemo muzejske in umetniške mize, ni tujih ključev, ki omogočajo združevanje. Kako lahko umetniško delo povežemo z muzejem, kjer je na ogled? Ali obstaja kakšen prostorski pogoj za združitev obeh tabel? Oglejmo si naslednjo povezavo SQL:

Poskusite sami: vaja za bralca

Prostorske zbirke podatkov imajo sicer bolj zapletene elemente, funkcije in algoritme, vendar so osnovni koncepti, o katerih smo govorili, prisotni za vsakim prostorskim elementom. Eden od elementov, ki tukaj ni zajet, je tretja dimenzija točke. Ne pozabite, da nam prostorske zbirke podatkov omogočajo tudi prikaz nadmorske višine določene točke. Predstavljajmo si muzej, kjer sta dve umetnini v istih zemljepisnih in vzdolžnih koordinatah, vendar v različnih nadstropjih.


je temeljni za učinkovito

Normalizacija je formalizacija postopka oblikovanja baze podatkov v skladu s konceptom normalne oblike.

Obravnava različne načine, na katere lahko iščemo ponavljajoče se vrednosti podatkov v tabeli.

Običajne oblike obstaja več stopenj in vsaka raven zahteva, da je izpolnjena prejšnja raven.

Postopek normalizacije temelji na zbiranju izčrpnega seznama vseh podatkovnih postavk, ki jih je treba hraniti v zbirki podatkov, in oblikovanju z nekaj tabelami & quotsuperset & quot.


Dobro branje

Podatke lahko dobite v oddelku v obliki datoteke ali v drugi obliki geopodatkov. Če želite uporabiti PostGIS, morate te podatke vnesti v našo bazo podatkov Postgres.

QGIS lahko uporabite z zgoraj opisanim upraviteljem baz podatkov. S klikom na »Uvozi plast/datoteko« se odpre okno, v katerem boste morali izbrati datoteko in nekaj možnosti, povezanih z njo.

  • V spustnem meniju izberite shemo, v katero jo pošiljate (v to shemo morate imeti dovoljenja za pisanje)
  • Vnesite želeno ime tabele
  • Izberite možnost »Geometrijski stolpec« in nastavite ime, ki ga želite. Za nas v mestu poimenujemo geometrijske stolpce geom + SRID za koordinatni sistem, v katerem so podatki: geom2249.
  • Prepričajte se, da sta polja »Izvorni SRID« in »Ciljni SRID« točna.
  • Izberite »Pretvori imena polj v male črke«. To je samo v skladu z našo politiko upravljanja v mestu, da vsa imena polj uporabljajo male črke.
  • Izberite »Ustvari prostorski indeks«. Za več informacij glejte razdelek Prostorsko indeksiranje.
  • Ko kliknete »V redu«, boste morda morali osvežiti shemo, da se prikaže nova tabela.

Uporaba obstoječih tabel PostGIS

ArcSDE lahko uspešno uporablja tabele, ki vsebujejo stolpce geometrije PostGIS, ki so jih zunanje ustvarile druge aplikacije, ali uporablja SQL (imenovan tudi tabele drugih proizvajalcev), če tabele izpolnjujejo naslednje predpogoje:

    Vsaka tabela mora biti v lasti uporabnika, ki registrira tabelo.

Registracija tabel tretjih oseb, ki vsebujejo stolpce geometrije PostGIS

Skrbniški ukaz ArcSDE sdelayer –o register registrira tabelo kot razred lastnosti. To pomeni, da je za tabelo v sistemskih tabelah sde_layers, sde_table_registry in sde_geometry_columns dodan zapis. Zapisi se dodajo tudi v sistemsko tabelo sde_column_registry za vsak stolpec v tabeli.

Spodaj je primer registracije tabele, lastnosti, ki vsebujejo geometrije točk (–e p) v prostorskem stolpcu, oblike. Tabela ima celoštevilski stolpec, fid, ki ga je treba uporabiti kot stolpec za edinstveno identifikacijo lastnosti (–C fid, SDE), ki ga vzdržuje SDE.

Kot je navedeno zgoraj, sdelayer samo doda tabelo v sistemske tabele ArcSDE. Če želite uporabljati funkcionalnost zbirke geopodatkov ArcGIS Desktop, na primer topologijo, določanje različic in omrežja, morate tabelo registrirati tudi v bazi gepodatkov. Za več informacij o registraciji tabel glejte Registracija tabel, ki jih bo uporabljal ArcGIS Desktop.

OPOMBA: prostorskih tabel, shranjenih z vrsto geometrije PostGIS, ne morete preimenovati. To je posledica dejstva, da ni funkcije PostGIS za posodobitev imena tabele v tabeli public.geometry_columns.


Poglej si posnetek: QGIS Tutorial: Connect to PostGIS database EN