Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Airspace is complicated — and so I abuse PostGIS once again — Reprise…

Posted by smathermather on October 25, 2014

In the previous post: https://smathermather.wordpress.com/2014/10/25/airspace-is-complicated-and-so-i-abuse-postgis-once-again/ we explore the 3D shape and complexity of controlled airspace.

Now here’s the rest of the code. We’ll add our affine transformation ala Seth Fitsimmons:

    SELECT 
        ST_Affine(
            ST_Rotate(geom, -pi() / 2),
            -- isometric
            cos(pi() / 6), -cos(pi() / 6), 0,
            sin(pi() / 6), sin(pi() / 6), 1,
            0, 0, 0,
            0, 0, 0
        )
    AS geom

And integrate that into our original function:

-- Inputs are a geometry and an elevation to move the geometry to
CREATE OR REPLACE FUNCTION threed_iso(footprint geometry, elevation numeric)
  RETURNS geometry AS
$BODY$

-- Force 3D, then translate to the input elevation
WITH floor AS
(
    SELECT ST_Translate( ST_Force3DZ(footprint), 0, 0, elevation ) AS geom
),
-- Now make isometric (and begin Seth Code)
iso AS
(
    SELECT 
        ST_Affine(
            ST_Rotate(geom, -pi() / 2),
            -- isometric
            cos(pi() / 6), -cos(pi() / 6), 0,
            sin(pi() / 6), sin(pi() / 6), 1,
            0, 0, 0,
            0, 0, 0
        )

    AS geom
    FROM floor
)
-- We'll force it back to 3D so QGIS is happy
SELECT ST_Force2D(geom) FROM iso
;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

And voila!

DROP TABLE IF EXISTS class_c_isoc;

CREATE TABLE class_c_isoc AS
	SELECT gid, airspace, name, lowalt, highalt, threed_iso(geom, lowalt::numeric * 5) AS geom
	FROM class_c_subset;

Let’s take a look at Washington, DC and surrounds, another nice complicated example:

3D Figure of DC controlled airspace

3D Figure of DC controlled airspace

And again with map tiles by Stamen Design, under CC BY 3.0. Data by OpenStreetMap, under ODbL:

3D Figure of DC controlled airspace

3D Figure of DC controlled airspace

 

3 Responses to “Airspace is complicated — and so I abuse PostGIS once again — Reprise…”

  1. […] Airspace is complicated — and so I abuse PostGIS once again — Reprise… […]

  2. GW said

    Hi Stephen, I was following along in your airspace posts and when I do “CREATE TABLE class_c_iso…”, Im getting the error “ERROR: invalid input syntax for type numeric: “SFC” SQL state: 22P02″ , did you come across this when you attempted the same thing. I cant figure out why Im getting this error – I am fairly new with postgis. Any thoughts on how I can fix this? I have postgis 2.1.3 installed. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: