Still working furiously on final edits for the PostGIS Cookbook. My last chapter is the last one that the Packt editors and managers are waiting for, but it will be the best I’ve contributed to the book, so I think it will be worth everyone’s time.
The trick with this chapter is that when I first wrote it, it was before the heavy lifting the Oslandia folks have done on SFCGAL and all the nifty 3D integration work, so I have spent a few days reviewing their work and figuring out some novel applications for their work to date (FOSS moves so quickly, I’m having to write new stuff just to keep up…).
Also, for those of you who ooh’d and ahh’d at Bobby Sudekum’s exciting drone mapping post on the MapBox blog, I’ll be showing you some of the post-processing steps you can do in PostGIS of drone imagery. The whole workflow isn’t in place yet (now that Bborie has made raster faster, we’re waiting on some image draping utilities), but I predict we’re less than a year away from being able to replace that proprietary stack (that can cost between $4-20,000 per annum in licensing fees) with a PostGISful stack. The 3D chapter will take you halfway there.
Now for some drone scribbles:
Smathermather's Weblog 
Steve,
Your 3D posts have been great. Keep it coming. I have yet to wrap my head around all the SFCGAL stuff. Hoping to do that in next month or two.
BTW, are you planning to be the first package manager to wrap up and put a bow on SFCGAL? It’s not too bad to get going on Ubuntu, but it’s not exactly a picnic either… . It’d be awesome to see it in the Windows builds.
That’s in my plans. I was close I think — got sfcgal to compile, cgal 4.2 to compile, but got stuck getting postgis to be happy with the sfcgal and cgal connection (that was a while ago and oslandia had made some fixes since then at my request). I’m planning to have it packaged with PostGIS 2.2. Too late for 2.1, but once I have the kinks worked out will have available on winnie PostGIS 2.2 buildbot builds for people to try.
I’ll have cutting room floor 3D recipes for posting here– I’m bursting with ideas, and limited by page number and time, so I’ll definitely keep it coming here.
Any plans on making the scripts and data available? I’m mostly interested in testing (valgrind, callgrind, etc.)…
Data can be found here:
https://github.com/smathermather/postgis-cookbook-data/tree/master/chp07
although expect these datasets to be in flux for the next couple of days.
PostGIS is used at the beginning and end:
1) PostGIS can be used to optimize the ordering of the images that go into the sparse bundle adjustment by looking at likely 3D viewshed overlap based on GPS location, pitch, roll, and heading (assuming the step I haven’t tested yet of calculating 3D intersections just works…). In this way, pairwise comparisons of matches can be optimized, and the number of cross comparisons minimized. A simpler method might be to just ORDER BY ST_GeoHash.
2) Point cloud creation is in bundler/PMVS, i.e. https://www.cs.cornell.edu/~snavely/bundler/ This step could be partially done in PostGIS too (importantly, the part with patent encumbered code) with the addition of OpenCV and sift: http://docs.opencv.org/modules/nonfree/doc/feature_detection.html?highlight=sift )
3) Finally, PostGIS works as a tool for creating the DTM (so far as written with GEOS– hope to complete with SFCGAL as well in the next couple of days) and also rendered as a raster using a voronoi diagram as a cheap interpolater. Once ST_Drape (or whatever) is in place, then this could be done as a drape of the original images on the DTM, then rendered back as a raster.
Code will be in the private PostGIS Cookbook GitHub repo in the next few hours, and on Packt’s site http://www.packtpub.com/support when the time comes.
Now if only I could read the raster part of my recipe– so rusty on raster… (and it’s still spaghetti-ish, need some CTE to clean it up.