PostGIS Feature Frenzy

Video in TIB AV-Portal: PostGIS Feature Frenzy

Formal Metadata

PostGIS Feature Frenzy
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
PostGIS has over 300 functions, which in turn can be used with the many features of the underlying PostgreSQL database. This talk covers some basic and not- so- basic ways to use PostGIS/PostgreSQL to process spatial data, to build infrastructures, and to do crazy things with data. Consider the possibilities: raster, topology, linear referencing, history tracking, web services, overlays, unions, joins, constraints, replication, json, xml, and more!
Keywords postgis features technology
State of matter Reduction of order
Point (geometry) State of matter Multiplication sign Energy level Food energy Product (business)
Web 2.0 Suite (music) Building Context awareness Open source Mapping Multiplication sign Projective plane Number Product (business)
Point (geometry) Server (computing) Functional (mathematics) Context awareness INTEGRAL Random access Set (mathematics) Mereology Product (business) Usability Number String (computer science) Energy level Hydraulic jump Descriptive statistics Airfoil Polygon Rollback (data management) Database Basis <Mathematik> Line (geometry) Flow separation Type theory Subject indexing Spring (hydrology) Personal digital assistant Network topology Table (information) Row (database) Geometry
Point (geometry) Standard deviation Functional (mathematics) Serial port Sequel Namespace Polygon Database Line (geometry) Type theory Category of being Different (Kate Ryan album) Internet service provider Operator (mathematics) Buffer solution Metric system Error message
Server (computing) Functional (mathematics) Sequel Code Connectivity (graph theory) Data recovery Database Bit Line (geometry) Mereology Blog Video game Right angle
Classical physics Dataflow Functional (mathematics) Sequel Information Length Code INTEGRAL Electronic mailing list Database Line (geometry) Cartesian coordinate system Usability Category of being Query language Term (mathematics) Bus (computing) Statement (computer science) Moving average Table (information)
Point (geometry) Revision control Multiplication sign Keyboard shortcut Database Bit Information technology consulting Physical system
Area Computer file Information Polygon Data storage device Database Theory Type theory Mathematics Bit rate Software Different (Kate Ryan album) Query language Network topology Right angle Endliche Modelltheorie Table (information) Traffic reporting
Area Covering space Functional (mathematics) Sequel Weight Software developer Mathematical analysis Database Entire function Attribute grammar Type theory Subject indexing Latent heat Interpreter (computing) Website Object (grammar) Geometry
Revision control Message passing Mathematics Perfect group File format Core dump Data storage device Bit Resultant
Enterprise architecture Default (computer science) Standard deviation Open source File format Chemical equation Source code Projective plane Disk read-and-write head Product (business) Frequency Category of being Software Reading (process)
Point (geometry) Slide rule Server (computing) Mobile app Building Open source Multiplication sign Software developer Database Line (geometry) Field (computer science) Integrated development environment Different (Kate Ryan album) Point cloud Quicksort
Shift operator Web service Dynamical system Database Cloud computing Endliche Modelltheorie Wireless LAN Theory
Medical imaging Digital photography Cellular automaton Fiber bundle Whiteboard Differential topology
Point (geometry) Slide rule Dialect Mapping State of matter Electronic mailing list Archaeological field survey Ultraviolet photoelectron spectroscopy Instance (computer science) Web application Data management Personal digital assistant Energy level Physical system
Complex (psychology) Functional (mathematics) Sequel Block (periodic table) String (computer science) Equaliser (mathematics) Buffer solution Database Declarative programming Number Formal language Power (physics)
Goodness of fit Information Key (cryptography) Query language Archaeological field survey Energy level Right angle Game theory Table (information) Address space Number Row (database)
Covering space Key (cryptography) State of matter Code Planning Physicalism Basis <Mathematik> Database Line (geometry) Mereology Subject indexing Uniform resource locator Query language Universe (mathematics) Table (information) Condition number
Area Point (geometry) Functional (mathematics) Length File format Cellular automaton Binary code Set (mathematics) Line (geometry) Distance Binary file Sequence Radius Analogy Software testing Object (grammar) Geometry
Point (geometry) Area Covering space Building Functional (mathematics) Mapping State of matter Multiplication sign Polygon Execution unit Line (geometry) Disk read-and-write head Timestamp Symbol table Bit rate Table (information) Mathematical optimization
Covering space Area Algorithm Functional (mathematics) State of matter Multiplication sign Set (mathematics) Vector graphics Software Network topology Order (biology) Vertex (graph theory) output Right angle Iteration Mathematical optimization Resultant Row (database)
Point (geometry) Classical physics Group action Functional (mathematics) Algorithm Link (knot theory) Multiplication sign Direction (geometry) Polygon Line (geometry) Computer Flow separation Number Subject indexing Query language Personal digital assistant Operator (mathematics) Touch typing Order (biology) Figurate number Resultant Geometry Alpha (investment)
Point (geometry) State observer Functional (mathematics) Transportation theory (mathematics) Multiplication sign ACID Streaming media Mass Field (computer science) Sign (mathematics) Bridging (networking) Different (Kate Ryan album) Software testing Physical system Domain name Dialect Multiplication Information Data storage device Database 10 (number) Data model Absolute geometry Uniform resource locator Data management Chain Linearization Species Geometry
Point (geometry) Curve Functional (mathematics) Game controller Sequel Curvilinear coordinates Equaliser (mathematics) Polygon Streaming media Line (geometry) Differentiable manifold Distance Measurement Subset Web application Type theory Latent heat Curvature Utility software Reverse engineering Arc (geometry)
Functional (mathematics) Algorithm Server (computing) Spline (mathematics) Sequel Personal digital assistant Projective plane Stress (mechanics) Representation (politics) Arc (geometry) Oracle Library (computing)
View (database) Graph (mathematics) Polygon Projective plane Rectangle Sphere Type theory Spherical geometry Personal digital assistant Square number Moving average Endliche Modelltheorie Pole (complex analysis)
Open source Latin square Projective plane Polygon Database Power (physics) Category of being Coefficient of determination Personal digital assistant Web-Designer Self-organization Circle Bijection Object (grammar) Physical system Geometry
Area Suite (music) Implementation Functional (mathematics) Mapping Projective plane Planning Line (geometry) Distance Sphere Planar graph Subject indexing Hacker (term) Calculation Circle Software testing Object (grammar) Pole (complex analysis) Geometry
Functional (mathematics) Mapping Great circle Projective plane Planning Line (geometry) Function (mathematics) Peer-to-peer Process (computing) Vertex (graph theory) Flag Right angle Data structure Address space Distortion (mathematics) Spacetime Task (computing)
Stapeldatei Functional (mathematics) Constraint (mathematics) Sequel Projective plane Database Metadata Number Revision control Type theory Operator (mathematics) Utility software Endliche Modelltheorie Table (information) Hydraulic jump Physical system Geometry
Point (geometry) Group action Functional (mathematics) Sequel Transformation (genetics) 3 (number) Database Metadata Type theory Mathematics Table (information) Geometry Physical system Row (database)
Three-dimensional space Functional (mathematics) File format Database Function (mathematics) Mereology Dimensional analysis Type theory Subject indexing Web service Triangle output Object (grammar) Row (database) Spacetime
Subject indexing Type theory Three-dimensional space Functional (mathematics) Representation (politics) Database Computational geometry Object (grammar) Function (mathematics) Dimensional analysis Geometry Library (computing)
Point (geometry) Information Orientation (vector space) Multiplication sign Surface Decimal Mathematical analysis Volume (thermodynamics) Database Line (geometry) Type theory Personal digital assistant Representation (politics) Object (grammar) Analytic continuation
Area Type theory Vector space Sequel Calculation Polygon Mathematical analysis Database Endliche Modelltheorie Drop (liquid) Table (information) Number
Area Classical physics Information Bit Database Mass Data management Vector space Personal digital assistant Network topology Boundary value problem Object (grammar) Determinant
Area Point (geometry) Matching (graph theory) State of matter System administrator Multiplication sign Independence (probability theory) Regular graph Subject indexing Population density Vertex (graph theory) Boundary value problem Endliche Modelltheorie Table (information)
Point (geometry) Observational study Forcing (mathematics) Distance Limit (category theory) Subject indexing Exterior algebra Personal digital assistant Order (biology) output Arrow of time Quicksort Game theory Table (information)
Point (geometry) Classical physics Electric generator Software developer Polygon Variance Theory Degree (graph theory) Ring (mathematics) Different (Kate Ryan album) Touch typing Self-organization Geometry Physical system
Point (geometry) Functional (mathematics) Validity (statistics) View (database) Polygon Mathematical analysis Distance Food energy Computer Mathematics Personal digital assistant Energy level Endliche Modelltheorie Hydraulic jump Geometry Exception handling
Web service Personal digital assistant Query language State of matter Mehrplatzsystem Triangle Database Unit circle Library (computing)
Point (geometry) Functional (mathematics) Statistics Building Implementation Multiplication sign Set (mathematics) Distance Revision control Core dump Covering space Curve Algorithm File format Curvilinear coordinates Diffuser (automotive) Database Basis <Mathematik> Type theory Query language Network topology output Object (grammar) Routing Geometry Library (computing)
Point (geometry) Adventure game Subject indexing Type theory Functional (mathematics) Perfect group Personal digital assistant Polygon Stress (mechanics) Line (geometry) Data type
Point (geometry) Standard deviation Group action Perfect group Code Multiplication sign Consistency View (database) Weight Basis <Mathematik> Database transaction Extreme programming Mereology Number Statement (computer science) Video game Software testing Address space Geometry Physical system Row (database)
I a few so this is the most is the sheer frenzy of
like to look at the ownership of the friends the concept and and the frenzy
because of the last to give state of posters talks a reduction opposed to stocks and inside of a 20 minutes or 25 it would it was really possible to talk about about what pushes is and who's using it any really be touch on and on all the features that are available and so I said that I said you know what I'm going to get rid of all introductory material and just talk about features immediately and people
can learn about how I came to be and so on on their own time then there the guns that state of talks tended to focus on the new features exclusively so someone coming in fresh would get this strange set of
od brand new stuff loaded into them and really not getting a feel for the overall capabilities of of the product so it made sense to constant features
to not avoid talking about features is because they happen to be old old features are just as useful as new features of to product fresh and also that'd be good of a friend is everyone likes to rack up a lot of points really fast right and so I submitted my future frenzy and then they said congratulations will make you invited talk and I was that this is where you have an hour was the whole point of the frenzy that only have 20 minutes I I can't keep but this level of energy for all our so how can and
then the whole hour material to is that rather than try to extend the frenzy I did was recognize the heddles introductory material about the history and context project which had previously cut out within a time so also brought back in so my Paul Ramsay and at from time time I'll face and self-serving things I'd like to preface then tie with they had no toad all glory to the building I I work work-bound
CEO the how we make the whole product produced web mapping using open source projects Open Source projects and we make you would use sweet and support an overdue suite consists of a number of a number of open source products bound together
and for ease of use and for integrated support from and amongst that set of products is hostess as when you talk to you about today so what is posters you asked me and I will tell you it is of spatial database and you'll see for of so what is a spatial database Paul and assistive server treating the 1st principles of move OK really take it back to the rollback level of what a database
would expect when you that pieces after called database and style what functionality is going to give you I was gonna give you random access but to potentially large chunks of data it's going to give you some types to represent the data in this can give you and indexes delighted to quickly look up particular rows in your tables and thank you some useful things with the data that you put in your tables so from that basis is really easy described what spatial databases the spatial database takes the functionalities of of the database and simply adds spatial so we have types still but we added to our numbers and strings additive added the geometry geography to and we still have B-tree indexes but we added archery and quadtree kd tree of the update tree indexes is yet part that later and we've added spatial functions that handle the spatial type things that understand what it means to be a special type of lines have like points of coordinate polygon severity a function that only makes sense within the context of a spatial and we have a just pick the descriptions for these things out of thin air we haven't done let the definition of a geometry spring over 4 heads and or the function that we provide a jump has been reported to be followed international standards as
set up by the geospatial consortia the simple features for sequel standard the and what civil features physical tells us is that
what kinds of types you have to provide points lines polygons collections of the same all kinds of operations you to provide against those types for example serial differences intersections metric differences buffers and what kind of function names you
have to use for that functionality for a particular namespace to use as well so intersects distance error union buffer all the lovely st prefects and posters is not alone in this category of OGC conforming spatial databases we got some some big friends
like Oracle Spatial sequel server spatial was the wonder most likely to run into in your professional life and spatial functionality is gone over last 10 years for being a bit of a rarity in a bit of an odd duck in the database world to something which is just consider the standard feature used to have to spend extra money on the spatial feature please now this comes in baked in so I
that's really interesting background but what is actually do what kinds of questions can you answer with a spatial database you can answer with an ordinary database and posters answers questions that spatial components things like uh just question the salvage yes question right what part those among bloggers fire so we can you tell the others to go away but how many lines to take to answer this Jespersen Harry lines of code to take answers questions a one-line right
use 1 spatial function in accordance to the fire the table parcel data we can generate a classic GIS warning list of people the phone about 5 or how this
question how far the bus travel last week to take into that I think I think 1 line just a simple
sequel statement that finds the best uh finds how we the edges follow the last week and summed up the total length I have a question like
summary questions how much land is there in each zoning quot category that take all those information roll up into just 1 line how much land and again how lying to take you that it should take 1 line is equal to
summarize the whole dataset just 1 query Siegel in a database is really really powerful it's more powerful than desktop GIS in terms the amount of code required to answer questions the size of datasets that can be queried in the ease of integration with other applications and other data flows in year in your institution so that's that's what posters isn't sort of at a general-purpose sense on out of history where they come
from the approaches came from originally a company that I founded in Victoria British
Columbia this is the picture all the staff In of I think work was the have the data the is what 2004 2005 and that's collection a few people that picture who were at this conference along with me and Miller works no 1 1 we know this country's worst company anymore I actually write post I just said posters would be a great idea and that's like get to be a cofounder but the guy who wrote that the database at my my urging was named a blast he wrote the 1st versions 0 . 1 0 . 7 before left the company in 1 of 2 is 0 then I might as to the build system the documentation the Java binding and so on but this actually pitched picture actually postdates the posters by points quite a bit at the time post is 0 . 1 was written there we fight people companies and the reason why we wrote it was because we had a consulting work for the British Columbia government and that consulting work involved managing lots of little chunks of data and it was the answer to the question you know
how to manage a whole bunch of changing data we were working for the tree negotiations Ministry in British Columbia and 1 of the things that renegotiate do is they generate areas and that maybe would be included in a tree settlement lands and then they come to the dead people may say tell me everything about this here this type of area how we trees are who owns apostles the and and then having got the report they go away the changes like they come back they tell about this 1 so this huge stack
of these different areas of interest for tree negotiations made a passenger modeling software you the answers that and they keep changing versions of be fine except the changes in strongly come back into the 1 I gave you 6 weeks ago that what what I want information about 1 and then you have to go back through a try to find 1 and there's 10 tables all generating these things but it was not the most efficient way to represent data the database model on the other hand would make a lot more sense rather than having 1 file per Republicans base we have the right 1 table for all the polygons with 1 poly on Corel so our our model or storage they would go from hundreds of things to 1 thing the query in 1 place so that's the theory rate ironically having written poses we never actually used it to have customer but it but there is a
lot advantages you query everything at once and if you wanna put up a little website attribution areas for them because you to browse all in 1 place so you can publish it all up 1 step you can manage it all in 1 place you don't have to go filling around finding stuff so a piece of it is why we bother to direct posters 1st place now
is the beginning but there is there is more to the story so 1st release and 2001 may 0 . 1 just object in indexes so the very basics of what it means to be a spatial database you can represent up geometries in back really fast but couple months later the 1st swallow functions to do analysis against these geometries by 2003 got enough functions to cover the entire OGC Simple Features for sequel specifications so everything is specified by the International Standards Organization we can do in 2003 2005 while a get to 1 . 0 release from some re changing of things under the covers to make the geometries lighter weight and improve performance 2010 a 1st because they use of new big brand new feature and added geography tied to the geometry type those already there and the most recently 2012 up which is 2 . 0 which added a roster type and there and if you look at these dates you may be saying yourself you know the year development temple appears to be slowing down what's going on paulie should I be worried here are the other developers getting tired as the community drifting away that I don't think that the interpretation you should put on on release of I feel like the interpretation should put on it so the more like
this where is asymptotically approaching perfection how and it's harder and harder to get closer with each passing year and as a result you know people's have with next release is going to be a of the you know particularly drunk they asked me when post is 3
. 0 Ozcan arrive but I guess I probably never probably never I mean the only reason we have post is 2 . 0 was because we need to make a breaking change to to the underlying the storage format which required a full dump and store of data between 1 . 5 and 2 . 0 and when we made that change we also made the format upgradable sold their and do that again so there's probably no reason for is ever to move to a post version 3 even if we keep on releasing minor releases every year to for the next 2 decades so here's a bit more history
and there's more than the features I talk about the release cadence posters went from that 2001 released when is kind of scratching our heads that this this might be interesting and useful and it's gone from a curiosity in the open source world to with industry standard the so
start off in 2001 to the 1st release in relatively short period time on the open source ecosystem embraces pretty quickly so that support from Google format to reduce hurt you just all pile on the approaches that usually source to have and by 2003 we get our sorry never 2003 we our 1st proprietary third-party product reading writing the posters that's the FME from say software members Colombia on and over the next few years course the open source projects continue pylon uses the default data source and we get a few smaller proprietary third-party people implemented so ionic enterprise category work but out of Belgium UK respectively I
2006 things such that got me a lot of criteria come companies coming in toward the end of this time was still the open-source coming up we get 1st sort of bolted on support misery and then and full support from his Yochai server the rest the proprietary the field starts to come on line that died is the 1st but then we get to get a MapInfo Intergraph towards the end a whole bunch of of small proprietary companies and by by the President this point is really nobody left so posters at this point is really
widely supported by GIS tools is supported by Open Source Geospatial tools and it's also supported by basically every proprietary J. has to and then is what is a slide I added this it's a really important thing now is developers start to build apps on top databases is now widely supported in deployable in different cloud environments
so from the start roku I'm using a post press databases been has been a supporter of of PostGIS an open shift from the start but most recently within the last year and the Web services added post rescue on posters to the RTS cloud service so it's very very easy now to spin up your own spatial database in the cloud and wireless
companies all supported coaches my theory is that it's because they believe in freedom and they want everybody to have as much free software as possible or it could be
because it makes the model we I and certainly we saw the dynamic of the small companies who 1st
adopted posters Sergio concept and had work for that was a differentiator the big except the big US companies didn't do it so when they added it was a way for them to go to customers who already started using posters and say hey look at us and we can handle these big guys and then give them access to more business will would have access to otherwise and that this trivial Small Business either are 1 of the biggest of the original doctors of posters was the idea and the French nationality and having support for posters give the small cell for companies a foothold going you know but then it's is kind of moved on it's
become a basic feature which is expected and so the big 3 as remapping photograph have had come on board as well because the customers have been saying they want but it's a demand is no longer optional it's required images to we better speak you better read write posters so who's
using both just add this point the customer list is legion like at Philip all slides and private sector side we've
got Debbie assign also known as the weather channel of New York Times runs posters behind the Jago instances info terror in the UK manages all the ordnance survey and map data at 500 million features in their posters instance Digital Globe Google both user to manage web-based imagery as CASE BAD ball systems defense contractors all using it here in the states of start ups like red owner are using it a lot and our governments side and national
levels the 1st national mapping agency I mentioned but also Portugal and Canada Sweden Denmark and regional levels like Minnesota prostheca back and local levels like peer County appears County see simple many more so it is also quite governmental of outfits like in rail and how can these these all use posters it so that the to the background now let's start the
friends and so of 5th and features so that they would know what the number 1 feature of hostesses guesses but the number 1 feature what is the most important feature of posters no so no at buffers data strings you people you lost your heart you lost your soul the most important feature of post this freedom of its liberty right this that just as spatial databases is a free as in freedom spatial database so it can be extended deployed in shared in any way you want not just the portal where is the ways I want but the ways you want to so what the 2nd most important feature of of posters and I alluded to it in my introduction and so you further no
Siegel movement perhaps they are the cool kids on the block I believe brew appear disappear cooler for this talk but I couldn't find any plaid a lot of for it but some other silly cool kid but the number 1 feature of PostGIS is this power equal that it puts the power hundreds of complex performance of my spatial functions at your fingertips through the powerful declarative language we call sequel it's of 4 GL so fourth-generation language used to care about these things and before I went back and started using third-generation language for everything I what can you do with you yes sequel database you can do amazing spatial
queries I'm at the number 1 feature for example suppose you had the customer Davis millions the customers of geocoded addresses suppose right and suppose that for marketing purposes you wanna know the income education level of your customers and couldn't point-of-sale say and your income education levels I and gather data that way but the customers probably would like it you can run a survey that the expensive but also be duplicative because the surveys or good run a huge expense of survey has already been run the income education level of customers Wal-Mart was called the US
census but the US Census in customer information tables don't have foreign key so if you deviate say well game that can do that but if your spatially say uh no problem we have a key that joins these 2 records it's where they are to assess how you get census information on to customer records you use that
universal key location every customer has a location every location balls inside a census tracts you can join them using a spatial relationship conditions and how the lines of code to the state you got something like
less you know that spilled a table that adds sensors athletes to customers and you can pipe that if you want to statistical analysis and get even more fine-grained answer the but not a great
feature posters you don't necessarily see it's hiding under the covers but the spatial tight the index is the fully integrated the PostgreSQL query plans but this is a big deal because it means that complex queries like this run the most efficient way possible and you can compare it to less efficient ways by comparing it by running it on a database that doesn't have a spatial implement the plan like my SQL and waterfall part we also have the basis the stuff which is defined by the simple features physical so you can use the
length the capital line 2 things distance for how far apart things d within a very important function for testing whether 2 objects are the radius of each other that areas the intersection true false is I can turn geometries in the text I can turn them into binary format do there a sequence lurk text into geometries msla binary geometries and but maybe era hipper than that they're using some cool new formats you can also slurp pushed
it out to G adjacent k analogy male or you can slurp in from came LG analogy adjacent to you also take geometries and make new geometries
take a small geometry make a bigger geometry so take a set of points make line the so take a cell lines make apologies they could settle whose make a different kind of a legalizing build Ariadne almost the same thing but not quite or set of polygons and melt them all together the 1 bigger polio
so for example building up of building a line from polygons that st make line takes an a rate of points a rose at appointed turns it into a line take that line is you pass it through the build area function and that spits out the polygon I'm so mad you walked around the lake with a GPS unit you get a whole bunch of time stamped points sort them by time turn them into line stuff them and build area do you got a policy just by walking on the edge of a lake or
suppose you have a map of all the counties in the States you wanted a map of all the states in the states just a symbol st Union I recall and out pops about states and this this function that used to be somewhat slower back in the old days maybe 3 4 years ago on under the covers there a lot of a very cool performance optimizations and hostess and when I hear someone come to me and say I I had this big problem has acted almost always the union problem but others should go up as well I had this big problem and I could write it in our GIS analyst for banging their heads against the table I I took the data from the my stuff in the posters I ran it ran in new 3 hours and 20 minutes to 3 minutes depending on the size of the dataset I go I know what happened there right what happened there was 1 1 of these quiet quietly
performance optimizations under the covers this is the original data set the cost cascade you need to be written and fellow gave us the dataset on the left and said but wrong you stupid software I wanna know that is taking forever the but to which we did not reply was probably a stupid data recent have and that's really awful data right and it's overlapping lots and lots lots is nearly coincident but not and but it drove us to sort of throw the profile and see where things are going wrong with the algorithm that it turned out is far more efficient ways to do union and we're doing it we're doing it 1 row at a time and we need to be doing is looking at the whole thing as a tree and not unique things what or ever order they happen to come to us but rather you need them in the order in which they adjacent to each other so this is a picture of what cascade union does under the
covers with a set of 1st Union's all things their neighbors to each other than it being in those neighbors to their neighbors in those areas of their neighbors and you get a final answer the result is that the states you really minimize the amount of new line work that's coming up the Non-Aligned work is always going down with each iteration of the sovereign so you don't have to take book keep all those vertices as the data gets smaller and smaller and that's you know what the function of union union generally throws away a lot of receipts you have less vertices no but they do in the input another I classic under the covers performance optimization people
don't know their link all prepare geometry you see it come the in the action when you start against intersects arrested contains a within or touches functions but not to
understand how works I don't understand how things like point polygon algorithms for example run but what they do how do you figure out what this point is inside the polygon and you be easier if there is a small alpha known inside a computer because they ask them but since there is you have to do it a step at a time computationally and what you do is you take your point news 1st say OK always pretend there's a stab into 31 direction and this figure out all the places that stab line has an edge I have it's not number of edges then it's inside a polyolefins even a registered outside the polio classical plus point point on operation I to do that no you have to interrogate every edge just in case I because you don't know whether it's Dublin onto you look at that which makes point polygon order and operations so the more edges you have the slower it is the and in the query on each separate point polygon operation even if the the polygon is the same each time requires checking every edge and polygon against the point nothing good looking at these edges over and over again you see the before we look at them again I'm got prepare geometry builds an index on the edges themselves and caches that index the 1st time a polygon holds it and the next time appalling rather interrogated edges it was the index set very quickly in log n time find which edges cross for big polygons of the results that this is a huge huge with so short story long long
story short prepare geometry makes repeated tests of large geometries very fast supplies that intersect history contains st within anything where a judge intersections come to play the medical featured people don't necessarily know about and we do have fully linear
referencing support in posters where referencing and if you're not in the GS field you might have heard of but it's a it's a funny kind of data model that's used a lot by people in transportation ministries of adverbs transportation they use it to manage their assets rather than necessarily keeping the precise location of a bridge they will have databases that say that particular assets are at a particular my along the highway so rather than storing both the highway geometry and the point young to they just all highly geometry any information about where along the highway the as it happens to be and for and for big things like bridge that seems kind of stupid no why would you do that but for a small thing to know about we have thousands thousand enjoyable the time like road signs or stops on these things I 1st of all the asset management systems to legacy systems you probably built a linear references and 2nd of all when you do use a linear referencing system gives you the freedom to alter your roads and chains the basic geometry without having to change every acid associated with it is change the base geometry all the assets go along for the ride so you can do points letter referencing you can do a layer of the masses of people in the fisheries domain upon that and this is really obvious where you don't want to store of multiple times to geometry so they'll say that this a particular reaches salmon habitat of any given chunk of stream can have tens or hundreds of these overlapping regions for different fish fish species are different observations taken at different times storing them all as independent it's a geometry makes the whole dataset unmanageable to ever want at the underlying streams if you had a just a reference to the streams and you can move the streams and all that all the re-segmented move right along with in the data so they're referencing functions the magic makes this happened not locate along function
which figures out how far along the line of point is a look at between is the same thing for segments ADD measure bad things like distance upstream or downstream distance along the highway to an existing the highway a stream and line look at point polls the point the imputed point off of a line segment given a particular measure along that 6 another function folks might come across that often is support for curvilinear features
curves are not defined in the simple features for sequels specification defined in the ISO C and specification which is basically a superset and SS equal and in our simple web-based online polygon world we can go over the the the the abstract pointless but if you're in a small world you probably do all the stuff a lot because engineers and use of CAD love to use circular arcs let's the make infinitely smooth curves with just 3 control points the end points and then the point where the curve has to go through the so the three year curvilinear types defined by ISO her compound curvature polygon are supported by posters and some utility functions these are actually sadly not not specify size over the incredibly useful I could take a circular ROC curve and stroke it into a line you can do the reverse with posters so you can take 1
of these which is perfectly circular but only has 3 control points 1 each and 1 mill and turn into the equivalent stress and magically they they collide 3 into the back into a circular arc representation using blind her rejection
of basic functionality it's not something curiously that shows up in sequel server they'd never chosen implemented but is an oracle and is and posters on the algorithm library underneath posters proj for which means to support incredibly large selection of
projections most which you probably never use but their case you need them if you're not in a projection perhaps you might be interested in
geography geography is a feature that was added 1 . 5 is the 1st the 1st new type since the dawn of the Donna posters back 1 . 0 jogger type models data on the sphere the so we understand the coordinates of geography not as X and Y but as stated 5 spherical geometry spherical coordinates and you were used to the
about the world graphs is the big square that rectangle a place mat but this flackery view would have a lot of problem cases but if you draw a square you know
polygon that covers the poles and then attempt to work with it in a plaque or a projection it'll come out like this it's that's
that's not workable but or if you're doing work in the 2 big ocean if you draw a circle around Hawaii and throat into a system that only understands plaque power it'll be understood as this this is also not optimal and this is not a problem which is restricted to pit the little open source uh database projects like ours it's a problem that
lots of companies have like Google Earth that's a polygon drawn across the and it so we built the dog kept fixed
at and what kind of people in the geography why do we build that go what use cases were were driving this this need there's really 2 kinds users and they couldn't be more different of the 1st kind user this geo newbies so the users through your web developer folks other folks the JavaScript folks humans and animals deprotection that I want understand bijections and geography lesson work with Latin on data without knowing objections but still get useful answers back the other category of users is really the exact opposite there that the the you UGC really big organizations they have truly global
data covers the poles that covers the date
lines and everything in
between but there's no map projection that works for them right they got over the pull the article over the lines they got the whole world to deal with so that makes sense so the implementation
geography currently only has support for a few functions but most importantly it supports the object in indexes and all functions work perfectly well over the poles of the decline so the indexes there's no hacks and forcing planar data in the circle that all the data is understood as objects interplay on the sphere so indexes you circle data on you can new intersects tests and distance on the steroid STD within true false Decimus 0 it area calculations on this period and because the fleet of functions that understand geography is so small and because the fleet of functions that used in geometry is so largely because many the function that is 10 geometry don't really care about whether you're on the sphere on the plane we also added casts to and from geometry so you could take a geography and push geometry Dusan geometry things on and pull it back to will have had access to the whole suite opposes functionality even when it is using the geography into .
1 I hadn't had a hand new job function that takes in a line like this so that has only 2 vertices because 1 has Paris Los Angeles and any densifies it which is really useful if you doing rendering on the plane if you drive is the Paris Las Angeles trip on the on the fly you get not correct trip that is not where the plane will go a plane will go and agenda in a geographic space so along the great circle edge so as you say what the geography densifies in spherical space this the 3 former densification and actually it's tackles reminder of how bias is planar projections are how biased repeater were applied for 8 years and you see the huge distortion of the north compared to the title dots the bottom each dot is 300 callers apart the all through comes apart so severe and experience of sitting on a plane with little map on the back of the seat right and in halfway through the flight the places with long you know real tearing underneath the goes Icelandic how good are almost there for some reason the plane just slows right down however this Scotland even over Scotland for ever what's wrong right and left address so 180 into posters
handy command-line tools prior to to 1 . 5 used at all exclusively with a command line tool like APT-SQL and we still have that you'll there because it's incredibly useful for automating data loading tasks lots of wonderful command-line flags of the ability to pipe the output structure directly into your peers built from a monitor we recognize that for all users is not necessarily the best way and there's lots of folks were fall under the command line it's certainly not good for teaching us so we added a gooey in 1 . 5 and we prove it
2 . 0 0 2 . 0 version includes ability to load multiple shapefiles in 1 batch so it includes the ability
to with this 1 this is important as even put them all in a batch of a young export now so you can go into your going to database find tables and choose what you want so there is a handle out tool for folks and I don't want what's around the command put is 2 . also had the idea of
type model which meant to constrain the kinds of geometry types in the tables using just the post press metadata system so prior to 2 . 0 you had to add a manually or automatically using utility functions constraints to the table now you don't have to add explicit constraints are implicitly constrained by the type definition you put in your table so when you create a table you say not only say geometry but you can also specify optionally jump type and spatial reference number that means that operations like changing the projection of a column and all the data in it have gone to from 5 steps pieces sequel to 1 step these a
sequel now you can change it in 1 step so all the table the column changes datatype 2 and this could include reproduction that could include transformations if you need to it could include stripping things in rather geometry and then using tells the system what function used to implement the transit the transition from what you type is now to what is specified to be the new tail for the new type because all this information about high-quality s related tight dimensionality is now stored in the post as metadata are 1 of the big pain points of managing post rescue or posters is gone away which is the
geometry columns which is used by lots of 3rd parties offer to figure out what is in the database there is no longer badly managed it's now system you so it's always up-to-date matter which create spatial tables on the command line but it's automatically today no need to go much of that so for example I went into geology columns and ask for the record for my database and my spatial table my
record this is what I get back so about plastic to he data but there is a whole
extra dimension of pushes functionality of the we go into the 3rd dimension it goes to 3 I she goes to 4 and so we got 3 types triangles Tim's
Polledo services and those 3 the types as we stop before you know you don't have a spatial database unless types indexes and functions 3 types of 3 functions that work on
those types of given to 3 objects how far they are part of how long the spaces in 3 space was the closest things between them and and input output format so given the input 3 D format like
x treaty on outputs reform Olympics D GML which understands 3 D and spit them out as output representation and then of course once you've got these volumetric 3 D objects in a database you need to build index
and you n-dimensional indexes if you use the in the geometry obs index type that means you can index of three-dimensional data and search it like this
I can also index for dimension that if you want to inserted with the triple % the search the that basic support when a 2 . 0 a few functions indexes the types in 2 . 1 of the good
folks at a lot of land you have been broadly not a functional support for the 3 D objects with be doing it by building in binding in the features of the seagull computational geometry library so got bounded Siegel and you can build enable posters is now that when you have single you get access to
all these other new types so you could take volumes and intersect those volumes you knew volume I can tessellate duty up to the object in extrude to the object and turn them in the volumes check the orientation of a face is important routing valid volumetric objects like even take polygons and turn them into straight line representation using skull ization which is useful for more things just reading In 2 point 0 1 of the big headline and the additional
features Rastas and artistry right up front wrasses incredibly stupid idea that and I said that for a long time and you can still get me to say it from time to time and it can still remain to be a stupid idea of DEC's out for a few use cases and and by few I mean you know a lot a lot a lot if you're doing the analysis there's no point in taking your imagery and stuff in a database and then pulling back at looking at it that is a waste of your time that is a waste database but if you're putting in in data not pictures but information wrasses the way way to represent continuous values surfaces like
elevation models for example so if I have the big coverage of elevation data I could model in my database as a big collection of little chips thereafter type
and I could do analysis against those chips I could you are after vector spatial joint analysis joined the vector table to the Rasta table do some calculations of the polygons against the rest as they happen intersect this is example I did I'm taking walking areas in the pink and free was lying areas were on steep slopes support environmental metric and they can summarize up into into 1 number you databases a disproportionate logging was on steep drops again all in 1 piece of sequel so you could do lasting stuff if you got
interest areas of interest and vector in and data in Rasta you could say take here vector data turn it into a mask and apply the
thereafter mass from the vectors onto the
rafters and summarize the bits that are left over so the common workflow of Reston database with posters another 2 . 0 feature it's been less widely used in Rasta is topology classic use case
typology is the land management where the boundaries of things determinative of parcels canasta administrative boundaries of the boundaries between 2 objects in those kinds of datasets and define the edges of the objects is no such thing as overlap in In parcels on ownership information there's also some cules also data processing use cases you can do with topology but
they still of use administration areas we got a bunch of administration areas he's once France and they are too complex to and simplify the if you simplify the model time these simplified junkies will not necessarily match up there was silly edge match because its implications independent of all the others so they might remove different vertices but the good and topological state where the boundaries are shared so simple features into a typology simplify the edges that
apology then can stand back noticeable features but the simple features will then have purposely coincidence edges which is nice 2 but also brought us next nearest neighbor
and for very large tables with a regular densities of index years there can be a huge performance wind and so here's an example there's 2 million years points poll better
Dunams and then did in nearest neighbor search 1 find all or find 1
point all points nearest to we Creek so find really the point we treat point then run
this be suspicious equal they get back the 10 years games and to recreate so the way do it is with a funny arrow funny bi-directional there'll that forces you into an index assistance sort basically on the table study that the things which are closest and then you have to have a limit so the order by force is going to be indexed and start returning things in in distance order but if you have a limit oldest return the whole table to you distance on and I will not be faster than the alternative but is it is really fast as is 2 million to input points of 10 nearest neighbor search by
milliseconds that's pretty good liturgy has always been a problem in posters it's easy to
generate invalid geometries and it's hard to fix them so police have hard fix them and different agencies different organizations a different understanding of what what is valid or not and you can represent for example the classic bow-tie polygon that touches itself at 1 point in 2 different ways represented as a single ring attaches itself or represented
as 2 rings where the whole touches the exterior at 1 point and sadly different company to realizations specify those 2 variance is valid as researchers the self that theory is valid and he sees says the ring touching the exterior is developed way for things to be so it's not uncommon to find different systems and the data which they do not agree on the degree whether development a previously we just tell you whether things valid not true or false was refined it was wrong we can do anything about it and then we had the
jury could tell you why I was wrong as little more useful so the user of the is valid reason function spitting back and tell you what's wrong these charges to the point of view were jump to model of energy when we finally added the 2 levels waiting for
the make valid function for almost all cases inability make valid will take the geometry terror apart put back together in a balanced and and validity is important because if you run analyses invalid polygons you'll either get the exception release tell you something went wrong or sometimes the worst case you just get the wrong answer and that's really not good so as always get tested for validity before you start running
analysis on I actually occur support also added in 2 . 1 support for exact distance computation on the curves and on because they've let myself and was fun fun to do math exercise my mouth brain have to put 1
added some Delaney triangularization this came the just library exposed it so you can take say this case is simple unit circle triangulated isn't like that and another hidden visual features but added to put 1 continues to be improved what mean to point to
the idea of interoperability but as end-user practice 1 database for person this doesn't matter was presented you have a database in multi-user situation you don't want a queries to go into a lot continuous running state we have to wait for the finish wanna build saying no no I didn't mean it stopped by this feature was added by Carter T. be running a suffers a service situation they had a huge need to build a stop running queries to give the user the chance they know staying too long interrupted a lot of new features into put 1 or about performance so Rasta to
Union got about 10 times faster route to native implementation of the geography distance functions intersects function G within functions role prove those my work are basically applying the same up appear geometry ideas that work in geometry to the geography type that got to 23 times faster depending on how big a inputs are dump points for people who are tearing apart geometries is the native implantation now 10 times faster was before on under the covers will never see at the archery algorithm got his splitter but it makes the building of our trees are marginally slower but makes the access of data from trees 23 per cent faster and we consider that a big win worth getting and then finally the indeed geography statistics of interim proved that made complex queries plan out between 20 30 per cent faster and react
new features coming so the stuff that's already been done in 2 . 2 of for users of Rasta Wikipedia retile that change the grid basis of a set of a set of rest chips or create overview given a set of arrested chips build up another step that is resampled Overview set although in database no more pulling data out resampling and stuff it back in by endowed Raftery objection directly the database see all of them is a huge library the 1st time the rotten the folks muscle and it will never be able to add a few of the functions don't even more the functions in the latest versions and then some graphic stuff like like Google include a polyline format GML support for curvilinear features so you can push a curve so I and then they
got you know stuff which hasn't been done but would might be nice to have maybe and but we don't know depends on when 2 . 2 comes out so sp just as new index type and stress could potentially give us a big speed increases 4 point datatypes and can induce recheck our nearest neighbor functionality to work against lines and polygons much more effectively I kind of have in my in my back pocket the idea to start working on spatial-temporal data more so better support for x y t data and and helper functions to make them easier it all work with that stuff Florida so so maybe maybe not for those features that were really comes down to is choosing your own
adventure the I am I choose my venture I you can add your features but we don't really know what should be done for a post is 2 point 2 until people come to us the use cases and say this is a new feature we want and I know where asymptotically approaching perfection but there's no doubt there are more features to be done
and all glory to the dodo would the there are people out there who can add those features for you if that's what you need the so I think that the ground a great time with posters so it's gone from a strange look curiosity to being industry standard it's gone from something which is difficult to use something which is easy to use hard to install easy to install flip the switch is turned on and no
matter what coaches has had a great 13 years and I think would really strong for the next 13 role again better William closer perfection of extreme much I've got many tenderers
for questions so thanks but the tho the please ask me questions tell me about features you want at the posters code is not infected by Chinese or Russian there are 6 meters and ultimate so looked at by the system there's a 1 of the pleasures of being relatively small community is that it's not possible to make that guarantee at the Our look that the so my life yes no but I of high under if polysemy before I am I had 1 question that came up on their prepared geometries do those survive transactions and cab is that on a per-transaction basis for transit panel with a lot of the but they only make sense from the point of view of someone you know there's going be the consistency and as long as your statements of big enough that it's good like right million independent where this as of some never find their down when I started doing baby had said about this a large number of of rows in that it actually is faster if you break them in tests you chunk 7 thousand instead of doing a million and a 1 at once or something so that's kind of that's part of why I was curious yet not overstatement the generally speaking the statement addresses thousands or millions of rows and that's where attention has benefit the question please well I I'm here only get the weights that try to deal every conference Dulcinea


  630 ms - page object


AV-Portal 3.21.3 (19e43a18c8aa08bcbdf3e35b975c18acb737c630)