10 Things I've Learnt About PostGIS
Silk, Daniel
10 Things I've Learnt About PostGIS
 Thank you very much, Martin. It's great to have you all here today. So yeah, obviously I'm going to be running through 10 things I've learned about Posterous relatively quickly and there is quite a bit of SQL on some of these slides. So we'll definitely try and make the slides available later so that you can have a look back at them. Is my clicker working? Yes. Cool. So just a brief introduction. Posterous at Land Information New Zealand has been in use for quite a long time. In 2004 the Geodetic team started using Posterous. From about 2014 we've had a number of systems which have been developed with QGIS as a front end and for data manipulation and Posterous as the back end. So it started with the Gazetteer which is our place name status set and has continued through addressing and road data management and more recently building outlines and aerial imagery surveys. So the first thing that I've learned about Posterous is that spatial indices are your greatest asset. So adding a spatial index is relatively straightforward and then updating the query planner so that it makes best use of that spatial index. A spatial index is effectively a very simple representation of your geometry being stored in your database in order to do rapid analysis. So this is Te Papa Tongawera in Wellington, New Zealand. It's quite a complex shaped building with another little bit of building next to it. But its representation is a bounding box is just a square. And so the entire Wellington waterfront looks something like this. Those bounding boxes are then grouped into progressively larger rectangles and so when doing an intersect or something like that the database can very efficiently compare these very simple geometries before it needs to get to a point to actually extract that complex geometry and do the final comparison. So the next thing I've learned is that it's fast and it's mostly because of the previous point. This is a representation, this is 300,000 building outlines that cover the Wellington region. If we do something like find the centroid of those building outlines in QGIS 3 it takes about 23 seconds and if we do it in Posterous it takes about 3 seconds. But now that I've put this slide together, Nile will probably rewrite the centroid implementation in QGIS and make it 10 times faster than what it is at the moment. But that's competition that I'm quite happy to see continue. So there's just a bunch of centroids on our building outlines in the Wellington CBD. What I've also learned though is that if it's slow there's probably a better way. If a Posterous query runs for like two minutes then I just bin it and start again because I probably did something wrong or forgot to add a spatial index or something. But this particular case, part of our LiDAR process is part of the QA process for our LiDAR surveys is analysing the grids of LiDAR data that have been supplied to us. So something really simple that our LiDAR analyst wants to do is find out which of these 1.1 million grid squares that make up the whole of New Zealand are part of the latest LiDAR survey. So just a simple ST intersex should get the right result. But in this particular case it was taking a couple of minutes just to do that. One large polygon gone against a whole bunch of small polygons. So using ST subdivide we can make that one large polygon a whole bunch of small polygons that have a more equal distribution like number of vertices. And so if we zoom in a little closer at the edge of the LiDAR survey where there's more dense points, there's smaller squares. And if we then find the intersecting polygons from the previous grids do the exact same intersects but on the subdivided instead of the larger polygon, it takes a third of a second instead of two minutes. So that's just making really good use of the spatial indexes on both of those layers. The fourth thing I've learnt is that consistency is key. If I'm doing a bunch of analysis of different, trying to figure out some spatial relationships and stuff, as I go through the process I really want to make sure that I've got a primary key and I've got a spatial index the whole way through. Otherwise I might find some inefficient processes in amongst that. So in post just when doing different spatial analysis, I often end up in a situation where in this case if I take a polygon and I want its vertices, then I'll end up with five rows and if I run the query that's shown I get the same ID, the original ID for that polygon five times. So I can't use that as a primary key. So you can make use of the row numbers that Postgres stores. So on the previous slide we've got one, two, three, four, five on the side which is a representation of that. So if we wrap our original select statement with a window function and select the row number over as ID, then we get one, two, three, four, five and can immediately use that as our primary key. We also obviously make our own functions that have our business logic and store those in the database. So combining the spatial operations from Postgres to get the result that we want to get for the task at hand and in many cases we can find similar tasks that we can reuse one function over and over again for different purposes. This is pretty heavy in terms of code but basically if we want to find out what suburb intersects a polygon, then we've got some sort of business rules around how that works. I'll just run through it step by step. These building outlines again in Auckland Harbour. So this area down the bottom here is the end of a wharf and the wharf actually continues a little bit further because it's changed recently and we've got a few building outlines on the end there. So as they stand they don't exist within any of our suburbs but what our function does is first search for all suburbs within a kilometre of that building outline using STD which makes really good use of our spatial indexes again. To drill down to just the five suburbs that intersect that area and then we can get the distance in ascending order so we can find the suburb that is closest. This function also handles the case where a building crosses over the boundary of two different suburbs and we use the area of the intersection if it's a case where we just want to assign one suburb and forget about the other. So the final part of that whole process is limiting it to just the one. These stored functions can then be used just as part of other tasks that we need to do. For example, inserting a bunch of building outlines in a bulk import. The more complex code that was on the previous slide is just part of the suburb, underscored, intersect, underscored polygon function but each part is broken down and it's not one large piece of code stored in one place. The sixth thing I've learnt is that geometry types, postures can be very freeing about what you can store in a geometry column. So if you just say I've got a geometry column and that's it, then you can do something like insert a point, a line string and a polygon which is cool but not particularly practical if you can't display it in anything. You just obviously need those three things to be separated or you need to load it in three times and assign which geometry type you want to display. But adding a geometry type with this syntax as point means that if we repeat the same insert as before, the first time it hits something that's not a point it's going to error out. So understanding what you can and can't do and where it's appropriate to add that kind of type constraint is obviously really important to maintaining the integrity of your data. Geometry constraints can get a lot more complicated than that or richer than that. If you don't want to allow overlaps between polygons in your database, then you can create a function which uses ST intersects and not touches which is a close approximation of ST of I don't want this thing to overlap. And then you can add that constraint as a check constraint to your database. And then every single time you add a new polygon, you'll have a check to make sure that this is not overlapping any of the polygons that I already have in my database. So again, if your business rules determine that XYZ shouldn't happen, you can probably build something in at the database level to really make sure that it doesn't happen. Because the PostgreSQL community is an open source community, there's also a whole bunch of functions that are already out there that you can go and have a look at that might not be part of the core code at the moment. So there's a cool one called Normalize Geometry which pretty much has its own website. Gaspari's created a whole bunch of different parameters that you can feed in for how you want it to behave. But some of the things that it can do is remove spikes from existing geometries. So the top line is just showing geometries that have like an out and back and then cleaned up. And it can also simplify the number of vertices. So this is a circle that slowly becomes, you know, Corsair and Corsair depending on the parameters that you use with this function. So if you've got overly detailed information, then that's one way that you can simplify. Humans also has a whole bunch of open source PostgreSQL extensions and NZ Building Outlines. So some of our systems that we use to store PostgreSQL data are on GitHub and you can have a look at how we're using it there. The ninth thing I've learned is that recently, PGM and 4 got a geometry viewer. So in PGM and 4, you can now select ID and Geom. And if you have a geometry column, you get this little blue icon. And if you click on that, you can actually see your geometries within PGM. If they are in a coordinate system that's not 4326, then you just get a blank background. But if you transform the selection to 4326 WGC4, then you get some open street map contextual information in the background. And you can actually choose between five different maps to show in the background. The PG-Edmund for a geometry viewer also has the unique distinction of being able to show you that mixture of polygons, line strings and points. So if you are debugging some intersection where you've returned polygons and line strings and you only wanted polygons, you can actually view it in there and see what's going on. The tenth thing is that I've learned that I really enjoy solving spatial problems with postures. I think it's really satisfying when you find the way to do something that works really efficiently. We've obviously, at LYNS, continued to expand our use of postures for that reason. And yeah, I think the list of projects will just continue to grow that are being stored in a post-grader's postures database. So, yeah. Thank you for that. Thank you for that.
 Thank you very much, Martin.  It's great to have you all here today.  So yeah, obviously I'm going to be running through 10 things I've learned about Posterous  relatively quickly and there is quite a bit of SQL on some of these slides.  So we'll definitely try and make the slides available later so that you can have a look  back at them.  Is my clicker working?  Yes.  Cool.  So just a brief introduction.  Posterous at Land Information New Zealand has been in use for quite a long time.  In 2004 the Geodetic team started using Posterous.  From about 2014 we've had a number of systems which have been developed with QGIS as a front  end and for data manipulation and Posterous as the back end.  So it started with the Gazetteer which is our place name status set and has continued through  addressing and road data management and more recently building outlines and aerial imagery  surveys.  So the first thing that I've learned about Posterous is that spatial indices are your  greatest asset.  So adding a spatial index is relatively straightforward and then updating the query planner so that  it makes best use of that spatial index.  A spatial index is effectively a very simple representation of your geometry being stored  in your database in order to do rapid analysis.  So this is Te Papa Tongawera in Wellington, New Zealand.  It's quite a complex shaped building with another little bit of building next to it.  But its representation is a bounding box is just a square.  And so the entire Wellington waterfront looks something like this.  Those bounding boxes are then grouped into progressively larger rectangles and so when  doing an intersect or something like that the database can very efficiently compare  these very simple geometries before it needs to get to a point to actually extract that  complex geometry and do the final comparison.  So the next thing I've learned is that it's fast and it's mostly because of the previous  point.  This is a representation, this is 300,000 building outlines that cover the Wellington  region.  If we do something like find the centroid of those building outlines in QGIS 3 it takes  about 23 seconds and if we do it in Posterous it takes about 3 seconds.  But now that I've put this slide together, Nile will probably rewrite the centroid implementation  in QGIS and make it 10 times faster than what it is at the moment.  But that's competition that I'm quite happy to see continue.  So there's just a bunch of centroids on our building outlines in the Wellington CBD.  What I've also learned though is that if it's slow there's probably a better way.  If a Posterous query runs for like two minutes then I just bin it and start again because  I probably did something wrong or forgot to add a spatial index or something.  But this particular case, part of our LiDAR process is part of the QA process for our  LiDAR surveys is analysing the grids of LiDAR data that have been supplied to us.  So something really simple that our LiDAR analyst wants to do is find out which of these  1.1 million grid squares that make up the whole of New Zealand are part of the latest  LiDAR survey.  So just a simple ST intersex should get the right result.  But in this particular case it was taking a couple of minutes just to do that.  One large polygon gone against a whole bunch of small polygons.  So using ST subdivide we can make that one large polygon a whole bunch of small polygons  that have a more equal distribution like number of vertices.  And so if we zoom in a little closer at the edge of the LiDAR survey where there's more  dense points, there's smaller squares.  And if we then find the intersecting polygons from the previous grids do the exact same  intersects but on the subdivided instead of the larger polygon, it takes a third of a  second instead of two minutes.  So that's just making really good use of the spatial indexes on both of those layers.  The fourth thing I've learnt is that consistency is key.  If I'm doing a bunch of analysis of different, trying to figure out some spatial relationships  and stuff, as I go through the process I really want to make sure that I've got a primary  key and I've got a spatial index the whole way through.  Otherwise I might find some inefficient processes in amongst that.  So in post just when doing different spatial analysis, I often end up in a situation where  in this case if I take a polygon and I want its vertices, then I'll end up with five rows  and if I run the query that's shown I get the same ID, the original ID for that polygon  five times.  So I can't use that as a primary key.  So you can make use of the row numbers that Postgres stores.  So on the previous slide we've got one, two, three, four, five on the side which is a representation  of that.  So if we wrap our original select statement with a window function and select the row  number over as ID, then we get one, two, three, four, five and can immediately use that as  our primary key.  We also obviously make our own functions that have our business logic and store those in  the database.  So combining the spatial operations from Postgres to get the result that we want to get for  the task at hand and in many cases we can find similar tasks that we can reuse one function  over and over again for different purposes.  This is pretty heavy in terms of code but basically if we want to find out what suburb  intersects a polygon, then we've got some sort of business rules around how that works.  I'll just run through it step by step.  These building outlines again in Auckland Harbour.  So this area down the bottom here is the end of a wharf and the wharf actually continues  a little bit further because it's changed recently and we've got a few building outlines  on the end there.  So as they stand they don't exist within any of our suburbs but what our function does  is first search for all suburbs within a kilometre of that building outline using STD which makes  really good use of our spatial indexes again.  To drill down to just the five suburbs that intersect that area and then we can get the  distance in ascending order so we can find the suburb that is closest.  This function also handles the case where a building crosses over the boundary of two  different suburbs and we use the area of the intersection if it's a case where we just  want to assign one suburb and forget about the other.  So the final part of that whole process is limiting it to just the one.  These stored functions can then be used just as part of other tasks that we need to do.  For example, inserting a bunch of building outlines in a bulk import.  The more complex code that was on the previous slide is just part of the suburb, underscored,  intersect, underscored polygon function but each part is broken down and it's not one  large piece of code stored in one place.  The sixth thing I've learnt is that geometry types, postures can be very freeing about  what you can store in a geometry column.  So if you just say I've got a geometry column and that's it, then you can do something like  insert a point, a line string and a polygon which is cool but not particularly practical  if you can't display it in anything.  You just obviously need those three things to be separated or you need to load it in  three times and assign which geometry type you want to display.  But adding a geometry type with this syntax as point means that if we repeat the same  insert as before, the first time it hits something that's not a point it's going to error out.  So understanding what you can and can't do and where it's appropriate to add that kind  of type constraint is obviously really important to maintaining the integrity of your data.  Geometry constraints can get a lot more complicated than that or richer than that.  If you don't want to allow overlaps between polygons in your database, then you can create  a function which uses ST intersects and not touches which is a close approximation of  ST of I don't want this thing to overlap.  And then you can add that constraint as a check constraint to your database.  And then every single time you add a new polygon, you'll have a check to make sure that this  is not overlapping any of the polygons that I already have in my database.  So again, if your business rules determine that XYZ shouldn't happen, you can probably  build something in at the database level to really make sure that it doesn't happen.  Because the PostgreSQL community is an open source community, there's also a whole bunch  of functions that are already out there that you can go and have a look at that might not  be part of the core code at the moment.  So there's a cool one called Normalize Geometry which pretty much has its own website.  Gaspari's created a whole bunch of different parameters that you can feed in for how you  want it to behave.  But some of the things that it can do is remove spikes from existing geometries.  So the top line is just showing geometries that have like an out and back and then cleaned  up.  And it can also simplify the number of vertices.  So this is a circle that slowly becomes, you know, Corsair and Corsair depending on the  parameters that you use with this function.  So if you've got overly detailed information, then that's one way that you can simplify.  Humans also has a whole bunch of open source PostgreSQL extensions and NZ Building Outlines.  So some of our systems that we use to store PostgreSQL data are on GitHub and you can  have a look at how we're using it there.  The ninth thing I've learned is that recently, PGM and 4 got a geometry viewer.  So in PGM and 4, you can now select ID and Geom.  And if you have a geometry column, you get this little blue icon.  And if you click on that, you can actually see your geometries within PGM.  If they are in a coordinate system that's not 4326, then you just get a blank background.  But if you transform the selection to 4326 WGC4, then you get some open street map contextual  information in the background.  And you can actually choose between five different maps to show in the background.  The PG-Edmund for a geometry viewer also has the unique distinction of being able to show  you that mixture of polygons, line strings and points.  So if you are debugging some intersection where you've returned polygons and line strings  and you only wanted polygons, you can actually view it in there and see what's going on.  The tenth thing is that I've learned that I really enjoy solving spatial problems with  postures.  I think it's really satisfying when you find the way to do something that works really  efficiently.  We've obviously, at LYNS, continued to expand our use of postures for that reason.  And yeah, I think the list of projects will just continue to grow that are being stored  in a post-grader's postures database.  So, yeah.  Thank you for that.  Thank you for that.
Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_0_59.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_60_707.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_708_2207.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_2208_6995.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_6996_7367.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_7368_11027.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_11028_11651.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_11652_14867.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_14868_20171.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_20172_20843.png Daniel_Silk__10_Things_I_ve_Learnt_About_PostGIS_93bX1AO95nY_20844_20929.png
Thank you very much, Martin. It's great to have you all here today. So yeah, obviously I'm going to be running through 10 things I've learned about Posterous relatively quickly and there is quite a bit of SQL on some of these slides. So we'll definitely try and make the slides available later so that you can have a look back at them. Is my clicker working? Yes. Cool. So just a brief introduction. Posterous at Land Information New Zealand has been in use for quite a long time. In 2004 the Geodetic team started using Posterous. From about 2014 we've had a number of systems which have been developed with QGIS as a front end and for data manipulation and Posterous as the back end. So it started with the Gazetteer which is our place name status set and has continued through addressing and road data management and more recently building outlines and aerial imagery surveys. So the first thing that I've learned about Posterous is that spatial indices are your greatest asset. So adding a spatial index is relatively straightforward and then updating the query planner so that it makes best use of that spatial index. A spatial index is effectively a very simple representation of your geometry being stored in your database in order to do rapid analysis. So this is Te Papa Tongawera in Wellington, New Zealand. It's quite a complex shaped building with another little bit of building next to it. But its representation is a bounding box is just a square. And so the entire Wellington waterfront looks something like this. Those bounding boxes are then grouped into progressively larger rectangles and so when doing an intersect or something like that the database can very efficiently compare these very simple geometries before it needs to get to a point to actually extract that complex geometry and do the final comparison. So the next thing I've learned is that it's fast and it's mostly because of the previous point. This is a representation, this is 300,000 building outlines that cover the Wellington region. If we do something like find the centroid of those building outlines in QGIS 3 it takes about 23 seconds and if we do it in Posterous it takes about 3 seconds. But now that I've put this slide together, Nile will probably rewrite the centroid implementation in QGIS and make it 10 times faster than what it is at the moment. But that's competition that I'm quite happy to see continue. So there's just a bunch of centroids on our building outlines in the Wellington CBD. What I've also learned though is that if it's slow there's probably a better way. If a Posterous query runs for like two minutes then I just bin it and start again because I probably did something wrong or forgot to add a spatial index or something. But this particular case, part of our LiDAR process is part of the QA process for our LiDAR surveys is analysing the grids of LiDAR data that have been supplied to us. So something really simple that our LiDAR analyst wants to do is find out which of these 1.1 million grid squares that make up the whole of New Zealand are part of the latest LiDAR survey. So just a simple ST intersex should get the right result. But in this particular case it was taking a couple of minutes just to do that. One large polygon gone against a whole bunch of small polygons. So using ST subdivide we can make that one large polygon a whole bunch of small polygons that have a more equal distribution like number of vertices. And so if we zoom in a little closer at the edge of the LiDAR survey where there's more dense points, there's smaller squares. And if we then find the intersecting polygons from the previous grids do the exact same intersects but on the subdivided instead of the larger polygon, it takes a third of a second instead of two minutes. So that's just making really good use of the spatial indexes on both of those layers. The fourth thing I've learnt is that consistency is key. If I'm doing a bunch of analysis of different, trying to figure out some spatial relationships and stuff, as I go through the process I really want to make sure that I've got a primary key and I've got a spatial index the whole way through. Otherwise I might find some inefficient processes in amongst that. So in post just when doing different spatial analysis, I often end up in a situation where in this case if I take a polygon and I want its vertices, then I'll end up with five rows and if I run the query that's shown I get the same ID, the original ID for that polygon five times. So I can't use that as a primary key. So you can make use of the row numbers that Postgres stores. So on the previous slide we've got one, two, three, four, five on the side which is a representation of that. So if we wrap our original select statement with a window function and select the row number over as ID, then we get one, two, three, four, five and can immediately use that as our primary key. We also obviously make our own functions that have our business logic and store those in the database. So combining the spatial operations from Postgres to get the result that we want to get for the task at hand and in many cases we can find similar tasks that we can reuse one function over and over again for different purposes. This is pretty heavy in terms of code but basically if we want to find out what suburb intersects a polygon, then we've got some sort of business rules around how that works. I'll just run through it step by step. These building outlines again in Auckland Harbour. So this area down the bottom here is the end of a wharf and the wharf actually continues a little bit further because it's changed recently and we've got a few building outlines on the end there. So as they stand they don't exist within any of our suburbs but what our function does is first search for all suburbs within a kilometre of that building outline using STD which makes really good use of our spatial indexes again. To drill down to just the five suburbs that intersect that area and then we can get the distance in ascending order so we can find the suburb that is closest. This function also handles the case where a building crosses over the boundary of two different suburbs and we use the area of the intersection if it's a case where we just want to assign one suburb and forget about the other. So the final part of that whole process is limiting it to just the one. These stored functions can then be used just as part of other tasks that we need to do. For example, inserting a bunch of building outlines in a bulk import. The more complex code that was on the previous slide is just part of the suburb, underscored, intersect, underscored polygon function but each part is broken down and it's not one large piece of code stored in one place. The sixth thing I've learnt is that geometry types, postures can be very freeing about what you can store in a geometry column. So if you just say I've got a geometry column and that's it, then you can do something like insert a point, a line string and a polygon which is cool but not particularly practical if you can't display it in anything. You just obviously need those three things to be separated or you need to load it in three times and assign which geometry type you want to display. But adding a geometry type with this syntax as point means that if we repeat the same insert as before, the first time it hits something that's not a point it's going to error out. So understanding what you can and can't do and where it's appropriate to add that kind of type constraint is obviously really important to maintaining the integrity of your data. Geometry constraints can get a lot more complicated than that or richer than that. If you don't want to allow overlaps between polygons in your database, then you can create a function which uses ST intersects and not touches which is a close approximation of ST of I don't want this thing to overlap. And then you can add that constraint as a check constraint to your database. And then every single time you add a new polygon, you'll have a check to make sure that this is not overlapping any of the polygons that I already have in my database. So again, if your business rules determine that XYZ shouldn't happen, you can probably build something in at the database level to really make sure that it doesn't happen. Because the PostgreSQL community is an open source community, there's also a whole bunch of functions that are already out there that you can go and have a look at that might not be part of the core code at the moment. So there's a cool one called Normalize Geometry which pretty much has its own website. Gaspari's created a whole bunch of different parameters that you can feed in for how you want it to behave. But some of the things that it can do is remove spikes from existing geometries. So the top line is just showing geometries that have like an out and back and then cleaned up. And it can also simplify the number of vertices. So this is a circle that slowly becomes, you know, Corsair and Corsair depending on the parameters that you use with this function. So if you've got overly detailed information, then that's one way that you can simplify. Humans also has a whole bunch of open source PostgreSQL extensions and NZ Building Outlines. So some of our systems that we use to store PostgreSQL data are on GitHub and you can have a look at how we're using it there. The ninth thing I've learned is that recently, PGM and 4 got a geometry viewer. So in PGM and 4, you can now select ID and Geom. And if you have a geometry column, you get this little blue icon. And if you click on that, you can actually see your geometries within PGM. If they are in a coordinate system that's not 4326, then you just get a blank background. But if you transform the selection to 4326 WGC4, then you get some open street map contextual information in the background. And you can actually choose between five different maps to show in the background. The PG-Edmund for a geometry viewer also has the unique distinction of being able to show you that mixture of polygons, line strings and points. So if you are debugging some intersection where you've returned polygons and line strings and you only wanted polygons, you can actually view it in there and see what's going on. The tenth thing is that I've learned that I really enjoy solving spatial problems with postures. I think it's really satisfying when you find the way to do something that works really efficiently. We've obviously, at LYNS, continued to expand our use of postures for that reason. And yeah, I think the list of projects will just continue to grow that are being stored in a post-grader's postures database. So, yeah. Thank you for that. Thank you for that.
to be running through 10 things I've learned