Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Posts Tagged ‘PostGIS Cookbook’

Packt Sale — 2 books for the price of one

Posted by smathermather on March 25, 2014

Now I feel like a salesman. I feel obliged to mention the Packt buy one get one free sale (ends tomorrow):

http://www.packtpub.com/?utm_source=referral&utm_medium=marketingPR&utm_campaign=2000thTitle

If you want a postgresql title to go with the PostGIS Cookbook (just sayin’):

http://www.packtpub.com/search?keys=postgresql&sort=0&types=0&forthcoming=1&available=1&count=20&op=Go

Ok. Enough. Back to fun reading:

An map of Mount Pleasant

 

Posted in PostGIS, PostGIS Cookbook | Tagged: | Leave a Comment »

PostGIS Cookbook(s)

Posted by smathermather on March 5, 2014

This is starting to feel real…

PostGIS Cookbook(s).  2904 pages.

6 PostGIS Cookbooks

Page count for a single book can be calculated as in the following code:

WITH distinction AS (
    SELECT DISTINCT( the_text) AS pages FROM postgis_cookbook
SELECT COUNT (pages) AS "Count of Pages" FROM distinction;

The preceding query will result in the following:

Count of Pages
--------------
484

Feeling real

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 out Regina and Leo’s book, PostGIS in Action. Their second edition is already on pre-order. I got to read a bit of it last year, and it promises to be a really epic edition. I still reference the 1st edition myself. I wish I had had it when I was starting in PostGIS… .

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

Book Complete

Posted by smathermather on January 27, 2014

For all following along at home, the PostGIS Cookbook is complete!  At 484 pages it’s a tome– but a great one (It’s been out for a few days, actually).

A shout out to my brilliant co-authors, Paolo, Bborie, and Tom. I could not have hoped for better.

http://www.packtpub.com/postgis-to-store-organize-manipulate-analyze-spatial-data-cookbook/book

And for any who wonder, the picture on the front is of the Maumee River, the largest tributary to the Great Lakes.

bridge over the maumee

Posted in PostGIS | Tagged: , | Leave a Comment »

Final “clubbed” book. Last read through…

Posted by smathermather on January 16, 2014

image

Posted in Other, PostGIS, PostgreSQL | Tagged: , | Leave a Comment »

Editing in progress

Posted by smathermather on January 9, 2014

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… .

image

Posted in Database, PostGIS, PostgreSQL, SQL | Tagged: , , , , | 6 Comments »

2.5D TINs in PostGIS

Posted by smathermather on December 18, 2013

(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:

SELECT
    ST_AsText(ST_DelaunayTriangles(
       ST_Union(ST_GeomFromText(
       'POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))'), 
       ST_Buffer(ST_GeomFromText(‘POINT(110 170)’), 20) ),0.001,2) )
    AS dtriag;

For the record, my code is about 2% slower on a dataset that takes ~50minutes to triangulate .

——————————————
Original Post
——————————————

Ever since ST_Delaunay made its way into PostGIS with GEOS 3.4, there’s been the promise of TINs from geometries in PostGIS. The promise is for 2.5D TINs.  What are these, and why aren’t they 3D? It’s all about overhangs– imagine the edge of a building.  A 3D TIN can handle that building edge with a true vertical wall, or even overhang.  A 2.5D TIN is like a 2.5D raster– no overlaps allowed.

With those limitations in mind, you can have TINs today if you want:
https://github.com/smathermather/postgis-etc/edit/master/3D/AsTin.sql

-- A small function to convert ST_Delaunay (requires GEOS 3.4) to a 2.5D Tin
-- Uses the hackerish approach of converting to text, and doing string replacement
--- for format conversion.

-- A Delaunay triangulation does not by itself formally make a TIN.  To do this,
-- we will require some modification to the output format.  If we perform an ST_AsText
-- on our new geometries we will see that they are POLYGON Z's wrapped in a
-- GEOMETRYCOLLECTION.

-- Thus there are two ways in which this is not a TIN-- the internal POLYGON Z are
-- implicitly triangles, but explicitly POLYGON Zs.  In addition, the external wrapper
-- for the collection of triangles is a GEOMETRYCOLLECTION, not a TIN.
-- Once that we have this geometry in text form, the easiest way to fix this is
-- with string replacement to fix these two things, then convert back to binary

-- We'll go from e.g. this:
-- GEOMETRYCOLLECTION Z (POLYGON Z ((-1.14864 0.61002 -2.00108,-0.433998 -0.0288903 -2.13766, ...
-- to this:
-- TIN ( ((-1.14864 0.61002 -2.00108,-0.433998 -0.0288903 -2.13766, ...

CREATE OR REPLACE FUNCTION AsTIN(geometry)
  RETURNS geometry AS
$BODY$

WITH dt AS
(
SELECT ST_AsText(ST_DelaunayTriangles(ST_Collect($1))) AS atext
),
replacedt AS
(
-- Remove polygon z designation, as TINs don't require it.
SELECT replace(atext, 'POLYGON Z', '') as ttext
  FROM dt
),
replacegc AS
(
-- change leading declaration to TIN
SELECT replace(ttext, 'GEOMETRYCOLLECTION Z', 'TIN Z') AS tintext
  from replacedt
),
tingeom AS
(
-- Aaaand convert back to binary.  Voila!
SELECT ST_GeomFromEWKT(tintext) AS the_geom FROM replacegc
)

SELECT the_geom FROM tingeom

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

Here’s an example TIN derived from UAS (non-weaponized drone) imagery:
Image showing UAS derived TIN

Posted in 3D, Analysis, Database, Drone, Other, Photogrammetry, PostGIS, PostgreSQL, SQL, UAS | Tagged: , , , , , | 6 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 »

UAS (drone) Footprint Geometries Calculated in PostGIS with SFCGAL

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:

Figure showing overlapping viewing footprints from images from UAS flight.

Posted in Other | 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 »