Mending Spatial Data with PostGIS
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.
HSU, LEO
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
 Power there as well. Thanks for watching! Okay. Hello everybody, I'm going to go ahead and get started. My name is Leo. I work for Paragon Corporation. Regina is my wife and we co-authored a book called Postures in Action, if you haven't bought it yet, look for it and buy the second edition. 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? Oh, I thought there was. Oh, not yet. No slide. Cool. So the motivation is from me having to take some data from my local neighborhood and present it to a group of important people who also live in my neighborhood. And I could not use Google data, so I used OpenStreetMap. So I pulled down the records for the neighborhood in question and what I saw were irregularities, should I say. Problems with geometries that are contributed by people, whether they be missing streets, intersections that didn't really quite intersect and issues like that. So what I had to manually do was fix up the data. So at the very least, it would be presentable. It's quite easy to see if you're in the same neighborhood as the people that you're presenting to that a street is falling off or not in the right place. So I'm going to start with just very simple geometries. I'm not even going to worry about spatial reference or anything. I'm going to take some simple cases and show you some of the functions that you can use in post just to make simple fixes to roads. So let me open up my post grades environment. I have a local database here. The first thing I am going to show you is simply a series of line streams that are in segments along the diagonal of the Cartesian coordinate. So I have four separate line streams. Let's say that each person, each different person put in a different segment of the street that they lived on and I end up with four different records in my geometry table. So I kind of don't like that because as you can see, so right now I've created the table with the four records. And let me just show you that in open jump. Okay, so in this simple example, you have say four people who put in four separate line streets. So I find this annoying because first there's four records and it's clearly the same road. So I'm going to take some steps to clean this up. The first thing I'm going to do is I'm going to, I'm just going to do a simple union. And when you do this, it will collapse down the four records into one. However, it will produce a multi-line stream. So we're down to one record. But now instead of a regular simple geometry, we end up with a multi-geometry. All right, so I'm still not happy. So I apply another function here, line merge. What line merge will do is it will merge the points along my multi-line stream together. It will stick them end to end. If they come out to be a single line stream, it will reduce my multi down to a single line stream. In this case, since everything lines up on the same road, I do get a very clean result back to one line stream with one, two, three, four, five points. Still, I'm not happy because this is a road. It's a straight road. And we all know that we only need two points to define this any given straight line segment. So I apply simplify. And I am down to my final result, which is just two points at negative two and two, two. So I'm just going to show you that. Take out the text. One final line stream. Now I am happy. So I go on like this. And the next example, well, I have line streams with people who put in different segments overlapping. So you have here Antoine and Bastien who basically put in the same entry. And this simplification method will take care of it too. So let me just show you this. In the textual form, I'm not going to bother drawing it. Okay. So this is a case where you have multiple people putting in different segments of the same street. I ran into people who put in reverse. People who, if they're taking their GPS and they're trying to identify the street that they live on, some person may start from one angle to the other or vice versa. And so then I get these results. So I have 13 and it will still work. Okay. The only thing you have to watch for when people put in these reverses are if they're one-way streets. But if they're not one-way streets, you don't really have to worry about it and you can use this simplification and get it down to one line stream. And then finally, and I'm not even going to show you that this works, you can just have all sorts of crap in here. You could have any segment along this line stream. As long as everybody filled up the whole line stream that you're trying to cover, you can get them down to one simple line stream with two endpoints. Okay. Let me jump down to the misplaced intersection. So let me show you what this looks like. Okay. So this is what I'm trying to say here is I have four streets. They intersect, they should intersect at the origin, zero, zero. However, they don't. And I will show you that pictorically. Let me get rid of the other layers. Okay, so this is where this is the misplaced intersection, the intersections should really be at the origin. So to fix this, I'm going to use the snap function. The snap function will take a point on one geometry and move that point to the point on another geometry. The first geometry is the, what we call the input geometry, and then the second one is the reference geometry. So in this example, we're taking all of our roads and we are going to snap it to the point, zero, zero. So that's a new table called 31. And let's take a look at that. Okay, so as you can see, this is the revised geometry where we moved all the roads by snapping their endpoints, which is right here, back to the origin. The next example, which, because the snap has this value here, it says to snap only within one unit measure, in this case it's just Cartesian. That's why that's a good point. If you set this to something that's too high, you could end up with something funky. So to do all these fixes, I advise that you do it on a sort of a case by case first. And if it seems like that it's working for some and you're too tired of fixing all the roads in the city, then run it for all the city and you may over fix, but first eyeball it and try it on a few cases. Okay, the next one is pretty common. This is a case where the roads don't meet when they should. So roads should meet at, in this case, a T intersection, but it doesn't. So this is example 40. I have the table. Okay, so this is for whatever reason, the person didn't get the same coordinate here as somebody that marked out this street. And so you have this gap. There's a few ways to fix this. One of them are going to be perfect since you're going to make an assumption about what goes into here. The easiest way and the only way that I bothered with is to use the shortest line between this line string and this line string. So this will fill in the space like that. That's the shortest line. So that will, between the two 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. So let me get rid of this layer. I think I set that snap parameter too close. Okay, that's probably citing it too. As you mentioned, I set that parameter too high and it's snapping the wrong point. So let me see if I can get it right. It doesn't look like I'm going to be able to compute this eyeball-wise. So there are two ways to do it. One is to use the shortest line string. The other one is to find the closest point on this line string to this line string and snap this point to it. And then you can also do the over snapping case, which is just take some point on this row and snap it to the closest point on this row. I mean, these are not 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's driving down this row. So to put this into practice, what I have are some open-stream map data that I pulled, open-stream lines, points, polygons and roads. In this case, only the lines matter. So I pulled a section of upper Manhattan. And let me show you what that looks like. Start up. Okay. Okay. Okay, so this is a section of Upper Manhattan. There are some problems with it. Some of the problems I introduced for the sake of example. So I want you to notice this little red thing here, that this is actually somebody put this into OSM and I'm not quite sure what that is, but it doesn't look too good. The other thing I want you to notice is here, this Cooper Street. This Cooper Street should go all the way down. And then finally, this intersection here is off. It's going into houses. So applying the technique, I'm going to do it all at once. So this is exactly the same thing as what I did for the simple geometry example. Okay. And for this, I need to change that safe. Okay. I intentionally put in a wrong fix for Payson. I oversimplified this Payson Avenue to show you what happens when you oversimplify. It turns into a straight line instead of keeping all the kinks in it. But other than that, you can see that I fixed this intersection. It's now perfect. Cooper Street. I called this Cooper Street extension and that continues. And then over here, I snapped this extraneous red part of Henry Hudson back into this piece. And that is it for this presentation. Questions? Well, you said it's exactly the same, but you actually have a few fancy sepal constructs in there. So you said it was exactly the same when you applied it at scale to many line strings and many polygons. But actually, you have a few interesting sequel constructs. So maybe just for review. Well, simple people, you could look at a few of those like you have those nice width, you have a cross join, you have some sub select, you have just some of those things. So I will, it is still the same as far as the simple Cartesian geometry versus the OSM. The only thing different with the OSM is I named them and I deleted the old geometry so that they're not sitting on top of each other. So the width, let me just go back to a simple width. With this, the CTE, the new CTE construct, which allows you to basically name subqueries in SQL. I love it before I would have to write this as one big select. But now I can break it into two separate selects, name them M and S, and then do my join here so it's a much cleaner. The only thing you have to watch for when you do it this way is that these will materialize in the sense that these queries will run. So if these subqueries are going to generate a lot of results that you no longer need in the main query, you probably shouldn't do this. But otherwise it's a great syntax and I probably use it more than I should. So that's the width and then everything else. The cross-join, for those of you that's not familiar, all that does is take all the rows from one table and creates a Cartesian coordinate, Cartesian join with the second table. So if you have three rows in table A, four rows in table B, your cross-join basically creates 12 rows. In this case, they're all single rows and I'm just matching them together one by one so that I got both sides. Otherwise it's all the leads and inserts and all the inserts and the leads are really just to clean up the table so that I can show them to you nicely in open jump. Any other questions? Okay. Thank you very much.
 Power there as well.  Thanks for watching!  Okay.  Hello everybody, I'm going to go ahead and get started. My name is Leo. I work for Paragon  Corporation. Regina is my wife and we co-authored a book called Postures in Action, if you haven't  bought it yet, look for it and buy the second edition.  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?  Oh, I thought there was.  Oh, not yet. No slide.  Cool.  So the motivation is from me having to take some data from my local neighborhood and present  it to a group of important people who also live in my neighborhood.  And I could not use Google data, so I used OpenStreetMap.  So I pulled down the records for the neighborhood in question and what I saw were irregularities,  should I say. Problems with geometries that are contributed by people, whether they be  missing streets, intersections that didn't really quite intersect and issues like that.  So what I had to manually do was fix up the data.  So at the very least, it would be presentable.  It's quite easy to see if you're in the same neighborhood as the people that you're presenting  to that a street is falling off or not in the right place.  So I'm going to start with just very simple geometries.  I'm not even going to worry about spatial reference or anything.  I'm going to take some simple cases and show you some of the functions that you can use  in post just to make simple fixes to roads.  So let me open up my post grades environment.  I have a local database here.  The first thing I am going to show you is simply a series of line streams that are in  segments along the diagonal of the Cartesian coordinate.  So I have four separate line streams.  Let's say that each person, each different person put in a different segment of the street  that they lived on and I end up with four different records in my geometry table.  So I kind of don't like that because as you can see, so right now I've created the table  with the four records.  And let me just show you that in open jump.  Okay, so in this simple example, you have say four people who put in four separate line  streets.  So I find this annoying because first there's four records and it's clearly the same road.  So I'm going to take some steps to clean this up.  The first thing I'm going to do is I'm going to, I'm just going to do a simple union.  And when you do this, it will collapse down the four records into one.  However, it will produce a multi-line stream.  So we're down to one record.  But now instead of a regular simple geometry, we end up with a multi-geometry.  All right, so I'm still not happy.  So I apply another function here, line merge.  What line merge will do is it will merge the points along my multi-line stream together.  It will stick them end to end.  If they come out to be a single line stream, it will reduce my multi down to a single line  stream.  In this case, since everything lines up on the same road, I do get a very clean result  back to one line stream with one, two, three, four, five points.  Still, I'm not happy because this is a road.  It's a straight road.  And we all know that we only need two points to define this any given straight line segment.  So I apply simplify.  And I am down to my final result, which is just two points at negative two and two, two.  So I'm just going to show you that.  Take out the text.  One final line stream.  Now I am happy.  So I go on like this.  And the next example, well, I have line streams with people who put in different segments  overlapping.  So you have here Antoine and Bastien who basically put in the same entry.  And this simplification method will take care of it too.  So let me just show you this.  In the textual form, I'm not going to bother drawing it.  Okay.  So this is a case where you have multiple people putting in different segments of the  same street.  I ran into people who put in reverse.  People who, if they're taking their GPS and they're trying to identify the street that  they live on, some person may start from one angle to the other or vice versa.  And so then I get these results.  So I have 13 and it will still work.  Okay.  The only thing you have to watch for when people put in these reverses are if they're  one-way streets.  But if they're not one-way streets, you don't really have to worry about it and you can  use this simplification and get it down to one line stream.  And then finally, and I'm not even going to show you that this works, you can just have  all sorts of crap in here.  You could have any segment along this line stream.  As long as everybody filled up the whole line stream that you're trying to cover, you can  get them down to one simple line stream with two endpoints.  Okay.  Let me jump down to the misplaced intersection.  So let me show you what this looks like.  Okay.  So this is what I'm trying to say here is I have four streets.  They intersect, they should intersect at the origin, zero, zero.  However, they don't.  And I will show you that pictorically.  Let me get rid of the other layers.  Okay, so this is where this is the misplaced intersection, the intersections should really  be at the origin.  So to fix this, I'm going to use the snap function.  The snap function will take a point on one geometry and move that point to the point  on another geometry.  The first geometry is the, what we call the input geometry, and then the second one is  the reference geometry.  So in this example, we're taking all of our roads and we are going to snap it to the point,  zero, zero.  So that's a new table called 31.  And let's take a look at that.  Okay, so as you can see, this is the revised geometry where we moved all the roads by snapping  their endpoints, which is right here, back to the origin.  The next example, which, because the snap has this value here, it says to snap only within  one unit measure, in this case it's just Cartesian.  That's why that's a good point.  If you set this to something that's too high, you could end up with something funky.  So to do all these fixes, I advise that you do it on a sort of a case by case first.  And if it seems like that it's working for some and you're too tired of fixing all the  roads in the city, then run it for all the city and you may over fix, but first eyeball  it and try it on a few cases.  Okay, the next one is pretty common.  This is a case where the roads don't meet when they should.  So roads should meet at, in this case, a T intersection, but it doesn't.  So this is example 40.  I have the table.  Okay, so this is for whatever reason, the person didn't get the same coordinate here  as somebody that marked out this street.  And so you have this gap.  There's a few ways to fix this.  One of them are going to be perfect since you're going to make an assumption about what  goes into here.  The easiest way and the only way that I bothered with  is to use the shortest line between this line string and this line string.  So this will fill in the space  like that.  That's the shortest line.  So that will, between the two 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.  So let me get rid of this layer.  I think I set that snap parameter too close.  Okay, that's probably citing it too.  As you mentioned, I set that parameter too high and it's snapping the wrong point.  So let me see if I can get it right.  It doesn't look like I'm going to be able to compute this eyeball-wise.  So there are two ways to do it.  One is to use the shortest line string.  The other one is to find the closest point on this line string to this line string and  snap this point to it.  And then you can also do the over snapping case, which is just take some point on this  row and snap it to the closest point on this row.  I mean, these are not 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's driving down this row.  So to put this into practice, what I have are some open-stream map data that I pulled,  open-stream lines, points, polygons and roads.  In this case, only the lines matter.  So I pulled a section of upper Manhattan.  And let me show you what that looks like.  Start up.  Okay.  Okay.  Okay, so this is a section of Upper Manhattan.  There are some problems with it.  Some of the problems I introduced for the sake of example.  So I want you to notice this little red thing here, that this is actually somebody put this  into OSM and I'm not quite sure what that is, but it doesn't look too good.  The other thing I want you to notice is here, this Cooper Street.  This Cooper Street should go all the way down.  And then finally, this intersection here is off.  It's going into houses.  So applying the technique, I'm going to do it all at once.  So this is exactly the same thing as what I did for the simple geometry example.  Okay.  And for this, I need to change that safe.  Okay.  I intentionally put in a wrong fix for Payson.  I oversimplified this Payson Avenue to show you what happens when you oversimplify.  It turns into a straight line instead of keeping all the kinks in it.  But other than that, you can see that I fixed this intersection.  It's now perfect.  Cooper Street.  I called this Cooper Street extension and that continues.  And then over here, I snapped this extraneous red part of Henry Hudson back into this piece.  And that is it for this presentation.  Questions?  Well, you said it's exactly the same, but you actually have a few fancy sepal constructs  in there.  So you said it was exactly the same when you applied it at scale to many line strings and  many polygons.  But actually, you have a few interesting sequel constructs.  So maybe just for review.  Well, simple people, you could look at a few of those like you have those nice width, you  have a cross join, you have some sub select, you have just some of those things.  So I will, it is still the same as far as the simple Cartesian geometry versus the OSM.  The only thing different with the OSM is I named them and I deleted the old geometry  so that they're not sitting on top of each other.  So the width, let me just go back to a simple width.  With this, the CTE, the new CTE construct, which allows you to basically name subqueries  in SQL.  I love it before I would have to write this as one big select.  But now I can break it into two separate selects, name them M and S, and then do my join here  so it's a much cleaner.  The only thing you have to watch for when you do it this way is that these will materialize  in the sense that these queries will run.  So if these subqueries are going to generate a lot of results that you no longer need in  the main query, you probably shouldn't do this.  But otherwise it's a great syntax and I probably use it more than I should.  So that's the width and then everything else.  The cross-join, for those of you that's not familiar, all that does is take all the rows  from one table and creates a Cartesian coordinate, Cartesian join with the second table.  So if you have three rows in table A, four rows in table B, your cross-join basically  creates 12 rows.  In this case, they're all single rows and I'm just matching them together one by one  so that I got both sides.  Otherwise it's all the leads and inserts and all the inserts and the leads are really  just to clean up the table so that I can show them to you nicely in open jump.  Any other questions?  Okay.  Thank you very much.
05_Leo_Hsu_0_131.png 05_Leo_Hsu_132_251.png 05_Leo_Hsu_252_1055.png 05_Leo_Hsu_1056_1607.png 05_Leo_Hsu_1608_1751.png 05_Leo_Hsu_1752_2351.png 05_Leo_Hsu_2352_2867.png 05_Leo_Hsu_2868_3251.png 05_Leo_Hsu_3252_3539.png 05_Leo_Hsu_3540_7607.png 05_Leo_Hsu_7608_8447.png 05_Leo_Hsu_8448_17183.png 05_Leo_Hsu_17184_19715.png 05_Leo_Hsu_19716_19811.png 05_Leo_Hsu_19812_21911.png 05_Leo_Hsu_21912_24107.png 05_Leo_Hsu_24108_33107.png 05_Leo_Hsu_33108_33179.png 05_Leo_Hsu_33180_33587.png 05_Leo_Hsu_33588_33671.png 05_Leo_Hsu_33672_33827.png 05_Leo_Hsu_33828_35975.png 05_Leo_Hsu_35976_36023.png 05_Leo_Hsu_36024_36335.png 05_Leo_Hsu_36336_42527.png 05_Leo_Hsu_42528_42587.png 05_Leo_Hsu_42588_43487.png 05_Leo_Hsu_43488_43751.png 05_Leo_Hsu_43752_43835.png 05_Leo_Hsu_43836_44462.png