Merken

Writing better PostGIS queries

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Beta
Erkannte Entitäten
Sprachtranskript
OK I'm humming people on youtube who's posters OK rights or try to be is it i regino pay and I'm 1 of the developers on the push system and also the Project Steering Committee and I'm also co-author of a couple of books with my husband leaves it was over there hinge on the links here on the 1st on the 1st page are links to other sites that we manage which have examples and also links the books that we've written am for this talk I'm going to show a couple of examples of things that I see people commonly do with pushes and how they do them wrong and the better way of doing it the so
for these examples I'm going to use some OpenStreetMap data item which I pulled with this command and I'm also gonna use the extension called H store count how many people are familiar with H star upset after the al explain a little bit is basically a key-value store that allows you to put a random keys and associated values with that in a single column so it's kind of like the scheme was designed type cont the and so those slides so after you load on some data you end up with 4 tables that look as presented here you end up with a point line polygon from roads and organ focus mostly on the point 1 but the the lessons here apply to if you're dealing with polygons online shrinks and other things the so the 1st thing that people do when they found that they often what do they want a change the datatype I like if you wanna change of from geometry to geography because you decided you don't wanna use like the Web Mercator projection which is commonly used for web mapping how many people are familiar with when but
have signed on to explain that so if you had let's say a web catering you decided that he wanted to use the geography type because you can do mostly like proximity analysis and you'd use a
command you can do it in 1 step you just basically do alter the transformed to WGS 84 which has the ID for 3 to 6 and that's about it but people use a drop the column with easy adding new column updated and then drop it but you don't really need to do that just 1 converting existing data types in an existing column
from sometimes you wanted you both web mapping and on very fine tune proximity analysis in which case you might wanna keep both the geometry column in the geography column so in that case you wanna create a new column as
shown here so we added geography column and the then we update it with the data that we loaded from web marketer by transforming and then we create an index a spatial index the spatial indexes are important to you don't wanna forget because to be really slow at that
and so if you look at the geography columns of table then you should see the new tables that you created but if you notice here and when I brought in the polygon it was just geometry but with the ST multi-antenna forced all the single polygons the multi polygon and nite at a specified is calling out and I get a clean multi polygon type so the 1st the 1st thing that people
wanted do with posters assist defined how close things are to a specific location so I could see wanna find all the restaurants it within 1 kilometer of a particular location the standard way that
people do this is to use the ST distance function and the problem with that is that that doesn't take advantage of spatial indexes so this is kind of like the brute force the brute force way of doing it in sequel server this actually works and can use a spatial index but not in process come so the alternative way some of the more posts a specific way is to use the STD
within function and so here it is set the same thing so you basically ask for what things are within 1 thousand metres and better restaurants using this a store
query and this basically does in a bounding box checked and expand on the bus check then it does a short circuit distance so it doesn't actually have to compute the distance and so it it works much faster with and if you just wanna
count of things you don't really want to just pull the data and then count he should just to account is way faster because you don't you don't part you don't come get the effect of having to pull the data out of disk the so if you have the basic lesson here is
that as the distance can't use a spatial index STD with 10 and this is kind of the textual
plan to demonstrate the at what
is using a geometric network like we are interested in the OK in that case you probably 1 use PT running because that would have a concept of a network so this 1 just does straight line site distance but it's really the minimum distance within the geometry so if it's a polygon and as the minimum distance within the coming up so that I mean you could use linear referencing into closest point but yeah you'd be better off at PG running on that OK so so 1 of
the common things that people do with OpenStreetMap data as the query both approaches and a story kind of like the examples that already shown um end it even if you have an index on both the geometry and there 8 and the tags column that is straw column it sometimes prefers 1 column 1 spatial index over another so you get you get the geometry the use but not but the tags index and
sometimes it uses both with what's called a bit maps can strategy the the so people using just to just index 1 for the geometry and 1 for the text but the alternative is you can actually create a single index that has both the geometry and a store in this applies to other data types to support the GST index type they can also use like if you have an array you could have geometry and an array and so here's an example I create a compound index for this I drop the original ones because you don't need you don't need the other 2 few men have a
single 1 that contains both I mean that the
down to there is a downside to this the downside is this index is faster than the 2 separate so if you just always query them separately you don't necessarily want do that the
add another common thing to you might wanna do is you don't wanna just find the distance but you wanna find number everything you also wanna know the actual distance of the things that are that are within the area so in that
case you would use both STD within an as the distance so you'd use STD within in the where clause and then you'd use and as the distance to sort of and you can actually use
that alias name that use in the selection order you don't have to repeat it so that's how 1
have disappeared because I defined it in the select part now some people use Web
Mercator for a proximity analysis and that's fraught with some problems and the 1st problem is if you use
Weber cater to say what's like with what's within 1 kilometer like what restaurants within 1 kilometer it's not really gonna give you
what restaurants are within 1 kilometer so for example this is the same query with the Web Mercator geometry Common you notice the you only get 1 answer whereas in the geography we got 5 or 6 chances enter that's just the with the way how women caters skews the world so you could still use web
mercator if you overshoot by who it depends how might where you are in the world how much if the overshoot but the idea is you want overshoot and then you wanted you a true distance create a check you know by casting to geography so that the idea
behind that is the 1st part the
query will use the spatial index of the way column the 2nd part will then take that set and then filtering out so you have a few receptor-like do we'll check on the other
alternative is you create a functional index status geography with some people it's kind of if the thing to do but it seems to improve speed and then you don't have to do this kind of hokey expand you can just the cast geography directly and just use it in your index will kick on because you have a geography index yeah come on the other
way you have seen people do is to quot out colored mutants geography Mercator and the idea behind that is that you buffer geography because I'll give you a true from 1 meter buffer convert that tumor cater and because it kind of skews things a more or less the same way in the same area um it more or less works so you end up with the same answer as if
you're doing and you could still take advantage of the the up of the Mercator
spatial index the and the next
common thing that people ask for is water the end closest thanks to me and for that you don't really care about how far those and closer things are you just wanna like what are the 5 closest restaurants inaugural for the air so
for this that and the brute force way is to just do it distance check across all your arm geometries in your table and answer by distance in and take that N which is the have a large table is really slow and for small table of it's you know reasonable reasonable so the way to get to use a
spatial index and in that case is allowed to use the can operators geometry operators which were introduced in push is 2 . 0 and but these are only bounding box they don't actually work against the geometry so 4 points like if you're using a measure preserving of projection it's absolute for anything else it's not so you have the same issue of the the other thing
is that it only works in order by cause it doesn't work in the where the it is you have a question not OK and this OK here's an
example and so this is using what's called the common table expression where
we define any sub-query
called S 1 and this 1 we see is using the operator and then the final from so we overshoot that and then we take the top 5 based on the real distance function that based real distance calculation and here's
the explained planted demonstrate that it is
using an index so recover that Viet
point data it's more or less right on less if are you using something like
geometry In WGS 84 than it's it's not right the so here we see a answers
hand they're much faster which I don't
have a much faster but can and doesn't
work for geography at least not yet hopefully pushes 2 . 2 will have I can infer geography than that right now the but you could
use geometry with geography and it's kind of like the reverse of what we talked about where instead of a just the geography you use a geometry spatial index In addition the and so when you wanna use the geometry spatial index just cast a geography to geometry
and it will use the index because it's a function on that just it's probably likely but the this yeah and this are
equivalent is that for whatever reason I can throw the cast syntax into a
pre index i have to use a function but they they resolved to the same thing the and so here you see
it's much faster like before we had 29 seconds now at the 3 seconds and it's it's
almost as fast as the geometry 1 the now I am in terms of using KNN if you use geometry what we call plaque so basically geomet Ahmadinejad WGS 84 it's actually not as good as Mercator from 1 from have observed so you always have to do that double check so you see here that dust the answers we get our different they're not the right answers but
if we if we use boy we use Mercator and is right sometimes you wanna get you
don't really care if you don't have a single location like let's say you wanna know the closest um the closest transportation points to a the the like all the restaurants in your set they don't have a single point of reference so can doesn't normally take and so the way to get it to kick in is to use the lateral class which was introduced impose grace 9 . 3 I mean people are familiar with lateral after hockey just once so basically the idea beats behind the lateral clause is that it allows you to do a subselect in the FROM clause
so here's the here's the lateral class so the lateral part is this and the important thing to note is that the p . way comes from this table normally you can't get away with that an so this basically treats a p . way is a constant so in this case my index my spatial index can taken but normally wouldn't be able to i'd have to have an absolute constant here so that allows me to uh
basically say for each for each restaurant antifreeze Japanese restaurant and I wanna go to but what each Japanese restaurant in my whole table what's the closest to transportation locations to it but the 1 of the cool features that was
introduced simple system 2 . 1 is the segmentized function for geography and how's that different from the segmentized for geometry at geometry even if you have a WGS 84 it treats it like a Cartesian plane so we stigmatize is still linear it's still you know so it's not right like if you were to plot in honor curve it's not right but on their geography considers the spirit shape and it actually segment r across the you know longest period so you can throw
this on yeah you can convert this to W
K T and we also have a new function in 2 . 2 which is released yet um which which will output to the Google encoded formats that makes it easy to just throw the year the the segmentized on the on the on a Google
map so here's kind the difference which are still from the bottom the stocks this is the geometry you see how that's a straight line but if he's Sharifi then you get the true curve behavior and so now I'm going
into some post a specific stuff so how
many people use posters roster here In all cases where the fuck was of so for this I I just loaded some
elevation data and aerial data
In the mosquitoes commands the 1st thing you wanna do when you using roster is you use it how the data because it you know rosters of big two-to-one stuff like you know 10 gigabyte roster in a single column that it becomes kind hard to query see chop it up into tiles but then if you're trying to and get area of interest in in those across tiles you wanna 1st and
you wanna carve out areas we wanna figure out what tells fit in your area of interest and what people mistakenly do is they 1st union and then they clipped so if you're Tao's around relatively big this is really slow come it's actually much
faster if you clip 1st and then you union it's actually I think in many cases orders of magnitude but it depends on and how big your tiles are the bigger tells a more efficient process so so here is the best example but I forgot
to put the timings and about that in 0
1 function I really love using as the st resize function which allows you to basically take a lower resolution without any consideration of the spatial reference system of it but this st resize function has a lot of uh it's very overloaded so it has 1 variant that takes into years which is the pixel size with a high and then it has 1 that takes percentages and then it has another 1 takes taxes so it's easy to fall into the wrong loop so this is what often
happens them when people coming to to rest they do this can anybody figure out what's wrong with this a . it In this section that is a
problem nobody knows what's wrong with us yes well 0 yeah that's it that's it you got it OK so so the what happens in this case it's it falls into the flow and version which thinks that you're talking about percentages and expects everything to be lower than 1 but it's not lower than 1 so you get this annoying error percentages must be value greater because it doesn't matter what thing allele complained about as it doesn't allow you to go above 100 % that kind of system of but who so he's like this functions to um
so so where you have to do is you have to ensure that both foreign integer if you want pixels you have to cast it 2 integers so it gets treated as an integer and then you get your nice picture and
then the other lesson which is not really to Rasta specific but when you're working with any spatial data is you always want to transform the fewer records so in that case it use your location of interest 81 transform it to what you have indexed unless if you have a functional index on the transformed so in this case we want to transform or elevation which isn't WGS 84 the same projections are faster and we get the elevation you in I set
about it and here's the link to it if you wanna by any of our books of any questions the the and you know which is
women if you have the slides available 0 yeah I'll post them on output summer and there was just you as well so giving a talk at Chicago were giving some tutors who will suppose those words 1 is for a job geometry geography the other ones for recipes PT routing and and topology so post those slides as well focus on so just remember that sunny wanna buy books you wanna read our stuff for slides there are any other questions yeah you're talking about the k and and using that to see what points are in geometries Sahaja with I'm often looking at whether 2 of this series is In this series of geometries and this other Sousa geometries which ones overlap the weight you use in to do something with that are arm in that case you just use the intersex function you would really need over you would use can and for that is Oh no amido the faster way is you can simplify but it's not like absolutely right but if I have something like a polygon that's huge you know that's got like 100 thousand points I use a simplified 1st and then you can actually create a spatial index on the simplified so so that ends up giving you faster answers to or you can do like up actually no you don't create a spatial index on the simplified you create it you basically you can write you can write a wrapper function that basically simulates the STD with him but it does like a simplification to the level that you want to the accuracy the once you get faster but is not actually accurate and with I have a tricky question about him in Hungary have you found a way to make to use K and then to get points within a distance that can be the within take can away so if you use the canon operator yes to get points within within a radius so you can consistently we deal with that I now because I don't think those 2 mix well together so yeah I I would use women with their and and give up the canon part you buttons
Web Site
Physikalisches System
Binder <Informatik>
YouTube
Softwareentwickler
Computeranimation
Homepage
Bit
Punkt
Mathematik
Selbst organisierendes System
Stichprobe
Nummerung
Maßerweiterung
Zählen
Fokalpunkt
Räumliche Anordnung
Polygon
Computeranimation
Rechenschieber
Mapping <Computergraphik>
Benutzerbeteiligung
Datentyp
Projektive Ebene
Abstand
Maßerweiterung
Speicher <Informatik>
Schlüsselverwaltung
Gerade
Analysis
Tabelle <Informatik>
Mapping <Computergraphik>
Benutzerbeteiligung
TUNIS <Programm>
Datentyp
Räumliche Anordnung
Abstand
Tropfen
Räumliche Anordnung
Computeranimation
Analysis
Multiplikation
Elektronisches Marketing
Automatische Indexierung
Datentyp
Räumliche Anordnung
Polygon
Computeranimation
Tabelle <Informatik>
Abstand
Lineares Funktional
Prozess <Physik>
Forcing
Automatische Indexierung
Server
Fortsetzung <Mathematik>
Abstand
URL
Instantiierung
Statechart
Computeranimation
Lineares Funktional
Quader
Meter
Bus <Informatik>
Abfrage
Abstand
Speicher <Informatik>
Computeranimation
Soundverarbeitung
Abstand
Automatische Indexierung
Mini-Disc
Mereologie
Indexberechnung
Abstand
Zählen
Computeranimation
Web Site
Mereologie
Datennetz
Extrempunkt
Automatische Handlungsplanung
Indexberechnung
Abstand
Automatische Handlungsplanung
Räumliche Anordnung
Gerade
Computeranimation
Automatische Indexierung
Bit
Indexberechnung
Abfrage
Räumliche Anordnung
Computeranimation
Eins
Mapping <Computergraphik>
Automatische Indexierung
Datentyp
Schätzung
Strategisches Spiel
Äußere Algebra eines Moduls
Speicher <Informatik>
Flächeninhalt
Automatische Indexierung
Indexberechnung
Zahlenbereich
Abstand
Computeranimation
Aliasing
Trennschärfe <Statistik>
Abstand
Ordnung <Mathematik>
Statechart
Computeranimation
Benutzerbeteiligung
Mereologie
Abstand
Analysis
Computeranimation
Analysis
Benutzerbeteiligung
Abfrage
Abstand
Räumliche Anordnung
Computeranimation
E-Learning
Lineares Funktional
Menge
Automatische Indexierung
Mereologie
Indexberechnung
Äußere Algebra eines Moduls
Abfrage
Lineares Funktional
Auswahlverfahren
Computeranimation
Puffer <Netzplantechnik>
Pufferspeicher
Flächeninhalt
Schiefe Wahrscheinlichkeitsverteilung
Meter
Computeranimation
Pufferspeicher
Automatische Indexierung
Wasserdampftafel
Computeranimation
Nichtlinearer Operator
Quader
Rechenzeit
Indexberechnung
Kraft
Räumliche Anordnung
Computeranimation
Forcing
Betrag <Mathematik>
Projektive Ebene
Räumliche Anordnung
Abstand
Einflussgröße
Tabelle <Informatik>
Arithmetischer Ausdruck
Physikalischer Effekt
Indexberechnung
Spieltheorie
Räumliche Anordnung
Nichtlinearer Operator
Ordnung <Mathematik>
Computeranimation
Tabelle <Informatik>
Nichtlinearer Operator
Lineares Funktional
Reelle Zahl
Rechenzeit
Indexberechnung
Abstand
Rechnen
Computeranimation
Abstand
Punkt
Automatische Indexierung
Rechenzeit
Indexberechnung
Räumliche Anordnung
Computeranimation
Normalvektor
Addition
Automatische Indexierung
Reverse Engineering
Rechter Winkel
Nichtlinearer Operator
Räumliche Anordnung
Computeranimation
Lineares Funktional
Automatische Indexierung
Machsches Prinzip
Äquivalenzklasse
Auswahlverfahren
Lineares Funktional
Automatische Indexierung
Rechenzeit
Zwei
Räumliche Anordnung
Term
Räumliche Anordnung
Hacker
Computeranimation
Fehlertoleranz
Punkt
Menge
Konstante
Klasse <Mathematik>
Einfache Genauigkeit
Räumliche Anordnung
URL
Transportproblem
Computeranimation
Automatische Indexierung
Klasse <Mathematik>
Mereologie
URL
Transportproblem
Computeranimation
Tabelle <Informatik>
Ebene
Lineares Funktional
Shape <Informatik>
Funktion <Mathematik>
Physikalisches System
Kurvenanpassung
Frequenz
Räumliche Anordnung
Computeranimation
Mapping <Computergraphik>
Lineares Funktional
Subtraktion
Minimum
Dateiformat
Kurvenanpassung
Räumliche Anordnung
Gerade
Computeranimation
Funktion <Mathematik>
Bitmap-Graphik
Sinusfunktion
Räumliche Anordnung
Computeranimation
Tabelle <Informatik>
Bitmap-Graphik
Flächeninhalt
Parkettierung
Flächeninhalt
Figurierte Zahl
Computeranimation
Fitnessfunktion
Prozess <Physik>
Parkettierung
Zentrische Streckung
Größenordnung
Loop
Lineares Funktional
Pixel
Zentrische Streckung
Garbentheorie
Physikalisches System
Figurierte Zahl
Computeranimation
Bildauflösung
Lineares Funktional
Mailing-Liste
Pixel
Ganze Zahl
Versionsverwaltung
Physikalisches System
Datenfluss
Computeranimation
Fehlermeldung
Umwandlungsenthalpie
Datensatz
Automatische Indexierung
Softwarewartung
Projektive Ebene
URL
Computeranimation
Lineares Funktional
Radius
Nichtlinearer Operator
Gewicht <Mathematik>
Punkt
Reihe
Gibbs-Verteilung
Binder <Informatik>
Räumliche Anordnung
Polygon
Computeranimation
Übergang
Eins
Netzwerktopologie
Rechenschieber
Automatische Indexierung
Prozess <Informatik>
Wrapper <Programmierung>
Mereologie
Mixed Reality
Vorlesung/Konferenz
Wort <Informatik>
Abstand
Funktion <Mathematik>

Metadaten

Formale Metadaten

Titel Writing better PostGIS queries
Serientitel FOSS4G 2014 Portland
Autor Obe, Regina
Lizenz CC-Namensnennung 3.0 Deutschland:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen.
DOI 10.5446/31741
Herausgeber FOSS4G, Open Source Geospatial Foundation (OSGeo)
Erscheinungsjahr 2014
Sprache Englisch
Produzent FOSS4G
Open Source Geospatial Foundation (OSGeo)
Produktionsjahr 2014
Produktionsort Portland, Oregon, United States of America

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract This presentation will demonstrate ways to take most advantage of spatial indexes, SQL constructs, and PostGIS specific functions. For these exercises we'll be using PostGIS 2.1+ and PostgreSQL 9.3+ . We'll demonstrate common cases people often do inefficiently.This presentation demonstrates the following1) Various SQL constructs including ANTI join, LEFT, RIGHT, EXISTS, LATERAL, CASE clauses, aggregates2) What common table expressions (CTEs) are and when to and when not to use them3) We'll demonstrate these concepts in use in a couple of common spatial query problems - e.g. proximity analysis (both geometry and geography), raster analysis and generation, aggregation of data based on various attributes, other correlation queries.
Schlagwörter postgis
sql
postgis raster

Ähnliche Filme

Loading...