PostGIS Feature Frenzy
27 views
Formal Metadata
Title 
PostGIS Feature Frenzy

Title of Series  
Author 

License 
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. 
DOI  
Publisher 
FOSS4G, Open Source Geospatial Foundation (OSGeo)

Release Date 
2014

Language 
English

Producer 

Production Year 
2014

Production Place 
Portland, Oregon, United States of America

Content Metadata
Subject Area  
Abstract 
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

00:00
State of matter
Reduction of order
00:41
Point (geometry)
Product (category theory)
State of matter
Multiplication sign
Energy level
Food energy
01:42
Web 2.0
Suite (music)
Building
Context awareness
Product (category theory)
Computer animation
Mapping
Open source
Multiplication sign
Projective plane
Number
02:29
Point (geometry)
Server (computing)
Context awareness
INTEGRAL
Geometry
Random access
Mereology
Usability
Number
Database
String (computer science)
Energy level
Hydraulic jump
Descriptive statistics
Airfoil
Product (category theory)
Polygon
Rollback (data management)
Basis (linear algebra)
Set (mathematics)
Line (geometry)
Functional (mathematics)
Table (information)
Flow separation
Subject indexing
Spring (hydrology)
Computer animation
Network topology
Personal digital assistant
Data type
Row (database)
04:27
Point (geometry)
Standard deviation
Serial port
Sequel
Namespace
Polygon
Line (geometry)
Functional (mathematics)
Metric tensor
Category of being
Computer animation
Internet service provider
Operator (mathematics)
Database
Buffer solution
Data type
Subtraction
Error message
05:09
Server (computing)
Sequel
Code
Connectivity (graph theory)
Data recovery
Bit
Line (geometry)
Mereology
Functional (mathematics)
Video game
Database
Blog
Right angle
06:03
Classical physics
Dataflow
Sequel
Information
Length
INTEGRAL
Code
Electronic mailing list
Line (geometry)
Cartesian coordinate system
Functional (mathematics)
Table (information)
Usability
Category of being
Computer animation
Term (mathematics)
Query language
Database
Bus (computing)
Statement (computer science)
Moving average
07:11
Revision control
Point (geometry)
Computer animation
Multiplication sign
Database
Keyboard shortcut
Bit
Information technology consulting
Physical system
08:24
Area
Computer file
Information
Scientific modelling
Polygon
Theory
Table (information)
Mathematics
Computer animation
Network topology
Bit rate
Software
Query language
Data storage device
Database
Right angle
Traffic reporting
Data type
Subtraction
09:47
Area
Covering space
Sequel
Software developer
Geometry
Mathematical analysis
Weight
Functional (mathematics)
Entire function
Attribute grammar
Subject indexing
Latent heat
Computer animation
Database
Interpreter (computing)
Website
Object (grammar)
Data type
11:33
Revision control
Message passing
Perfect group
Mathematics
Computer animation
File format
Data storage device
Core dump
Bit
Resultant
12:23
Enterprise architecture
Default (computer science)
Standard deviation
Product (category theory)
Open source
File format
Chemical equation
Projective plane
Source code
Disk readandwrite head
Category of being
Frequency
Software
Reading (process)
13:23
Point (geometry)
Slide rule
Server (computing)
Building
Mobile app
Open source
Multiplication sign
Software developer
Point cloud
Line (geometry)
Field (computer science)
Computer animation
Integrated development environment
Database
Quicksort
Subtraction
14:17
Shift operator
Web service
Computer animation
Database
Scientific modelling
Dynamical system
Cloud computing
Wireless LAN
Theory
14:58
Medical imaging
Digital photography
Computer animation
Differential (mechanical device)
Cellular automaton
Fiber bundle
Whiteboard
15:50
Point (geometry)
Slide rule
Dialect
Mapping
State of matter
Electronic mailing list
Ultraviolet photoelectron spectroscopy
Archaeological field survey
Instance (computer science)
Web application
Computer animation
Personal digital assistant
Energy level
Data management
Physical system
16:52
Complex (psychology)
Sequel
Block (periodic table)
Database
String (computer science)
Equaliser (mathematics)
Buffer solution
Functional (mathematics)
Declarative programming
Number
Formal language
Power (physics)
18:41
Goodness of fit
Information
Key (cryptography)
Query language
Archaeological field survey
Energy level
Right angle
Game theory
Address space
Number
Row (database)
Table (information)
19:37
Covering space
Key (cryptography)
State of matter
Code
Basis (linear algebra)
Physicalism
Planning
Line (geometry)
Mereology
Table (information)
Subject indexing
Uniform resource locator
Computer animation
Query language
Database
Universe (mathematics)
Condition number
20:36
Area
Point (geometry)
File format
Length
Cellular automaton
Geometry
Binary code
Line (geometry)
Set (mathematics)
Distance
Binary file
Sequence
Functional (mathematics)
Radius
Computer animation
Analogy
Software testing
Object (grammar)
21:32
Area
Point (geometry)
Covering space
Building
Mapping
State of matter
Multiplication sign
Polygon
Line (geometry)
Disk readandwrite head
Functional (mathematics)
Timestamp
Symbol table
Table (information)
Computer animation
Bit rate
Mathematical optimization
Units of measurement
22:52
Covering space
Area
Algorithm
State of matter
Multiplication sign
Vector graphics
Set (mathematics)
Functional (mathematics)
Software
Network topology
Order (biology)
Vertex (graph theory)
output
Right angle
Iteration
Mathematical optimization
Resultant
Row (database)
24:15
Point (geometry)
Classical physics
Group action
Algorithm
Direction (geometry)
Multiplication sign
Geometry
Polygon
Line (geometry)
Functional (mathematics)
Computer
Number
Subject indexing
Computer animation
Linker (computing)
Personal digital assistant
Query language
Operator (mathematics)
Touch typing
Order (biology)
Figurate number
Resultant
Alpha (investment)
Separation axiom
26:00
Point (geometry)
State observer
Domain name
Transportation theory (mathematics)
Multiplication sign
Geometry
ACID
Streaming media
Mass
Field (computer science)
Chaining
Sign (mathematics)
Bridging (networking)
Database
Software testing
Subtraction
Physical system
Dialect
Multiplication
Information
Functional (mathematics)
10 (number)
Data model
Uniform resource locator
Absolute geometry
Computer animation
Data storage device
Linearization
Species
Data management
28:10
Point (geometry)
Curve
Game controller
Curvature
Sequel
Curvilinear coordinates
Equaliser (mathematics)
Polygon
Line (geometry)
Streaming media
Differentiable manifold
Distance
Measurement
Functional (mathematics)
Subset
Web application
Latent heat
Utility software
Data type
Arc (geometry)
Reverse engineering
29:33
Server (computing)
Algorithm
Sequel
Projective plane
Spline (mathematics)
Functional (mathematics)
Computer animation
Stress (mechanics)
Personal digital assistant
Representation (politics)
Arc (geometry)
Oracle
Library (computing)
30:16
Scientific modelling
View (database)
Polygon
Projective plane
Rectangle
Graph (mathematics)
Sphere
Spherical geometry
Personal digital assistant
Square number
Moving average
Data type
Pole (complex analysis)
31:00
Hidden surface determination
Open source
Latin square
Projective plane
Polygon
Geometry
Power (physics)
Category of being
Computer animation
WebDesigner
Personal digital assistant
Database
Selforganization
Circle
Bijection
Object (grammar)
Physical system
32:08
Area
Suite (music)
Implementation
Mapping
Projective plane
Geometry
Line (geometry)
Distance
Sphere
Functional (mathematics)
Planar graph
Subject indexing
Calculation
Plane (geometry)
Computer animation
Hacker (term)
Software testing
Circle
Object (grammar)
Pole (complex analysis)
33:31
Spacetime
Great circle
Mapping
Projective plane
Line (geometry)
Function (mathematics)
Functional (mathematics)
Peertopeer
Plane (geometry)
Process (computing)
Computer animation
Vertex (graph theory)
Flag
Right angle
Data structure
Address space
Distortion (mathematics)
Task (computing)
35:17
Batch processing
Constraint (mathematics)
Sequel
Scientific modelling
Geometry
Projective plane
Functional (mathematics)
Metadata
Table (information)
Number
Revision control
Computer animation
Database
Operator (mathematics)
Utility software
Data type
Hydraulic jump
Physical system
36:34
Point (geometry)
Group action
Sequel
Transformation (genetics)
Geometry
3 (number)
Functional (mathematics)
Metadata
Table (information)
Mathematics
Computer animation
Database
Data type
Empennage
Physical system
Row (database)
37:37
Threedimensional space
Spacetime
File format
Function (mathematics)
Mereology
Functional (mathematics)
Subject indexing
Web service
Computer animation
Hausdorff dimension
Database
Triangle
output
Object (grammar)
Data type
Row (database)
38:19
Threedimensional space
Geometry
Computational geometry
Function (mathematics)
Functional (mathematics)
Subject indexing
Computer animation
Database
Hausdorff dimension
Representation (politics)
Object (grammar)
Data type
Library (computing)
39:15
Point (geometry)
Information
Orientation (vector space)
Multiplication sign
Surface
Mathematical analysis
Volume (thermodynamics)
Line (geometry)
Computer animation
Personal digital assistant
Database
Representation (politics)
Object (grammar)
Decimal
Analytic continuation
Data type
40:27
Area
Sequel
Scientific modelling
Polygon
Mathematical analysis
Drop (liquid)
Number
Table (information)
Calculation
Computer animation
Vector space
Database
Data type
41:09
Area
Classical physics
Information
Determinism
Bit
Mass
Computer animation
Vector space
Network topology
Personal digital assistant
Database
Boundary value problem
Object (grammar)
Data management
42:00
Area
Point (geometry)
State of matter
System administrator
Multiplication sign
Scientific modelling
Independence (probability theory)
Regular graph
Table (information)
Subject indexing
Population density
Computer animation
Vertex (graph theory)
Boundary value problem
Matching (graph theory)
42:45
Point (geometry)
Observational study
Forcing (mathematics)
Limit (category theory)
Distance
Table (information)
Subject indexing
Exterior algebra
Computer animation
Personal digital assistant
Order (biology)
output
Arrow of time
Game theory
Quicksort
43:31
Point (geometry)
Classical physics
Electric generator
Software developer
Geometry
Polygon
Variance
Ring (mathematics)
Theory
Degree (graph theory)
Computer animation
Touch typing
Selforganization
Subtraction
Physical system
44:30
Point (geometry)
Validity (statistics)
Computer
View (database)
Scientific modelling
Polygon
Geometry
Mathematical analysis
Distance
Functional (mathematics)
Food energy
Mathematics
Computer animation
Personal digital assistant
Energy level
Hydraulic jump
Exception handling
45:22
Teilnehmerrechensystem
Web service
Personal digital assistant
Query language
State of matter
Database
Triangle
Unit circle
Library (computing)
46:10
Point (geometry)
Statistics
Building
Implementation
Multiplication sign
Geometry
Distance
Revision control
Database
Core dump
Covering space
Curve
Algorithm
File format
Curvilinear coordinates
Diffuser (automotive)
Basis (linear algebra)
Set (mathematics)
Functional (mathematics)
Computer animation
Network topology
Query language
output
Object (grammar)
Routing
Data type
Library (computing)
47:45
Point (geometry)
Adventure game
Subject indexing
Perfect group
Stress (mechanics)
Personal digital assistant
Polygon
Line (geometry)
Data type
Functional (mathematics)
48:53
Point (geometry)
Database transaction
Standard deviation
Group action
Perfect group
Code
Multiplication sign
Consistency
View (database)
Geometry
Basis (linear algebra)
Extreme programming
Weight
Mereology
Number
Video game
Computer animation
Statement (computer science)
Software testing
Address space
Physical system
Row (database)
00:01
I a few so this is the most is the sheer frenzy of
00:11
like to look at the ownership of the friends the concept and and the frenzy
00:17
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
00:43
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
01:00
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
01:10
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
01:44
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 selfserving things I'd like to preface then tie with they had no toad all glory to the building I I work workbound
02:16
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
02:31
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
03:04
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 Btree 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
04:29
set up by the geospatial consortia the simple features for sequel standard the and what civil features physical tells us is that
04:37
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
04:54
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
05:12
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
05:35
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 oneline right
06:05
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
06:15
question how far the bus travel last week to take into that I think I think 1 line just a simple
06:23
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
06:31
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
06:44
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 generalpurpose sense on out of history where they come
07:13
from the approaches came from originally a company that I founded in Victoria British
07:19
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
08:27
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
08:56
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
09:48
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
10:08
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
11:33
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
11:51
. 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
12:25
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
12:41
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 thirdparty 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 thirdparty people implemented so ionic enterprise category work but out of Belgium UK respectively I
13:25
2006 things such that got me a lot of criteria come companies coming in toward the end of this time was still the opensource 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
13:56
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
14:19
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
14:41
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
14:52
because it makes the model we I and certainly we saw the dynamic of the small companies who 1st
14:58
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
15:33
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
15:51
using both just add this point the customer list is legion like at Philip all slides and private sector side we've
15:59
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 webbased 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
16:27
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
16:52
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
17:55
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 fourthgeneration language used to care about these things and before I went back and started using thirdgeneration language for everything I what can you do with you yes sequel database you can do amazing spatial
18:42
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 pointofsale 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 WalMart was called the US
19:16
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
19:39
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
19:52
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 finegrained answer the but not a great
20:04
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
20:38
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
21:03
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
21:14
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
21:33
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
22:03
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
22:53
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
23:42
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 NonAligned 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
24:15
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
24:29
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
26:03
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
26:19
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 resegmented move right along with in the data so they're referencing functions the magic makes this happened not locate along function
28:12
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
28:39
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 webbased 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
29:34
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
29:52
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
30:06
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
30:17
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
30:43
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
30:53
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
31:03
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
31:25
lots of companies have like Google Earth that's a polygon drawn across the and it so we built the dog kept fixed
31:35
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
32:11
data covers the poles that covers the date
32:13
lines and everything in
32:15
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
32:27
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 .
33:31
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
34:52
handy commandline tools prior to to 1 . 5 used at all exclusively with a command line tool like APTSQL and we still have that you'll there because it's incredibly useful for automating data loading tasks lots of wonderful commandline 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
35:20
2 . 0 0 2 . 0 version includes ability to load multiple shapefiles in 1 batch so it includes the ability
35:29
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
35:54
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
36:36
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 highquality 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
37:16
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 uptodate 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
37:37
record this is what I get back so about plastic to he data but there is a whole
37:44
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
37:55
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
38:06
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
38:19
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
38:33
and you ndimensional indexes if you use the in the geometry obs index type that means you can index of threedimensional data and search it like this
38:44
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
38:58
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
39:16
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
39:42
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
40:27
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
40:39
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
41:12
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
41:20
thereafter mass from the vectors onto the
41:23
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
41:35
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
42:00
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
42:26
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
42:37
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
42:47
Dunams and then did in nearest neighbor search 1 find all or find 1
42:52
point all points nearest to we Creek so find really the point we treat point then run
42:58
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 bidirectional 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
43:32
milliseconds that's pretty good liturgy has always been a problem in posters it's easy to
43:38
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 bowtie polygon that touches itself at 1 point in 2 different ways represented as a single ring attaches itself or represented
43:59
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
44:31
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
44:44
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
45:08
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
45:22
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
45:38
the idea of interoperability but as enduser practice 1 database for person this doesn't matter was presented you have a database in multiuser 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
46:12
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
47:01
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
47:47
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 spatialtemporal 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
48:33
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
48:53
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
49:21
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
49:31
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 pertransaction 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