Gimme some YeSQL ! - and a GIS -

Video in TIB AV-Portal: Gimme some YeSQL ! - and a GIS -

Formal Metadata

Gimme some YeSQL ! - and a GIS -
Title of Series
CC Attribution 3.0 Germany:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Release Date
Open Source Geospatial Foundation (OSGeo)
Production Year
Production Place
Portland, Oregon, United States of America

Content Metadata

Subject Area
So long NoSQL, here is YeSQL !Not long ago, PostgreSQL was the the most advanced OpenSource relational database. With the latest version 9.4, it became an over-powerful mutant : the most advanced OpenSource Object-Oriented relational and/or non-relational, Spatial, SQL and/or NoSQL database. For the sake of simplicity, let us call it a YeSQL database.This presentation will introduce you to the feature galore of PostgreSQL, giving insights into the latest improvements from a user point of view. Of course some GIS inclination will drive this talk, and show you how you can take advantage of spatial extensions together with PostgreSQL core features.PostgreSQL 9.4 is an important milestone for various reason : a lot of new outstanding features, and core improvements which prefigure a whole world of new use cases. The main feature from a user perspective, giving PostgreSQL this YeSQL title, is probably the new JSONB storage. A fine marriage between the Hstore extension and JSON support, it literally transforms PostgreSQL into a document database (think MongoDB in PostgreSQL without data losses).We will therefore present great PostgreSQL 9.4 features, and some ways to use them with spatial data, leveraging the latest PostGIS and PointCloud extensions :* Exclusion constraints* KNN search* Lateral joins* Window functions* (writeable) (recursive) CTE* Automatic updateable views* Materialized views* JSON, more JSON, JSON indexing, JSON proceduresÉ* Foreign Data Wrappers* Logical decoding and future applications* More JSON ? GeoJSON ?The feature set available to PostgreSQL users is growing with every release, as are performances. And the spatial part of it is not lagging behind. It is a must-use platform for data management, data infrastructures. And a GIS.
Keywords PostgreSQL Spatial Database SQL NoSQL JSON YeSQL
Goodness of fit
Data management Sequel Relational database Reduction of order Numbering scheme Basis <Mathematik> Database Information security Sequence Active contour model Physical system
Point (geometry) Perfect group Sequence Revision control Goodness of fit Query language Personal digital assistant Forest Bus (computing) output Summierbarkeit Quicksort Intercept theorem Table (information) Geometry
Ocean current Data management View (database) Operator (mathematics) Bit File viewer Table (information)
Point (geometry) Group action View (database) Multiplication sign Materialization (paranormal) Computer Element (mathematics) Computer configuration Operator (mathematics) Mathematical optimization Tunis Area Default (computer science) Inheritance (object-oriented programming) Relational database Block (periodic table) Fitness function Bit Subject indexing Digital rights management Query language Personal digital assistant Right angle Summierbarkeit Table (information) Resultant Geometry
Closed set Multiplication sign Range (statistics) Expression Open set Element (mathematics) Type theory Subject indexing Enumerated type Query language Summierbarkeit Right angle Table (information)
Wiki Twin prime Goodness of fit Word Bit rate Query language Multiplication sign Operator (mathematics) Expression Set (mathematics) Table (information) Resultant
Algorithm Graph (mathematics) Mathematical analysis Sequence Element (mathematics) Subject indexing Word Software Natural number Query language Personal digital assistant Phase transition Resultant Mathematical optimization
Revision control Crash (computing) State of matter Survival analysis Set (mathematics) Water vapor Table (information) Software maintenance Flow separation Information security Computer Resultant
Point (geometry) Group action Server (computing) Computer file Data storage device Device driver Database Replication (computing) Sequence Logic Query language Ontology Operator (mathematics) Website output Codierung <Programmierung> Table (information) Logic gate Geometry
Presentation of a group Expression Data storage device Device driver Database Function (mathematics) Computer font Mereology Sequence Semantics (computer science) Product (business) Subject indexing Inference Roundness (object) Bit rate Software testing
Functional (mathematics) Assembly language Multiplication sign Adaptive behavior Data storage device Database Parameter (computer programming) Mereology Sequence Element (mathematics) Type theory Logic Different (Kate Ryan album) Operator (mathematics) Object (grammar) Codierung <Programmierung> Geometry
Presentation of a group Key (cryptography) Code Multiplication sign Expression Data storage device Sampling (statistics) Bit Database Lattice (order) Element (mathematics) Subject indexing Type theory Personal digital assistant Query language Operator (mathematics) Right angle Bounded variation Mathematical optimization Exception handling
Revision control Distribution (mathematics) Subgroup
Point (geometry) Distribution (mathematics) Game controller Group action Closed set Database Medical imaging Octave Point cloud Table (information) Arithmetic progression Posterior probability Form (programming) Oracle
Subject indexing Casting (performing arts) Voting Projective plane Codierung <Programmierung>
Subject indexing Query language Personal digital assistant State of matter Different (Kate Ryan album) Weight Mixed reality Sequence Monster group Mathematical optimization
I would hope to you had a good start of prosody and so when did some it and I going to present you today some as things about those was the couldn't so does the sequence of
what is this thing do you know but what was the secret is exactly we know that SW activity at
the is a singular bonded ways but yes so apparently you all users of POS secret so
but to define what is just quickly it's a are all the matters which is a reduction of of database management system which is kind of of from and think of the death of his spouse of war the uh possessor goodies sequel compliance it's transactional it's a seed so that's a basic um constraint on that basis is got MVC scheme incubation contour system is extensible and it's got special features because you you got pushed yes of course you know that so today I'm gonna talk about those who is seek where new stuff and some not that by new stuff but the interesting stuff which are specific to pose with sequence and not a specific to pose serious you on no lot of of all pose serious icing but a lot of things in were security which may be useful for you and I which are not exactly
insight but so policy could is currently under in 9 point X so that the main version uh stable version is 9 . 3 and 9 . 4 is just around the corner so we're currently in beta 2 and it will be 1 belong before to reduce probably in October of something about uh so what's your inputs and impose with sequen 9 . 4 or I'm gonna show you some of the new stuff and so about to sum up there is a a a lot of good foundation walk like a very basic world very boring work uh that you want you want to use exactly like that and to maybe a few years but uh it sums of a lot of things needed to make new uh advances into that of his work was published you could so 9 . 4 is important in this would go and there are a lot of new features of our use of futures and some of them are useful approaches sort of show you some something so we have be talking about sequence so cool how'd you don't know squat or of not even 1 that's good perfect so let's go for the future go
so the 1st 1 is that the auditory so attorneys new kind of a specification for joint and so it allows you to access to all the table in sub queries it is kind of not that useful apparently but actually a forest Sergei as far as it fixes the um that distance a brighter than a constant issue which means um actually if you know this 1 which is a key and then search so nearest neighbor search you have to use normally a constant to compare to the geometry Europe and you are looking the wrong answer you cannot refer here to something which is outside of the of the query but resist lateral keyword you can't do that now we suppose were sequence before so you kind of like this kind of Korea which is fine me is a true bars closest to each bus stop but just in case 1 is close I want to and so so I got a bus is a good point for busses and intercept query I'm gonna find the 2 uh closest pals uh in regard to the suppose geometry so that's something which is interesting for gist 1st years of because otherwise you would have to do a lot of subqueries and that's a pain to right so outputs the other 1 is altered
views so we got views you all know what a furious as the saying is with 9 months we uh we got and later we got although at the 2 reduced so when you create a view you can
actually updates of you insert things into the viewer bit of you as soon as it's a simple you know I mean when it's a very complex so you with joining a lot of table is not possible to know to to to mine which updates you we you want to know but I it's a simple here can do that on without having any trigger because otherwise you would have to do that which triggers so that's pretty interesting and 9 . 4 that's a could uneven management which means you can have have view with some columns that are a bit of and some that are not sink a like a like if I computed offering a view for example I cannot of birth for current because it's not in the operating table but with 9 . 4 you could updates a bit of a cut on
multiple before slots pretty good and it allows us where to do fine tuning special right management because you could create a view in a specific area and then you could say uh by grants some more rights you 40 some users but not to the parent table so that you can restrict uh the the rights to some areas and to take actions allows you to prevent the user to a bit of you so that's a new elements go out those of you so this is an example of of you with such a constraint so I say I want to have always contained inside uh the sporty and so I want take options so that if I move as the geometry is I cannot move them outside of this political so that's uh what they can do now is a dating to view and simplifying simplifying the territory we've been no problem but if I try to translate the geometry a lot it won't fit into the party going defined on the view and then the apples with the query said no you cannot do that and that's a good thing to do as well uh we 9 . 4 and check options it is something which is pretty useful as well talking about use we got materialized Rousseau's now also much as used outcome came from 9 and so it's it actually view instead of just creating a reference to a query and so as a result of so you can think about it as a cash you for example it allows indexing on the material as you so you cannot some specific index on the material you which you don't have on the Parliament's table so that's interesting answer by default indexes and constrained from the branch of on not computer so if you want to have the same uh optimization you have prone to but have to computer indexes and constraint on the matters 9 4 improve that to have a refresh concurrently which means you can refresh view y you still accessing it so it's it's you don't have a big block on the new thoughts but you could use case is as our material is use of complex joins of complex spatial operations of 4 and that the worker everything which takes time uh to compute you can use a multitude of you to gasses data so good
that's an example for materials used so I that's like that's a that's a pretty computer-intensive of urea computing the performance of the sum of a lot of points I can use material as you and it's create materials you simply and sent to refresh I can say refresh my matter is materialized you 1 before and you can see that it's using a sequence and stand on the material that you so that's a good thing to around is a sorry yes question the and and uh not as it is and talk about the increment of seems to be fleet so when it is
our new that the types of which so what's reserve in Tigger enumerate dates and it allows you to do wonders of elements so you can have open use close friend is infinite ranges you can of indexes and is and it's good for all time that is particularly because you or of the sum of IT before unbodied after have gone on to know that the races so you can use ranges you you will have only 1 girl and that's pretty uh contingent right of our city who would who knows what the city is yeah someone values so cities a common table expressions it's actually as the is he that's
another uh syntactic we're frighting query which is pretty convenient because you can do things like is my kind of
temporary table which is a query I want them to do some operations just so you can write to is as steps are actually and in the post was the great late as this and you can do some modest modifying queries inside used the Monteverde expression just like that I got a new table but data which creates an award and want to deviate sings from a my good J is data words not valid and I cause this is the result of this debate uh to dump rate deleted under 1 twin sets of nodes research into my bad data table so that's or 1 really it's taking the data outs and at the same time taking the data in of so it's a pretty good syntax um the sure yeah
writing complex secret words and we city you can also do wikis city uh which is there something you
can do about network with itself and and if so cities uh into
the quest announces so that so we've considered key keyword and so that the city syntax which is pretty cool you got to be careful Roo we cities to performances because sometimes right prevent you optimize the to do some of optimization and it can be slow rose up uh anomic where we we support where requested cities that allows us to do graph analysis so that because it's like save on graph a graph algorithm by nature our work you'll see if you can implements them using sequence that's an example of this I don't want you to read everything but that certain always we choose you got 3 steps mainly you got see in these in the initialization phase where you select some elements then you got erectus phase when where you would say OK I will you reuse the results of the previous step to get new elements so for example on the network and we detect 1 or other and I would say give give me also what's connected to my to my 1st world and then you go work to the from 1 word to another to do some graph analysis and at the end you can use the results to display something 1 use case and it's exact is and that's the secret Bruges here is I got to the river network and I computes all upstream segments from 1 specific thing and it's very fast issue that indexing as with it's gonna use indexes all along the way and it's pretty clear and it's pretty fast it's actually for some queries fossils and using it you to become more complicated to write but once you've written once a recursive query smallest always
another feature another table so
additive or a table we is no securities so that's no water no maintenance and it's really faster to write to that's between of survival crash they have to say that again uh so it is worse off for a temporary results and for we produce result so I introduce do some pretty easy computation state budget for example you can use another devote would be quite faster but do not forget that humans several crash that the left so that's pretty easy to use its creates an table and so you just say that a new table with 9 . 5 which is next versions of next year you could you would be able to say at a table set log all sets and nodes so you can switch from road to another which put pretty convenience when you temporary data switch to non temporary data if you consider log all you do is you that thought about and yet
group the yeah the the so if you add up yeah um Buttercup decoding so it was about so it was a question about and uh data files so that data does not so magical decoding is probably
as as 9 . 4 feature but you want to use it why is it so why that is because the 1st step is of many steps and it's kind of a database modification of logo actually some but no there's some some dualizing ontological evil so on the query evil and that's a 1st step to logical replication which is another step to multi-master replication lecture so when you got so much per server updated at the same time and want to synchronize them so that's what you must replication and you don't want to do that a repetition ontological evil for that so that's the 1st step which must or petition 1 being the political unto next year probably but you can always already use logic of decoding for using site loading uh we got have best soon we're gonna have increment updates as well at some point and this looks like something I got so you initialise as a logical decoding is thought to be to create logical replication you greatest lots lots of place I want to listen to what happens in my database it as and I'm gonna do some operations on my database for example candidates but from points where GATE equals 1 and then I can queries a logical decoding and tool so that's again gets the modification done and it's as this this decoding driver will send me some of the specific data are saying uh the 2 . tables we modified answered D 1 has been to date so you can access to all the modification in you the debates with with sequence but which could if I had that 2 point for example I change the geometry I can access as a logical because the loading and I can get everything which is modified so I could I could we create the queries which have been used to modify the data I could stores them to a re a place and to Annals of database uh yeah of the and a lot of different together that does not for you and so uh it's faster as well it's it's very low even when the input with sequence and it's rock-solid
and yeah it's much faster and it's easier to set up and its extendable as well so as
this this output is a test decoding drivers are all the drivers you can get a date and Jason for example uh so you can get a modification written in Jason and use it for whatever you want to use it we apply that to among the DB the database of some yes QR so yes go is not a well that an existing well but it's from her Dimitri font move and as but is that so it's just a reaction to the knows the cost is the so I you know Gnostic were more or less so uh some people say no sequence of future some will say it's bytes uh so but which no secret what most acquires a lot of different no sequence and present sequester products now document-oriented uh no sequence so that's unstructured data did values and so you can think of it as Mongo DB on AC so you're getting high there and um our inference you quickly the a semantics and the prox the axiomatic part as well which is pretty of and the H star would be present in the next August so quickly because of every round of stuff time of day song is the new document storage in 9 1 2 and later uh you can stop p-value things and you got in indexing uh but not in this you have to create an expression indexes writes it's a text storage it's very fast trial and it's got the size of a rate compared to just the plain text but it's pretty efficient it's not as efficient acts as a store but still and there is no Dean and this index on a specialized index for exists at
time of data and you get a lot of operators and functions so you can access elements you can access and text us Jason object you can uh get passes out of some do some objects you can convert some Arroyo's 2 days and so you directly have some adaptation data out of your database Europe was the geometry and could be that stupid you carry and you got a ton of
different features with Jason and so critical argument of defiance of a a lot of things to promote and so uh this and beat assemblies of being using so 2nd best feature in the Swiss sequence 9 and for the past logical decoding as that's for Jason binary so there some type to some native type pose with secret is a written as text uh this G some B is a binary storage so you get you get like a inside the database with a different to some parts that's is not the duplicates as there is no other preservation it's got slower
I will arise quite because that disarmament in storage uh but actually it's better because you have 8 star like operators that you have all the day some operators as well and your own perfume or operators which the 1 here and moribundity you that Dean indexing so you can index should Jason and it's really fast so use except for some specific used use cases if would need some very good I you'll our issue need some most flattering guns and you can use it stoic can users
of sodium index uh onto day is therefore this kind of operator which is uh is their key presents a user a of the immense so presented might do some its very fast you can as well rights expression indexes and you get some variation on the indexes so that you can put a tail of the optimization for a look up inside of that of it so you can do this kind of seeing uh to ever be to inside my uh a 1 b 2 adjacent be elements on it's a true uh do you have any of the elements in the any BK in a 1 b 2 years is I can do pass search as well I can do look up for a narraive keys that's very flexible are very very convenient so that's all and structured data and it's very fast if you have to send you can have duties on we edges spots so that Perseus place to you can have uh reason by STS you hours due to some 90 with at something like this and you can do some queries meetings a edge that native types with the most serious due to some and you got this kind of where formatted duties and uh documents outside of the database we sort of it to write than PHP code which um so this and the other queries a lot of user queries and so some more nodes to bit of time gap uh the new things as well samples were
sequenced so from deploying here there is this APT deposed credits are will go which got packages for all supported those
with the provision for ah epitope-based distribution so what a reservation of political with version of your distribution you can in some packages that's maintain interested but this is inside so use it subgroup of I got
to Dr. repeated use images where images were with sausages features of and inside already so was Mr. Green 9 . 4 points here so as to reduce the FPT in point cloud and it's a 1 I normally on talker enabled to distribution if it works as an it's Bush have but nobody should so that's pretty convenient that's not on what's coming next so what we are currently working on a work of special following that the upper access your Octave spatial
data from inside your posterior so that the base so that the article that's uh transparent access to work table action currently there is no Oracle Spatial supports or on the oracle of classic there you could walk out on ways to be Katie as an intermediary for much form of Oracle to plus years but it's a mess to set up uh so we're working on native supports so from as you directly to posteriors but for petition close that emits queries it into it's a work in progress but most of us a controls per week resting it as an example of using directly an oracle database that was but she's so it doesn't work it should not my for bytes
support project so once it's out you can use it was them for a while but it's not
obvious but especially index is as biggest which is the some of that project we see the
decoder's committable not sure yet but there some steps so it faster to it fast automated and there is this vote casting so we have the
um embody B as a on a state so now we have was sequences but uh that's the rich guys creating new kind of index is as its abbreviation of 30 some the indexes and it's a net Freebase angiostatin onto 3 walk what does that mean that means it's orgy of spaghetti monster mess when you have all your data which the same extends uh you queries will run very slow always CASE CID is making out of this pretty gone a lot of
difference more preguntas up against the optimizer queries based on a mix so as not yet that far before 9 1 5 but a discovery of support and
that's about all that sort of thing I'm late I'm sorry but many of them for 1 Gaussian yes such yeah question that notion
Ch well but this was too much OK that affect been something thank you and that was


  412 ms - page object


AV-Portal 3.21.3 (19e43a18c8aa08bcbdf3e35b975c18acb737c630)