A little beginner article about PostGIS and 3D: becoming a PostGIS Fan more and more, I was wondering how far we can go with with 3D Geometries in PostGIS. So the first obstacle I had is: pgAdmin can wonderfully display 2D geometries even on OpenStreetMaps - but no 3D. After failing with with GIS- and CAD-Tools, PostGIS-Contributor Regina Obe pointed me to x3dom and PostGIS’ built-in export for this via Twitter. So I decided to write this little intro.

Display a simple Cube on my homepage

First, I show how to display the “HelloWorld Cube” of x3dom on this Homepage - which is very easy and why I liked this approach:

you have to load one JavaScript and one CSS on your page:

<script type='text/javascript' src='http://www.x3dom.org/download/x3dom.js'> </script> 
<link rel='stylesheet' type='text/css' href='http://www.x3dom.org/download/x3dom.css'></link> 

Then, you can just use x3dom’s markup:

<x3d width='500px' height='400px'>
    <scene>
        <shape>
            <appearance>
                <material diffuseColor='1 0 0'></material>
            </appearance>
            <box></box>
        </shape>
    </scene>
</x3d>

…which will result in this Cube: (you can rotate it the mouse or with one or two fingers on a touch device):

That’s all. Nice, no?

Creating a Cube in PostGIS

Next little step:

How to I create a 3D Geometry in PostGIS?

Well, also this is simple: just use PostGIS plain SQL, like this example stolen from theh PostGIS Page:

insert into geopart (plan, description, shape) values ('3d', 'unitcube', geometry('
POLYHEDRALSURFACE Z (
  ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
  ((0 0 0, 0 1 0, 0 1 1, 0 0 1, 0 0 0)),
  ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
  ((1 1 1, 1 0 1, 0 0 1, 0 1 1, 1 1 1)),
  ((1 1 1, 1 0 1, 1 0 0, 1 1 0, 1 1 1)),
  ((1 1 1, 1 1 0, 0 1 0, 0 1 1, 1 1 1))
)
'))

As Database Schema I used this “Hello World Schema”:

create extension if NOT EXISTS postgis; 

-- Experiment 1:
CREATE TABLE if not EXISTS public.geopart
(
    id serial primary key, 
    plan text not null, 
    description text not null, 
    shape geometry
); 

Next, I just query the datbase, using PostGIS’ function :

select ST_AsX3D(shape) 
from geopart
where plan = '3d'

…and put the result into the same x3dom-scaffold as above:

<x3d width='500px' height='400px'>
    <scene>
        <shape>
            <appearance>
                <material diffuseColor='1 0 0'></material>
            </appearance>
            <IndexedFaceSet convex='false'  coordIndex='0 1 2 3 -1 4 5 6 7 -1 8 9 10 11 -1 12 13 14 15 -1 16 17 18 19 -1 20 21 22 23'><Coordinate point='0 0 0 0 1 0 1 1 0 1 0 0 0 0 0 0 1 0 0 1 1 0 0 1 0 0 0 1 0 0 1 0 1 0 0 1 1 1 1 1 0 1 0 0 1 0 1 1 1 1 1 1 0 1 1 0 0 1 1 0 1 1 1 1 1 0 0 1 0 0 1 1' /></IndexedFaceSet>

        </shape>
    </scene>
</x3d>

this sould now make our little tesselated surface.