Posts Tagged ‘SFCGAL’

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

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:

then we get an output thus (a volumetric intersection 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:

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

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

PostGIS with SFCGAL — Videos, how did I miss these videos?

Posted by smathermather on December 5, 2013

The guys from Oslandia are really ripping things up in the PostGIS 3D world.  (They are nice guys too– Olivier and Hugo had a skeletonization approach punched up and ready to test before I left the Boston Code Sprint this past Spring.)

So I missed these two videos in my first few views of the SFCGAL website:

————————————————————————————-

Just one question– what is “Horoa plugin 3D renderer”? Google couldn’t answer this question for me… .

EDITED December 6 from Olivier in the comments:

“It’s just the name of the QGIS plugin dedicated to 3D rendering”

http://oslandia.github.io/horao/

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

PostGIS with SFCGAL

Posted by smathermather on December 4, 2013

So, for those of you who haven’t seen it, SFCGAL, “a C++ wrapper library around CGAL with the aim of supporting ISO 19107:2013 and OGC Simple Features Access 1.2 for 3D operations” is now an optional include in PostGIS (I believe beginning with 2.1, forgive me if I’m wrong).  This was a quiet outcome of the Boston Code Sprint, after Paul Ramsey declared exact rational number representation would not make its way into PostGIS.

You shall not pass!

(I promise, that’s the only animated gif I’ll ever do, hat tip James Fee who did it for years before it was cool).

What does this mean for a typical PostGIS user? Well, so far it adds a nice suite of new 2D and 3D functions:

ST_Extrude — Extrude a surface to a related volume
ST_StraightSkeleton — Compute a straight skeleton from a geometry
ST_IsPlanar — Check if a surface is or not planar
ST_Orientation — Determine surface orientation
ST_ForceLHR — Force LHR orientation
ST_MinkowskiSum — Perform Minkowski sum
ST_Tesselate — Perform surface Tesselation
ST_Extrude is fun– this is a function for doing things like this:

Extruded footprints from (ahem) City Engine. Ssssh. Don’t tell.

ST_StraightSkeleton does in one step the first phase of what I’ve been going on about for a couple years re: Voronoi diagrams (and bypasses Voronoi altogether):

Skeleton of stream

Plus more! I’ve just started exploring.

BTW, in advance of there being an SFCGAL install guide for PostGIS, a good source for info on install can be gleaned from the PostGIS Developers listserve.

Posted in 3D, Analysis, Database, PostGIS, PostgreSQL, SQL | Tagged: , , , | 2 Comments »