3D, Database, PostGIS, PostgreSQL, SFCGAL, SQL

The easiest way to get PostGIS and friends running:

Docker. See: https://github.com/vpicavet/docker-pggis for a quick and easy Docker running PostGIS. Understand, this isn’t production ready (the connection permissions make my teeth hurt a little) but, so nice to have a one stop shop for postgis, pgrouting, and pointcloud. Now I may have to write blog posts on pointcloud. Point cloud didn’t get in the PostGIS Cookbook because it was too hard to build. I … Continue reading The easiest way to get PostGIS and friends running:

smathermatherMay 21, 20147 Comments

Database, PostGIS, PostGIS Cookbook, PostgreSQL, SQL

PostGIS Cookbook(s)

This is starting to feel real… PostGIS Cookbook(s). 2904 pages. Page count for a single book can be calculated as in the following code: The preceding query will result in the following: A special thanks to my family, my co-authors, all the contributors to PostGIS, and to Regina Obe for helping me through the hardest bit of code in my part of the book. Check … Continue reading PostGIS Cookbook(s)

smathermatherMarch 5, 2014March 5, 20143 Comments

Database, PostGIS, PostgreSQL, SQL

Editing in progress

Edit: Free book to the first to tell me what the is name of the children’s book on the armchair in the foreground: Book editing in progress… . Continue reading Editing in progress

smathermatherJanuary 9, 2014January 11, 20146 Comments

Analysis, Database, PostGIS, PostgreSQL, SQL

Voronoi in PostGIS

PostGIS has for a time had ST_DelaunayTriangles for Delaunay Triangulation, and since 2.1 apparently can even natively create a 2.5D TIN using said function, which is pretty cool. I think with SFCGAL, we will eventually have true 3D TINs as well. We’re still waiting on native Vororoi support for PostGIS though. According to http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS “GEOS 3.5 is still in development but will have Voronoi which … Continue reading Voronoi in PostGIS

smathermatherDecember 21, 2013December 21, 20135 Comments

3D, Analysis, Database, Drone, Other, Photogrammetry, PostGIS, PostgreSQL, SQL, UAS

2.5D TINs in PostGIS

(edited: changed TIN to TIN Z) (edited again — function already exists as a flag in ST_DelaunayTriangles… .) Uh, I wrote a function for nothin’… As Regina points out in the commments, this functionality was rolled into 2.1 with a flag. Her example: For the record, my code is about 2% slower on a dataset that takes ~50minutes to triangulate . —————————————— Original Post —————————————— … Continue reading 2.5D TINs in PostGIS

smathermatherDecember 18, 2013December 19, 20136 Comments

3D, Analysis, Database, Drone, Image Processing, Optics, Other, Photogrammetry, PostGIS, PostgreSQL, QGIS, SFCGAL, SQL, UAS

UAS (drone) Footprint Geometries Calculated in PostGIS with SFCGAL — for real this time

In my earlier post, I made a claim that SFCGAL was used in this figure: It dawned on my afterwards, while I was using 3D, I hadn’t actually employed any of the analysis goodies that come with SFCGAL. Well, here it is– a footprint as calculated using the view angles and a real terrain model: Here it is compared with the initial calculation: Continue reading UAS (drone) Footprint Geometries Calculated in PostGIS with SFCGAL — for real this time

smathermatherDecember 15, 2013December 15, 20133 Comments

3D, Analysis, Database, Drone, Image Processing, Optics, Other, PostGIS, PostgreSQL, QGIS, SFCGAL, SQL, UAS

UAS (drone) Footprint Geometries Calculated in PostGIS — Individual Footprint

UAS (drone) Footprint Geometries Calculated in PostGIS (viewed in QGIS nightly), taking into account relative elevation, bearing, pitch, and roll, this time just one: Continue reading UAS (drone) Footprint Geometries Calculated in PostGIS — Individual Footprint

smathermatherDecember 15, 2013Leave a comment

3D, Database, PostGIS, PostgreSQL, SQL

Aaargh! No: geometry ST_RotateX(geometry geomA, float rotRadians, geometry pointOrigin)

Edit– Code may be flawed, testing—– testing. Please wait to use… . In PostGIS, ST_RotateZ has a couple forms: a version that rotates around 0,0 and a version that rotates around a point of origin: ST_RotateX and ST_RotateY have had no equivalents– until now. These equivalents are dumb versions– they use a transformation/rotation/reverse-transformation to do their magic, which is maybe not as efficient as using … Continue reading Aaargh! No: geometry ST_RotateX(geometry geomA, float rotRadians, geometry pointOrigin)

smathermatherDecember 14, 2013December 14, 2013Leave a comment

3D, Database, PostGIS, PostgreSQL, SQL

Roll, Pitch, Yaw, ST_Rotate

Thinking out load (and hoping I’m right), but with the classic flight dynamics of roll, pitch, yaw for flight dynamics relative to PostGIS concepts of rotation: Pitch Axis = ST_RotateX Bearing (instead of yaw) = ST_RotateZ using 90-minus the angle for NSEW coordinates from Cartesian angles Roll = ST_RotateY Continue reading Roll, Pitch, Yaw, ST_Rotate

smathermatherDecember 14, 2013Leave a comment

3D, Database, PostGIS, PostgreSQL, SFCGAL, SQL

More cutting room floor stuff… — 3D pyramid maker

A little more leftover code from the PostGIS Cookbook— a little tool for making pyramids for SFCGAL enabled PostGIS databases. https://github.com/smathermather/postgis-etc/blob/master/3D/pyramidMaker.sql Edit: Actually, I think this would work without SFCGAL, but what would be the point… . Edit 2: Let’s embed some code: Continue reading More cutting room floor stuff… — 3D pyramid maker