X3D export

DEFINITION

asx3d() is an export function. X3D is the VRML successor and describes an XML syntax. So it's a 3D format which is fine to use in a web environment.


This function is available for the following geometry :

• POINT
• MULTIPOINT
• LINESTRING
• MULTILINESTRING
• POLYGON
• MULTIPOLYGON
• TIN
• POLYHEDRALSURFACE

Here are some example queries of its use :

• POINT/MULTIPOINT LINESTRING/MULTILINESTRING

___________________________________________________________________

SELECT asx3d('POINT(1 2 3)');

SELECT asx3d('MULTIPOINT(1 2 3, 4 5 6)');

SELECT asx3d('LINESTRING(1 2 3, 4 5 6)');

SELECT asx3d('MULTILINESTRING((1 2 3, 4 5 6), (0 0 0, 5 4 6))');
___________________________________________________________________

• TIN and POLYHEDRALSURFACE

______________________________________________________________________________________________________________

SELECT asx3d('TIN(((0 0 0, 1 0 0 , 0 1 0, 0 0 0)),((1 0 0, 0 0 1, 0 1 0, 1 0 0)))');

SELECT asx3d('POLYHEDRALSURFACE(((0 0 0, 0 1 0, 1 1 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)))');
______________________________________________________________________________________________________________



PGSQL2X3D COMMAND

PGSQL2X3D is a command which creates x3d files by exporting geometries from PostGIS.

Here is the help of this command:

__________________________________________________________________________________________________

USAGE : pgsql2x3d [options] database [schema.]table
pgsql2x3d [options] database subquery

OPTIONS :
-f filename
Use this option to specify the name of the file to create.
If not present, the result will be printed on stdout.
-h host
Allows you to specify connection to a database on a machine other than the default.
-p port
Allows you to specify a database port other than the default.
-P password
Connect to the database with the specified password.
-u user
Connect to the database as the specified user.
-g geometry_column
Specify the geometry column to be exported. This option is necessary.
-t texture_name_column
Specify the texture name column to be exported.
-uv texture_coordinates
Specify the texture coordinates column to be exported.
!!!: If one of -t or -uv option is given, the other one must be given too.
-pos position
Specify the observator's position at the beginning.
-gc ground_color
Specify the ground color. (for example : '-gc 0.34 0.7 0.98')
-sc sky_color
Specify the sky color.
-fc faces_color
Specify the color of faces which are not textured.
-?
Display this help screen.


Beware :
If you give a subquery instead of a table name in parameter, the -g -t and -uv options must be the names of the alias you give in the subquery.
Example :
pgsql2x3d -g geom -t tex -uv uvcoord -f testfile mydatabase 'SELECT ST_RotateX(the_geom, 2)
AS geom, the_texture AS tex, coorduv AS uvcoord from mytable'
___________________________________________________________________________________________________



EXAMPLES :

Later...

GEOS : isvalid operator

DEFINITION :

Now, PostGIS is able to store 3D geometries as TIN and POLYHEDRALSURFACE and to import 3D data with the collada2pgsql command.
Functionalities are added in order to manipulate these new geometries. First, it had to implement the isvalid operator in GEOS for TIN and POLYHEDRALSURFACE. GEOS (Geometry Engine Open Source) is a geometric library for PostGIS and the isvalid operator is based on the mathematical definition of geometries.

So isvalid operator check if :

• all faces are connected :

• there are no self-intersections :

• neighbour faces have the same orientation :

It's important to notice that a TIN or a POLYHEDRALSURFACE stored in PostGIS can represent a 3D volume but not be a valid geometry if we refer to its mathematical definition.



EXAMPLES :

Three examples of isvalid operator use :











____________________________________________________________________________________________________

SELECT isvalid('TIN(((0 0 0, 1 0 0, 0 1 0, 0 0 0)), ((1 0 0, 0 0 1, 0 1 0, 1 0 0)))') ;
____________________________________________________________________________________________________










____________________________________________________________________________________________________

SELECT isvalid('TIN(((0 0 0, 1 0 0, 0 1 0, 0 0 0)), ((1 0 0, 0 1 0, 0 0 1, 1 0 0)))') ;
____________________________________________________________________________________________________










____________________________________________________________________________________________________

SELECT isvalid('TIN(((0 0 0, 1 0 0, 0 1 0, 0 0 0)), ((0 0 0, 1 0 0, 1 1 0, 0 0 0)))') ;
____________________________________________________________________________________________________

New geometries in PostGIS

Twelve geometries already exist in PostGIS :
Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, Curve, MultiCurve, PolygonCurve, MultiSurface and GeometryCollection.

Two geometries are added to support 3D geometries in PostGIS, they are some specific MultiPolygon described in OGC SFS1.2 norm.

The SQL syntax is the same for these two new geometries and MultiPolygons :


















MultiPolygon(
-FACE 1- (x11 y11 z11, x12 y12 z12, x13 y13 z13, x14 y14 z14, x15 y15 z15),
-FACE 2- (x21 y21 z21, x22 y22 z22, x23 y23 z23, x24 y24 z24, x15 y15 z15),
-FACE 3- (x31 y31 z31, x32 y32 z32, x33 y33 z33, x34 y34 z34, x35 y35 z35)
) ;

In fact, a MultiPolygon needs five points for a square, because the faces must be closed. Moreover, two contiguous polygons must have the same orientation :









These new geometries are :

• POLYHEDRALSURFACE

Definition : Collection of contiguous 3D polygons which share some edges and describe 3D volume.

SQL syntax : Example for a cube.

POLYHEDRALSURFACE(
-BOTTOM- (0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0),
-LEFT- (0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0),
-FRONT- (0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0),
-TOP- (0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1),
-RIGHT- (1 0 0, 1 1 0, 1 1 1, 1 0 1, 1 0 0),
-BACK- (1 1 0, 0 1 0, 0 1 1, 1 1 1, 1 1 0)
) ;

• TIN

Definition : A Triangular Irregular Network is a POLYHEDRALSURFACE where all Polygons are Triangles.

SQL syntax : Example for a tetrahedron.















TIN(
-BOTTOM- (0 0 0, 1 0 0, 0 1 0, 0 0 0),
-FRONT- (0 0 0, 1 0 0, 0 0 1, 0 0 0),
-LEFT- (0 0 0, 0 0 1, 0 1 0, 0 0 0),
-RIGHT- (0 0 1, 1 0 0, 0 1 0, 0 0 1)
) ;

It's also possible to create 2D TIN or POLYHEDRALSURFACE but their main aim is to describe 3D volumes.

Unfortunately, it's not possible to create TIN & POLYHEDRALSURFACE with MultiRings Polygons. Indeed, their mathematical definition specify that PolyhedralSurfaces can't have holes.

However, some PostGIS export functions are available for these geometries :

• asEWKB
• asEWKT
• asKML
• asSVG (only 2D output)
• asGML (only 2D output)
• Summary

Here is some examples of TIN & POLYHEDRALSURFACE manipulation :

__________________________________________________________________________________________________________

SELECT asEWKT('TIN(((1 2 3, 3 4 5, 4 5 6, 1 2 3)))') ;

SELECT asSVG('POLYHEDRALSURFACE(((0 0, 0 1, 1 1, 1 0, 0 0)), ((1 0, 2 0, 2 1, 1 1, 1 0)))') ;

SELECT asGML('POLYHEDRALSURFACE(((0 0, 0 1, 1 1, 1 0, 0 0)), ((1 0, 2 0, 2 1, 1 1, 1 0)))') ;
__________________________________________________________________________________________________________

COLLADA Import

WHY?

This step applies the import of 3D data in PostGIS. The data will be import from COLLADA format.
COLLADA (COLLAboration Design Asset) is a 3D format which initially developed by Sony®. In fact, it's the official format of the PlayStation 3®.
The aim of this format is to be an EXCHANGE format. Indeed, most of 3D design software such as Blender, Maya or 3DSMAX can export COLLADA. It is also used by Google Earth to represent3D models with informations about colors or textures. Furthermore, in KMZ archives, you can find a kml file or a COLLADA file.

That's why a command which creates a SQL file from COLLADA file, named collada2pgsql has been developed.


MANUAL

Here is the help of this command :

____________________________________________________________________________________________________

USAGE : collada2pgsql [options] colladafile [schema.]table

OPTIONS :
-s
Set the SRID field. If not specified it defaults to -1.
-d
Drops the table, then recreates it with current collada file data.
-c
Appends shape file into current table, must be exactly the same table schema.
!!! : The options -d and -a are mutually exclusive.
-g
Specify the name of the geometry column (mostly useful in append mode).
-t
Import collada file data with informations about textures.
-dir
Specify the directory where the textures will be stored.
default: '/tmp/textures'
-I
Create a GiST index on the geometry column.
-?
Display this help screen.
____________________________________________________________________________________________________

Beware :

If the -g option is not given, the name of the geometry column will be 'the_geom'.

If the -t option is given, the import of the model will be done with information about textures: The user can specify the directory with -dir option, where he wants the textures to be stored (default : /tmp/textures). There also will be two others columns named 'the_texture' which contains the absolute path (or url) of a picture, and 'coorduv' which is a 2D geometry column giving relations between faces and textures.


EXAMPLES :

Let's see an example with the collada file 'Dubai.dae' providing from a GoogleEarth KMZ archive (3DVIA). You can find a lot of these kind of archives here.
Here is the command I have to execute if I want to import it with textures, stored in /home/acarme/texture/Dubai and if I want a give the SRID 27582 to my geometry :

____________________________________________________________________________________________________

collada2pgsql -t -dir /home/acarme/texture/Dubai -s 27582 /home/acarme/Dubai/Dubai.dae mytable
____________________________________________________________________________________________________


Beware, this table (mytable) mustn't exist : because it will be created by the SQL file.

By executing this command, you'll see this in your window :

____________________________________________________________________________________________________

COLLADA
version 1.4.1
building sql file...
--------------------
file /home/acarme/Dubai/Dubai.sql has been created
____________________________________________________________________________________________________







You must execute it from the directory where the collada file stands.


If you don't precise the target directory for the textures , they will be stored in /tmp/textures :


____________________________________________________________________________________________________

collada2pgsql -t -s 27582 /home/acarme/Dubai/Dubai.dae mytable
____________________________________________________________________________________________________


Here is the result, in a SQL file named Dubai.sql :

















Now, we just have to execute the following command where MA_BASE is the name of the database in which I want to import my geometries :

____________________________________________________________________________________________________

psql -U postgres MA_BASE < /home/acarme/Dubai/Dubai.sql
____________________________________________________________________________________________________


You can see on your window :

____________________________________________________________________________________________________

BEGIN
CREATE TABLE
addgeometrycolumn
----------------------------------------------------------
public.mytable.the_geom SRID:27582 TYPE:GEOMETRY DIMS:3

(1 row)

ALTER TABLE
addgeometrycolumn
------------------------------------------------------
public.mytable.coorduv SRID:-1 TYPE:GEOMETRY DIMS:2

(1 row)

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
...
____________________________________________________________________________________________________


If you want to see what stands in mytable, just try this command, after connecting to MA_BASE :

____________________________________________________________________________________________________

SELECT summary(the_geom), the_texture, summary(coorduv) from mytable;

____________________________________________________________________________________________________

and you'll be able to see that in your window :