Bestand wählen
Merken

Mending Spatial Data with PostGIS

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Beta
Erkannte Entitäten
Sprachtranskript
so all of so
on and on and on and on and on and on and on and on the left and right and the right eye and stst up at the back of the head of a uh a stone on me on on the on the right hand side of the cell and mind stst up at the back of of the head of the of the uh the story on on on the on the on the that the but
the In this the end of of the of the of the it so we polar body I'm gonna go ahead and get started on my name is Leo my words were 3rd on cooperation on retain as mine my wife and we are co-authored a book called posters and actually if you haven't bottom yet look for and by the 2nd edition of what I want to discuss today is really fixing some faulty geometries and the motivation for this came from me having to do some work in my local neighborhood yes of not knowing that not know slight if poem called the number so is it the motivation is from army having to take some data from local from my local neighborhood and presented to a group of important people who also live in my neighborhood and I could not use google data out so I used of OpenStreetMap so I pull down the records for the neighborhood in question and what I saw were upon irregularities shows a on problems with geometries of better contributed by people whether they be um missing streets intersections that in really quite that intersect and issues like that so what I have to manually do was of fix up the data so at the very least it would be presentable on it's quite easy to see if you're in the same neighborhood as the people they are presented to the industry has fallen off for 9 the right of so I'm going to start with just very simple
geometries all I'm not even were about spatial reference or anything I'm going to take some simple cases and show you some the functions that you can use and PostGIS to make a simple fixes to roles so it me open up my poster its environment I have the local database firm the 1st thing I am going to show you is simply a series of lines string it's better incitements along the along the diagonal of the Cartesian coordinate if I and so I have 4 separate lines strings let's say that each person each different person put on a different segment of the stick but they lived and I end up with the 4 different records in mind geometry table so I kind of go like that because of the as you can see and so right now I've created the the table with the 4 records and let me just show you that
in open jump and the number of the and so these Welch the OK
so in this simple example you have say for people who put in in 4 Sept separate lines streets so I find this annoying because of verses for records and its corollary the same rolled so I'm going to take some steps to clean this up the yeah the 1st thing I'm gonna do is I'm going to the but I'm just going to do a simple union and when you do this it will collapse down the 4 records into 1 however it will produce a mole tideline straight so we're down to 1 record but now instead of a regular simple geometry where we end up with the mall time geometry alright so I'm still not happy so I apply another function here the why merge and the what line March will do is here we will merge on the points below my multiply string together it will pass they contend to end if they come out to be a single line string it will reduce my mall tied down to a single line stream in in this case as everything lines up on the same road I do get a very clean results that to online strain with 0 1 2 3 4 5 points still are not happy because this is the road it's a straight road and we all know that we only need 2 points to define this at any given straight line segments so I apply simplified and I am down to my final result which is just 2 points and negative 2 and to to the family me lunch I will show you got the the you what think of the text the the long the 1 final line string now I am happy so I go like this uh um and the next example well I have my strains with people who put in different segments overlapping so you have here and clandestine who of who basically putting the same interest and this simplification method will take care of it to settle mean had to show you this we In the textual form and I'm going to positronium I the because so the this is a case where you have multiple people putting different segments of the same street I year ran
into the people who put in reversed people who the if they're taking the TPS and there the they're trying to identify the street that they live on some person may start from 1 angle to the other or vise versa and so then I get these results and so when searching came in it will still work of the OK the only thing you have to watch for when people put in these reverses or if they're one-way streets but what if they're not one-way street you don't really have to worry about it and you can use this simplification and get them to 1 line straight and then finally and I'm either going to show you that this works on you can just has all sorts of the crap and here you could have a lot any segment along those lines string as long as everybody filled up the whole lies that you're trying to cover you can get them down to 1 simple Langston with two M points the the they will jump down to the the missing misplaced intersections so the when we showed you what this looks like thank you the because focuses on some of what I'm trying to say here is I have 4 streets they intersect they should intersect at the origin 0 0 however they don't and I will show you the picture when the solution of a of the we get rid of the other layers the the OK so this is where I got this is the misplaced intersection the intersections should really be at the origin so to fix this of I'm going to use this map function the slack function will take A . 1 geometry and moved that point to the point on another geometry the 1st geometry is the um what we call the the input geometry and then the 2nd 1 is the reference geometry so in this example overtaking Paul for roads and we are going to use Snack the to the point 0 0 the the 2nd thing the so that's in a new table called 31 and let's take a look at that we book and so you can see this is the revised geometry where we moved all the roads by snapping the M points which is right here back to the origin the the next example which I had a lot of a because the snap has the snap has this value here it says to snap only within 1 unit measure in this case is just Cartesian that's what that that's a good point and if you if you set this to something that's too high you could still end up with something from the so to do all these faces II of ice that you do it on a sort of a case by case 1st and if it seems like that it's working for some and you're too tired fixing all the roads in the city then run the city
and you made over fix but of of 1st eyeball and tried on few cases the the next 1 is pretty common this is a case where the roadstone meet when they should so we wrote should me at any in this case at T intersection but it doesn't say this sample already I have tell the we and OK so this is the for whatever reason that that the person didn't get the same coordinate here as some somebody that marked up this the and so you have this gap there's a few ways to fix this but none of them are gonna be perfect sense you're going to make an assumption about what goes into here the easiest way and the only way that I'm bothered way of the is to use the shortest line between this line strangest string so this will have on filling the space the the like that that's the shortest line so that will between the 2 geometries it takes the endpoint and then dropping the perpendicular down to find the shortest line there's another way that's very similar which is also to snap the so let me curious about this layer and if you that the intersect at slant parameter to worlds and the OK not but that's probably setting it to as you as you mentioned i I set the parameter to high and its snapping the wrong point so let me see if I can get it right the the and OK no doesn't look like this is and look like I'm going to be able to compute this by Paul wise so but there are 2 ways to do it 1 is to use the as shortest 1 strain the other 1 is to find the closest point on this line strings to this line string and snap this point to it and then you can also do the overstepping case which is just take some point on this road and snap it you to the closest point on so I mean these are not the perfect solutions but you're not going to go out there and survey the area you need to make this presentable and believable to someone who was tracking down this road OK so so how to put this into practice the the work by hand the Our system from OpenStreetMap data that I pulled of open straight lines points of polygons and wrote in this case only the lines monocyte pulled section of upper Manhattan and we show you what that looks like the 1st thing that I found in of the the people and the the in
London who and you about the
the day I and that's the because of this this is a section of the the per hand up there are some problems with it some of the problems I introduce for the sake of example so I want you to notice this this little red thing here that this is actually somebody put this into OSM and I'm not quite sure what with that inspectors on the 2 the other thing I want you to notice is here this Cooper straight this compressed Fisher go all the way down and then finally this intersection here is all it's going into houses OK so applying the technique and graduate or ones we in
the for so this is just the exactly the same thing as what I did for the simple geometry example
and pretty much in
the and for this spending to log the book the church and its use we
to OK I intentionally put in our pair or fixed for pace and I oversimplifying this pace avenue to show you what happens when you person plify it turns into a straight line instead of keeping on the other kinks in it but other than that you can see but I think so this intersection it's not perfect the Cooper Street I call this the compression extensions and that continues and over here the I snapped this extraneous red part of Henry Hudson back into this piece and that is it for this presentation the fact that the questions also in same you a lot all the so you you said it was exactly the same when you applied at scale too many lines strings and many polygons but actually you have a few interesting sequel Construx so maybe just for review as well as simple people you could you could look at a few of
those like you you have those nice with the alcoholic showing you have some subselect you know just some of those things OK so up I will do it is still the same as far as the simple Cartesian geometry versus the OSM the only thing different with the OSM as I've named them and that I've deleted it the old geometry so that they're not sitting on top of each other OK so the with
i let me just go back a simple with with this the c on in the new city construct which allows you to basically means sub-queries in sequel I I love it before I would have to have to write this as 1 big select but now I can break it into 2 separate so selects name them mn s and then do my joined here so so much cleaner the only thing you have to watch for when you do it this way is that these will materialize in in the sense that these queries will run so the in these sub queries are going to generate a lot of results that you never know what the you no longer and me in the main query you probably shouldn't do this but otherwise it's a greater syntax and I proper use it more than I should a 1 so that's the with and than about everything else some the cross drawing for those of you that's not familiar all that does is take all the the rose from 1 table and creates a Cartesian coordinates the core of a Cartesian joint with the 2nd table so if you have 3 rows in Table 4 rows in Table B your across trying basically creates cough roles this In this case they're all single roles and I'm just matching them together 1 by 1 so that I of sites but otherwise it's all deletes a inserts and all the incident elites are really just to clean up the table so that I can show them to you nicely on the home in open jump any other questions OK the thank you the the
Systemprogrammierung
Hypermedia
Rechter Winkel
Zellularer Automat
Computerunterstützte Übersetzung
Räumliche Anordnung
Computeranimation
Schreib-Lese-Kopf
Trennungsaxiom
Nachbarschaft <Mathematik>
Lineares Funktional
Subtraktion
Konvexe Hülle
Datenhaltung
Reihe
Stellenring
Gruppenkeim
Zahlenbereich
Kartesische Koordinaten
Zeiger <Informatik>
Räumliche Anordnung
Computeranimation
Open Source
Datensatz
Einheit <Mathematik>
Rechter Winkel
Wort <Informatik>
Diagonale <Geometrie>
Programmierumgebung
Gerade
Tabelle <Informatik>
Zeichenkette
Resultante
Trennungsaxiom
Lineares Funktional
Dualitätstheorie
Transinformation
Punkt
Familie <Mathematik>
Zahlenbereich
Einfache Genauigkeit
Räumliche Anordnung
Computeranimation
Arithmetisches Mittel
Streaming <Kommunikationstechnik>
Bildschirmmaske
Wechselsprung
Datensatz
Einheit <Mathematik>
Gerade
Zeichenkette
Resultante
Parametersystem
Lineares Funktional
Punkt
Winkel
Physikalisches System
Ein-Ausgabe
Polygon
Räumliche Anordnung
Raum-Zeit
Quick-Sort
Computeranimation
Mapping <Computergraphik>
Perfekte Gruppe
Einheit <Mathematik>
Flächeninhalt
Einheit <Mathematik>
Offene Menge
Stichprobenumfang
Garbentheorie
Einflussgröße
Gerade
Lie-Gruppe
Zeichenkette
Tabelle <Informatik>
Garbentheorie
Computeranimation
Eins
Einheit <Mathematik>
Räumliche Anordnung
Computeranimation
Zentrische Streckung
Mereologie
Fortsetzung <Mathematik>
Kombinatorische Gruppentheorie
Maßerweiterung
Quellencodierung
Polygon
Gerade
Computeranimation
Zeichenkette
Resultante
Einfügungsdämpfung
Subtraktion
Web Site
No-Free-Lunch-Theorem
Einfache Genauigkeit
Abfrage
Fortsetzung <Mathematik>
Räumliche Anordnung
Inzidenzalgebra
Computeranimation
Datensatz
Wechselsprung
Einheit <Mathematik>
Offene Menge
Speicherabzug
Hill-Differentialgleichung
Tabelle <Informatik>

Metadaten

Formale Metadaten

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

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract This presentation demonstrates common techniques to clean-up spatial data from a real-world data source.I will start with raw OSM data.Topics I'll cover:1) Getting OSM data2) How to merge multilinestrings into single linestrings.3) Fill in gaps, snapping, reducing resolution4) Consolidating geometries with aggregate functions ST Union, ST MakeLine, ST Collect based on both spatial and non-spatial attributes
Schlagwörter postgis
sql

Ähnliche Filme

Loading...
Feedback