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

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:

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

## 3 thoughts on “Airspace is complicated — and so I abuse PostGIS once again — Reprise…”

1. GW says:

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!

1. Hi GW,

You will need to filter / change the text fields. SFC is surface, so replace that with 0.

Best

This site uses Akismet to reduce spam. Learn how your comment data is processed.