Logo TIB AV-Portal Logo TIB AV-Portal

PostGIS - Store and Process Geodata in a Relational Database

Video in TIB AV-Portal: PostGIS - Store and Process Geodata in a Relational Database

Formal Metadata

PostGIS - Store and Process Geodata in a Relational Database
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
Miksch, Jakob
Production Year
Production Place
presentation files forces time Relational Databases computational Databases processes Menu
presentation files formating workstation machine survey Databases Databases help shape terms computational versions Types terms website viewer statements data types
point interpolation functionality building servers files multi-user calculation Databases regular computational queries management geometric mechanisms Types different operations Indexable processes extent multiplication multiplication Databases bits applications queries Indexable Location processes case output calculation orders HDDs sort interpolation table data types result geometric spaces
point functionality views calculation part distances morphological theories training versions geometric Types real vector area email relation polygons sampling coordination plan bits lines plane real vector raster output calculation data types geometric Distorted
point polygons Serialization randomness randomization generate table point geometric sample testing key table
point sample polygons table queries result queries
point polygons geometric polygons workstation selection applications queries result queries
polygons geometric mapping terms predicate polygons queries
point polygons geometric bits part powerful queries
presentation services files Office shape queries
polygons links generate links Databases stacks queries queries geometric software software sort tasks
Relational Databases computational processes
alright so it's time for a lot of presentations and thanks for the introduction of as a force that it's about post years for storing your files menu data and spatial database in general and actually we're gonna talk
about databases and data types I'll show a little practical example and then we'll go over to the summary so then not the biggest agenda actually 1st of
all high when nice the I make this presentation I wasn't sure what the audience would would look like so it seems like we have a fairly experienced audience in terms of spatial data handling but maybe as a loose survey in the beginning the crew of viewer has never has successfully typed in a post GAS querying and executed on a computer and that's a draw a OK no money so everybody here has at least passed for most yes so then maybe for some of you things might have of the trivial but nevertheless I'm gonna introduce you to the station databases maybe as a little under gold and distant she's fighting must is of is the the websites is that creating that's created by some guy who doesn't like the shape file format this actually because to combine issues that we touched on before namely what Stephon Maneka showed the because 1st of all said file is horrible format and because of this is multiple files sitting around on your computer and and it's very disorganized and you also have no real way of versioning it's and if you change something then people we have seen that in practice people tend to clutter up the entire machine would with version underscore 2 final blah-blah-blah and create a little help and I want to help you out of this land and in the end because there will be some like simple copy and paste commands that you can try it yourself at home and if you've never done it but you have and I encourage you to try and play with which post yes and see what it can do for you so 1st of all just
really you basic from beginning why should you use a database to store and organize your data anyway fair question you could just put it in a file and habits it on your computer and 1st of all it's a it's a bit more reliable in general and because databases have mechanisms built in that ensured that your data is consistent with that certain them in yeah that's certainly backup mechanisms can be executed really easily so it's it's built through to store your data in a reliable and secure fashion and you can have multiple users accessing and managing your data and setting them up is so this really easy as well and it can easily live on a server over multiple people have axis so that's also something that's will close to impossible already would would just regular files you can build stuff like in users and that can speed up the processes that means that you're basically sacrificing a bit of space on your hard drive in order to pre- or some of the data in your database and then you can find data later on them quicker sometimes you maybe complex queries and because you don't always when you retrieve your data you don't always want like all of the data that you have the return back to you but you wanna refine your results may be by some and maybe by some and predicates that you can put on the data and those who queries you that you build it they can and get a really long and complicated and where if you use a database you can easily stored out you can repeat queries that did before so it's rooms it's easy to to replicated in your own AlDS queries and you can extend them them and also because of all of the a lot and if you want to work with data a lot you hope hopefully you try to automate a lot of you toss and that's also for for many things like at at a quality checks and 1 them is very easy if you use databases but for many applications no why should use not only databases about spatial databases a physical because all of this and and 2nd of all if you use of spatial database you get 1st of all special data has that out of the caption of but later you get spatial functions that allow you to use spatial operations on your data and because this is in like Hmong mainly focused on yes if you will look around in your database and look in the functions that you have if you have the posters extension installed you'll will find a lot of St underscore whatever functions and they're not exclusively called as the underscore but but also others that those are mainly spatial type and see if the and operation so can in case you ever wondered why it's always best he'd well I did at some point well what can you do if you use a spatial database you can have your queries based on on the location of the data and so for example 1 what other restaurants near me that school in a spatial database fairly easy question to answer but if you don't have a spatial data type and that's really hard you can do all sorts of school geometry calculations or interpolation and all kinds of GIS and functionalities basically yes on top of that you have spatial indexing which is like a regular indexing but in space shakes a call of and you've got spatial joins us slow like now so like you could use some regular joins in order to to request data from different tables you mean and 1 based on connecting future more attribution and you have a a special joint as well that allow you to use the location of your features found that can be fairly handy as well I mentioned spatial data types that
the so post yes some of us both vector RASTA support and I think it's mostly being used for that because I think specific more more specific ones for for us the data not so sure about that and the true they wanted fairly often encounter geography and geometry so on they do roughly the same so there they are not bound to or to any of the symbol of and to any 1 of the simple features like for example of points lines polygons already more fancy combined versions of the and once and but and they are mainly used in training posted in most yes some tables and stay differentiated so far that the geography datatype actually does calculations atmospheres and that comes with a bunch of implications so it's not as then and in as many functions implemented as geometry did not have I think basically all of the EU as underscore functions work with geometry not entirely sure if that's really all but most of them and only a few of them at this point but I think it's getting more and more of working with their geography data types and that of course leads to an inch yes this 1 you removed your view of the really it depends of bit the I know of this and and I would say the new it's really was like if you have if you have for example shipping relative from Europe to the U.S. then curvature really does a lot to your distance between the 2 points because you know if you just have it on a plane and coordinate system then you get a fair bit of distortion there so the smaller you target areas the less critical it is only for geography and also sometimes it's just a question of practicality so if you've got like a billion points and you 1 of these look if they're in in some area and look at that they sample later on and then it's just awful e-mail expensive computationally too used geography and that's always a bit of a tradeoff in general I would recommend to when you can use geography but then you should 1st recheck if none of the functions in your workflow and morphology of functions in the workflow can also deal with that formant question yeah that was already in the the theory part and I
just a tiny bit of a
practical so so if there were people in this audience who have never seen SQL queries that's when it looks like it's just not wholly earlier complicated is 1 of them it is here we just generate a bunch of some data town with points in it the 1st to create a table that can house some points Scott test point and this is actually bodies in body more experienced people among this is what I use very often to just generate random point data and because the needed for some tests and as I think does nothing premade in there so you can just if we just copy that I think it's not on the guitar revoke of lepton and so we insert brain basically a bunch of have a thousand points in this in this table may and not a table with 1 molecule in it who knows
what it is Switzerland
and that is what it looks like the betterment of points and we get we get a polygon and is based on that I will just show you some very very basic features but it is never seen that before you will be amazed the so 1st of all we
have a simple non spatial queries so we select that how many of them features are in that table which the ID greater than 200 so they have 2 serial ID from 1 2000 and big surprise it's 800 and he is what the result would look
like so distal this and on a polygon knowledge but here in in Orange you see the selected ones and blue and once and that's it so it's pretty just a random selection
Guzik again have the station queries and now we count how many of the points are reading the polygon so we're doing a spatial drawing which I mentioned earlier is useful for some applications we get a result and it would look like this
on a map and now in terms I'm not expecting any of you to
fall over backwards from your chairs grew from the amazement but we can combine these 2 and you actually count how many visits the 1st predicate which is they are we that polygon and also the predicate ID over 200 and we get a few less of course
looks like this is a little bit so this is not the amazing part but it should be
painfully obvious at this point that you could extend this to however complicated you want that to be and the power of doing that and storing that and repeating that that is actually 1 of the big leverages and that I wanted to show it to you
with this presentation so
very short summary please do use especially reasons officers search services in but because if we are collaborating then I don't have to deal with shape files that you sent me for example is also nice for me and I was like really tried to encourage you to try this at home so you can just copy of all of these
commands here place them in
your spatial database and it
will run the exact same queries and would just work like that so all the data generations in this as well in his links to the software you need documentation or other things this is a very nice community would have all sorts of created tasks at my website or if you interested in what else I do all and that was it already thanks to you
and to the organization and organize us who spent his questions feel free to just that