Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Archive for the ‘SFCGAL’ Category

The easiest way to get PostGIS and friends running:

Posted by smathermather on May 21, 2014

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 built it. I played with its wonders. Then I decided it was too much too early. Well, not any more… .

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION pgrouting;
CREATE EXTENSION pointcloud;
CREATE EXTENSION pointcloud_postgis;

Screen shot of pgAdmin with CREATE EXTENSIONS for all the fun postgresql extensions... .

Oh, one more thing: pro-tip. You can modify the Docker file into a shell script for installing all this goodness on your local Ubuntu/Debian machine as well:
https://github.com/vpicavet/docker-pggis/blob/master/Dockerfile

Posted in 3D, Database, PostGIS, PostgreSQL, SFCGAL, SQL | Tagged: , , , , | 7 Comments »

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

Posted by smathermather on December 15, 2013

In my earlier post, I made a claim that SFCGAL was used in this figure:

Figure showing overlapping viewing footprints from images from UAS flight.

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:

Map showing TIN of intersection of digital terrain model and viewing cone from UAS.

Here it is compared with the initial calculation:

Comparison of previous figure with original less correct estimate.

Posted in 3D, Analysis, Database, Drone, Image Processing, Optics, Other, Photogrammetry, PostGIS, PostgreSQL, QGIS, SFCGAL, SQL, UAS | Tagged: , , , , , | 3 Comments »

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

Posted by smathermather on December 15, 2013

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

Nadir view of viewing pyramid for individual drone  image

Posted in 3D, Analysis, Database, Drone, Image Processing, Optics, Other, PostGIS, PostgreSQL, QGIS, SFCGAL, SQL, UAS | Tagged: , , , | Leave a Comment »

More cutting room floor stuff… — 3D pyramid maker

Posted by smathermather on December 14, 2013

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

PostGIS Pyramid

Edit:  Actually, I think this would work without SFCGAL, but what would be the point… .

Edit 2:  Let’s embed some code:

 

-- Function creates an inverted pyramid
-- Function takes as input an origin point, the size base in x and y, and the height of the pyramid
-- Usage example: SELECT 1 pyramid_maker(geom, 2, 2, 1)  AS the_geom;
CREATE OR REPLACE FUNCTION pyramidMaker(origin geometry, basex numeric, basey numeric, height numeric)
  RETURNS geometry AS
$BODY$

-- Create points as the base of the pyramid-- which perversly is in the air... .
WITH basePoints AS
	(
	SELECT ST_Translate(origin, -0.5 * basex, 0.5 * basey) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, 0.5 * basex, 0.5 * basey) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, 0.5 * basex, -0.5 * basey) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, -0.5 * basex, -0.5 * basey) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, -0.5 * basex, 0.5 * basey) AS the_geom
	),
-- Make the points into a line so we can convert to polygon
basePointsC AS
	(
	SELECT ST_MakeLine(the_geom) AS the_geom FROM basePoints
	),	
baseBox AS
	(
	SELECT ST_MakePolygon(ST_Force3DZ(the_geom)) AS the_geom FROM basePointsC
	),
-- move base of pyramid vertically the input height
base AS
	(
	SELECT ST_Translate(the_geom, 0, 0, height) AS the_geom FROM baseBox
	),
-- Now we construct the triangles, ensuring that we digitize them counterclockwise for validity
triOnePoints AS
	(
	SELECT origin AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, 0.5 * basex, 0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, -0.5 * basex, 0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT origin AS the_geom
	),
triOneAngle AS
	(
	SELECT ST_MakePolygon(ST_MakeLine(the_geom)) the_geom FROM triOnePoints
	),
triTwoPoints AS
	(
	SELECT origin AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, 0.5 * basex, -0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, 0.5 * basex, 0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT origin AS the_geom
	),
triTwoAngle AS
	(
	SELECT ST_MakePolygon(ST_MakeLine(the_geom)) the_geom FROM triTwoPoints
	),
triThreePoints AS
	(
	SELECT origin AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, -0.5 * basex, -0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, 0.5 * basex, -0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT origin AS the_geom
	),
triThreeAngle AS
	(
	SELECT ST_MakePolygon(ST_MakeLine(the_geom)) the_geom FROM triThreePoints
	),
triFourPoints AS
	(
	SELECT origin AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, -0.5 * basex, 0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT ST_Translate(origin, -0.5 * basex, -0.5 * basey, height) AS the_geom
		UNION ALL
	SELECT origin AS the_geom
	),
triFourAngle AS
	(
	SELECT ST_MakePolygon(ST_MakeLine(the_geom)) the_geom FROM triFourPoints
	),
-- Assemble the whole thing into a pyramid
pyramid AS
	(
	SELECT the_geom FROM triOneAngle
		UNION ALL
	SELECT the_geom FROM triTwoAngle
		UNION ALL
	SELECT the_geom FROM triThreeAngle
		UNION ALL
	SELECT the_geom FROM triFourAngle
		UNION ALL
	SELECT the_geom FROM base
	),
-- format the pyramid temporarily as a 3D multipolygon
pyramidMulti AS
	(
	SELECT ST_Multi(St_Collect(the_geom)) AS the_geom FROM pyramid
	),
-- convert to text in order to manipulate with a find/replace into a polyhedralsurface
-- and then convert back to binary
textPyramid AS
	(
	SELECT ST_AsText(the_geom) AS textpyramid FROM pyramidMulti
	),
textBuildSurface AS
	(
	SELECT ST_GeomFromText(replace(textpyramid, 'MULTIPOLYGON', 'POLYHEDRALSURFACE')) AS the_geom FROM textPyramid
	)

SELECT the_geom FROM textBuildSurface

;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION pyramid_maker(geometry, numeric, numeric, numeric)
  OWNER TO me;

Posted in 3D, Database, PostGIS, PostgreSQL, SFCGAL, SQL | Tagged: , , , , | Leave a Comment »

More cutting room floor stuff… .

Posted by smathermather on December 14, 2013

ST_3DIntersection performs volumetric intersections for us in PostGIS, if we have SFCGAL enabled for our PostGIS back end.  Much like a normal PostGIS intersection, this is the mathematical definition of an interesection, so it returns the volumetric portion of the intersection, plus 3D linestrings and 3D points and other bits and pieces that qualify for the intersection.  As a little patch, I wrote a quick and dirty function for just extracting the volumetric portion.  It’s available here:

https://github.com/smathermather/postgis-etc/blob/master/3D/volumetricIntersection.sql

It’s a little cludgy, as not all the bits and pieces we’re used to on the 2D side are built yet, but it works!

One of these days I’ll get horao working on my machine… .

Edit: I’ll have more on volumetric intersection to come, but in the meantime, this tutorial on PovRay:
http://www.cs.tut.fi/~tgraf/harjoitustyot/tutorial/tutorial1.6.html does a good job explaining.

For example, if we intersect two overlapping spheres:
Figure of overlapping spheres
then we get an output thus (a volumetric intersection of two spheres):
Figure of the intersection result of two spheres

edit2, relevant code:

CREATE OR REPLACE FUNCTION volumetricIntersection(geom1 geometry, geom2 geometry)
-- volumetric intersection takes an the input of two 3D geometries
  RETURNS geometry AS
$BODY$

WITH 
intersected AS (
-- first we perform an intersection. This in most cases will return a TIN plus 3D linstrings
-- and other messy pieces we don't need.
	SELECT ST_3DIntersection(geom1, geom2) AS the_geom
	),
-- we use ST_Dump to dump these out to their requisite parts
-- (no, ST_CollectionExtract will not work here-- it only handles
-- points, lines, and polygons, not triangles and tins
dumped AS (
	SELECT (ST_Dump(the_geom)).geom AS the_geom FROM intersected
	),
-- Now we filter for triangle and collect them together
triangles AS (
	SELECT ST_Collect(the_geom) AS the_geom FROM dumped
		WHERE ST_GeometryType(the_geom) ='ST_Triangle'
	),
-- next as a venerable hack, we'll convert to text
triangleText AS (
	SELECT ST_AsText(the_geom) AS triText FROM triangles
	),
-- and replace words in the text in order to "convert" from a collection
-- of triangles to a TIN
replaceTriangle AS (
	SELECT replace(triText, 'TRIANGLE Z ', '') AS rTri FROM triangleText
	),
tinText AS (
	SELECT replace(rTri, 'GEOMETRYCOLLECTION', 'TIN') AS tt FROM replaceTriangle
	),
-- now we convert back to a binary tin, and give it back to the user
tin AS (
	SELECT ST_GeomFromText(tt) AS the_geom FROM tinText
	)
	
SELECT the_geom FROM tin;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

Posted in 3D, Database, PostGIS, PostgreSQL, SFCGAL, SQL | Tagged: , , , , | 4 Comments »

O man. Epic 3D on its way. I could drone on and on about #postgis

Posted by smathermather on December 8, 2013

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:

Path for drone (uas flight) with overlapping dots representing uas drone picture locations

Drone Scribbles

Posted in 3D, Analysis, Database, Drone, PostGIS, PostgreSQL, SFCGAL, SQL, UAS | Tagged: , , , , , | 7 Comments »

ST_Extrude screen shot: PostGIS with SFCGAL

Posted by smathermather on December 7, 2013

I have been working a bit to get horao working to visualize PostGIS 3D data.  In the mean time, I have a little work around for viewing 3D extrudes a 3Dish way (i.e. isometric)

The nightly build of QGIS now allows you to visualize polyhedral surfaces, the output type of ST_Extrude.  So, I cheated.  ST_Extrude takes 4 parameters:

geometry ST_Extrude(geometry geom, float x, float y, float z);

an input geometry, and extrusion along the x, y, and z axes.  So, instead of a normal Z extrude where the x and y axes of the extrude are zeroed out, e.g.:

CREATE TABLE buildings_extruded AS
SELECT gid, ST_Extrude(the_geom, 0,0, 40) as the_geom
	FROM building_footprints;

We’ll extrude a bit along the x and y axes:

CREATE TABLE buildings_extruded AS
SELECT gid, ST_Extrude(the_geom, 20,20, 40) as the_geom
	FROM building_footprints;

And then view in QGIS with the original geometry on top:

ST_Extrude output as quasi-isometric view

ST_Extrude output as quasi-isometric

Posted in 3D, Analysis, Database, PostGIS, PostgreSQL, SFCGAL, SQL | Tagged: , , | 1 Comment »