For this recipe, we will extrude a building footprint of our own making. Let us quickly create a table with a single building footprint, for testing purposes, as follows:

DROP TABLE IF EXISTS chp07.simple_building; 
CREATE TABLE chp07.simple_building AS  
SELECT 1 AS gid, ST_MakePolygon( 
  ST_GeomFromText( 
    'LINESTRING(0 0,2 0, 2 1, 1 1, 1 2, 0 2, 0 0)' 
  ) 
) AS the_geom; 

It would be beneficial to keep the creation of 3D buildings encapsulated as simply as possible in a function:

CREATE OR REPLACE FUNCTION chp07.threedbuilding(footprint geometry, height numeric) 
RETURNS geometry AS 
$BODY$ 

Our function takes two inputs: the building footprint and a height to extrude to. We can also imagine a function that takes in a third parameter: the height of the base of the building.

To construct the building walls, we will need to first convert our polygons into linestrings and then further separate the linestrings into their individual, two-point segments:

WITH simple_lines AS 
( 
  SELECT  
    1 AS gid,  
    ST_MakeLine(ST_PointN(the_geom,pointn), 
    ST_PointN(the_geom,pointn+1)) AS the_geom 
  FROM ( 
    SELECT 1 AS gid, 
    polygon_to_line($1) AS the_geom  
  ) AS a 
  LEFT JOIN( 
    SELECT  
      1 AS gid,  
      generate_series(1,  
        ST_NumPoints(polygon_to_line($1))-1 
      ) AS pointn  
  ) AS b 
  ON a.gid = b.gid 
), 

The preceding code returns each of the two-point segments of our original shape. For example, for simple_building, the output is as follows:

Now that we have a series of individual lines, we can use those to construct the walls of the building. First, we need to recast our 2D lines as 3D using ST_Force3DZ:

threeDlines AS
( 
  SELECT ST_Force3DZ(the_geom) AS the_geom FROM simple_lines 
),

The output is as follows:

The next step is to break each of those lines from MULTILINESTRING into many LINESTRINGS:

explodedLine AS 
( 
  SELECT (ST_Dump(the_geom)).geom AS the_geom FROM threeDLines 
), 

The output for this is as follows:

The next step is to construct a line representing the boundary of the extruded wall:

threeDline AS 
( 
  SELECT ST_MakeLine( 
    ARRAY[ 
      ST_StartPoint(the_geom), 
      ST_EndPoint(the_geom), 
      ST_Translate(ST_EndPoint(the_geom), 0, 0, $2), 
      ST_Translate(ST_StartPoint(the_geom), 0, 0, $2), 
      ST_StartPoint(the_geom) 
    ] 
  ) 
  AS the_geom FROM explodedLine 
), 

Now, we need to convert each linestring to polygon.threeDwall:

threeDwall AS 
( 
  SELECT ST_MakePolygon(the_geom) as the_geom FROM threeDline 
), 

Finally, put in the roof and floor on our building, using the original geometry for the floor (forced to 3D) and a copy of the original geometry translated to our input height:

buildingTop AS 
( 
  SELECT ST_Translate(ST_Force3DZ($1), 0, 0, $2) AS the_geom 
), 
-- and a floor 
buildingBottom AS 
( 
  SELECT ST_Translate(ST_Force3DZ($1), 0, 0, 0) AS the_geom 
), 

We put the walls, roof, and floor together and, during the process, convert this to a 3D MULTIPOLYGON:

wholeBuilding AS 
( 
  SELECT the_geom FROM buildingBottom 
    UNION ALL 
  SELECT the_geom FROM threeDwall 
    UNION ALL 
  SELECT the_geom FROM buildingTop 
), 
-- then convert this collecion to a multipolygon 
multiBuilding AS 
( 
  SELECT ST_Multi(ST_Collect(the_geom)) AS the_geom FROM  
    wholeBuilding 
), 

While we could leave our geometry as a MULTIPOLYGON, we'll do things properly and munge an informal cast to POLYHEDRALSURFACE. In our case, we are already effectively formatted as a POLYHEDRALSURFACE, so we'll just convert our geometry to text with ST_AsText, replace the word with POLYHEDRALSURFACE, and then convert our text back to geometry with ST_GeomFromText:

textBuilding AS 
( 
  SELECT ST_AsText(the_geom) textbuilding FROM multiBuilding 
), 
textBuildSurface AS 
( 
  SELECT ST_GeomFromText(replace(textbuilding, 'MULTIPOLYGON', 
         'POLYHEDRALSURFACE')) AS the_geom FROM textBuilding 
) 
SELECT the_geom FROM textBuildSurface 

Finally, the entire function is:

CREATE OR REPLACE FUNCTION chp07.threedbuilding(footprint geometry,  
  height numeric) 
RETURNS geometry AS 
$BODY$ 
 
-- make our polygons into lines, and then chop up into individual line segments 
WITH simple_lines AS 
( 
  SELECT 1 AS gid, ST_MakeLine(ST_PointN(the_geom,pointn), 
    ST_PointN(the_geom,pointn+1)) AS the_geom 
  FROM (SELECT 1 AS gid, polygon_to_line($1) AS the_geom ) AS a 
  LEFT JOIN 
  (SELECT 1 AS gid, generate_series(1,  
    ST_NumPoints(polygon_to_line($1))-1) AS pointn  
  ) AS b 
  ON a.gid = b.gid 
), 
-- convert our lines into 3D lines, which will set our third  coordinate to 0 by default 
threeDlines AS 
( 
  SELECT ST_Force3DZ(the_geom) AS the_geom FROM simple_lines 
), 
-- now we need our lines as individual records, so we dump them out using ST_Dump, and then just grab the geometry portion of the dump 
explodedLine AS 
( 
  SELECT (ST_Dump(the_geom)).geom AS the_geom FROM threeDLines 
), 
-- Next step is to construct a line representing the boundary of the  extruded "wall" 
threeDline AS 
( 
  SELECT ST_MakeLine( 
    ARRAY[ 
    ST_StartPoint(the_geom), 
    ST_EndPoint(the_geom), 
    ST_Translate(ST_EndPoint(the_geom), 0, 0, $2), 
    ST_Translate(ST_StartPoint(the_geom), 0, 0, $2), 
    ST_StartPoint(the_geom) 
    ] 
  ) 
AS the_geom FROM explodedLine 
), 
-- we convert this line into a polygon 
threeDwall AS 
( 
  SELECT ST_MakePolygon(the_geom) as the_geom FROM threeDline 
), 
-- add a top to the building 
buildingTop AS 
( 
  SELECT ST_Translate(ST_Force3DZ($1), 0, 0, $2) AS the_geom 
), 
-- and a floor 
buildingBottom AS 
( 
  SELECT ST_Translate(ST_Force3DZ($1), 0, 0, 0) AS the_geom 
), 
-- now we put the walls, roof, and floor together 
wholeBuilding AS 
( 
  SELECT the_geom FROM buildingBottom 
    UNION ALL 
  SELECT the_geom FROM threeDwall 
    UNION ALL 
  SELECT the_geom FROM buildingTop 
), 
-- then convert this collecion to a multipolygon 
multiBuilding AS 
( 
  SELECT ST_Multi(ST_Collect(the_geom)) AS the_geom FROM wholeBuilding 
), 
-- While we could leave this as a multipolygon, we'll do things properly and munge an informal cast 
-- to polyhedralsurfacem which is more widely recognized as the appropriate format for a geometry like 
-- this. In our case, we are already formatted as a polyhedralsurface, minus the official designation, 
-- so we'll just convert to text, replace the word MULTIPOLYGON with POLYHEDRALSURFACE and then convert 
-- back to geometry with ST_GeomFromText 
 
textBuilding AS 
( 
  SELECT ST_AsText(the_geom) textbuilding FROM multiBuilding 
), 
textBuildSurface AS 
( 
  SELECT ST_GeomFromText(replace(textbuilding, 'MULTIPOLYGON',  
    'POLYHEDRALSURFACE')) AS the_geom FROM textBuilding 
) 
SELECT the_geom FROM textBuildSurface 
; 
$BODY$ 
  LANGUAGE sql VOLATILE 
  COST 100; 
ALTER FUNCTION chp07.threedbuilding(geometry, numeric) 
  OWNER TO me;