What you need to use these functionalities

-





If you want to use these functionalities, and store 3D models in PostGIS, you first must have an installation of postgreSQL. You can get it on its website.


You also must install the following softwares:

• PROJ4

Proj4 is a Cartographic Projections Library. You can download it on its website

• GEOS

Geometry Engine Open Source is a library of geometry computation

You must install the version which is available on this blog if you want to use the 3D functionalities.



download GEOS

• POSTGIS

A plugin for PostgreSQL which allows to store geographic data.
You also need the version available on this blog to use the 3D functionalities.



download PostGIS







After installing PostgreSQL, you must install the other softwares in the following order:

1. PROJ4
2. GEOS
3. PostGIS.

• INSTALLATION OF PROJ4:

execute these commands in the root directory:



_____________________

./configure
make
sudo make install
sudo ldconfig
_____________________

• INSTALLATION OF GEOS

execute these commands in the root directory:



_____________________

./configure
make
sudo make install
sudo ldconfig
_____________________

• INSTALLATION OF POSTGIS

execute these commands in the root directory:



______________________________________________

./configure --with-proj --with-geos --with-pgsql
make
sudo make install
______________________________________________





Now, you're ready to store 3D models in PostGIS.





-

X3D export

-




Another export function I decided to create is asx3d(). 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:

• 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:



I also developed 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.

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:




In these examples the names of the database, the table, the geometry_column, the texture_column and the texture_coordinates column are respectively MABASE mytable the_geom, the_texture and coorduv.

• simple X3D export:

_______________________________________________________________

pgsql2x3d -u postgres -f testfile -g the_geom MABASE mytable
_______________________________________________________________



If you execute this command, you'll see see this in your window



_________________________________

ask for connection to MABASE
connexion ok
query ok
connexion closed
file testfile.x3d has been created
_________________________________





You need an X3D viewer to see testfile.x3d. X3D is the successor of VRML, so, most of VRML viewers works for X3D too. You can use for example freewrl which is a free software you can get here.



By executing this command:

____________________

freewrl testfile.x3d
____________________




you'll see this:

• X3D export with textures:

_____________________________________________________________________________________________

pgsql2x3d -u postgres -f testfile -g the_geom -t the_texture -uv coorduv MABASE mytable
_____________________________________________________________________________________________



If you execute this command, you'll see see this in your window



_________________________________

ask for connection to MABASE
connexion ok
query ok
connexion closed
file testfile.x3d has been created
_________________________________







By executing this command


____________________

freewrl testfile.x3d
____________________


you'll see this:










including X3D in a web browser:




An advantage of X3D format is that it's easy to use in a web environment because of its XML syntax.



Here is an example with two files you can get here. If you place them on a server, change the path of the file x3d.php in the index.htm one and put the query that interests you, you can get this result in a web browser at the right url:







Unfortunately, x3d plugins for web browthers aren't really competitive for now. The best solution seems to be the use of FLUX Player, available on MediaMachines website, and Internet explorer on Windows. Indeed, consequents geometries could need about a minute to be loaded.





-

GoogleEarth export

-





The last part of my work consisted in developing some export function.


The first one is the function ascollada. It works only with MULTIPOLYGONS, TIN and POLYHEDRALSURFACE.


Here is are some examples of its use:

• with a TIN:

_________________________________________________________________________________

SELECT ascollada('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)))');
_________________________________________________________________________________

• with a POLYHEDRALSURFACE:

__________________________________________________________________________________________________________

SELECT ascollada('POLYHEDRALSURFACE(((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)))');
__________________________________________________________________________________________________________














PGSQL2KMZ command:



I also developed a command named pgsql2kmz which create GoogleEarth models with geometries providing from PostGIS.


Here is the help of this command:










__________________________________________________________

USAGE: pgsql2kmz [options] database [schema.]table
pgsql2kmz [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.
-r range
Specify the altitude of the camera at the beginning.
-kml
Export the geometries in kml format which doesn't support textures.
-c
Concatenate the kml geometries in one multigeometry. It allows to see
all geometries in GoogleEarth. Must be used only with -kml option.
-kmz
Create a kmz archive.
-lat latitude
Specifies the latitude of the model if it's exported in collada format
(the default one) with -kmz option.
-lat longitude
Specifies the longitude of the model if it's exported in collada format
(the default one) with -kmz option.
-?
Display this help screen.

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'
__________________________________________________________









So it's possible to create a 3D model for Google Earth in collada or kml format and to store it in a kmz archive. KML doesn't support informations about textures instead of collada, so if you export a model in kml, you'll see it in gray with the GoogleEarth browser. If you decide to export it in collada (default mode) without informations about textures, the browser will choose a random color for any face of your model.



Let's see different examples of the use of this command where database, table, geometry_column, texture_name_column and texture_coordinates_column names are respectively MABASE, mytable, the_geom, the_texture and coorduv:
The geometries which are exported are the geometries we imported with collada2pgsl (pompidou.dae).

• kml export:

__________________________________________________________________________

pgsql2kmz -f testfile -u postgres -g the_geom -kml -c MABASE mytable
__________________________________________________________________________



that will happen in your window:


___________________________________

ask for connexion to MABASE
connexion ok
query ok
generation of kml geometries
geometries created
file testfile.kml has been created
connexion closed
___________________________________







The kml export use the native function askml() of PostGIS. So, the geometry you want to export must have a SRID.


The -c option is really important if you do kml export because it concatenates all geometries and allows to see the full model in GoogleEarth.


Now, if you open testfile.kml with GoogleEarth, you'll see this:





If you add -kmz option, the kml file will be stored in a kmz archive but the result will be the same.

• collada export:

___________________________________________________________________

pgsql2kmz -f testfile -u postgres -g the_geom MABASE mytable
___________________________________________________________________



that will happen in your window:


___________________________________

ask for connexion to MABASE
connexion ok
query ok
generation of collada geometries
geometries created
file testfile.dae has been created
connexion closed
___________________________________








It's possible to see testfile.dae in GoogleEarth by switching it directly in the window. We didn't specify textures options in the command, so the colors will be chosen by the browser:






If you add the -kmz option, the result will be the same, but the collada file will be contained in a kmz archive.

• collada with textures export:

_________________________________________________________________________________________

pgsql2kmz -f testfile -u postgres -g the_geom -t the_texture -uv coorduv MABASE mytable
_________________________________________________________________________________________




that will happen in your window:


___________________________________

ask for connexion to MABASE
connexion ok
query ok
generation of kml geometries
geometries created
file testfile.kml has been created
___________________________________








By switching testfile.dae in GoogleEarth window, you'll see that:






This command creates a collada file and also a directory named textures which contains all the pictures that map the model. So it's better to use it with the -kmz option which will store these two files in an archive. It must be used with -lat and -lg options which specifies the latitude and longitude of the model. These informations will be contained in the doc.kml file which georeferences the model. The result is a single file named testfile.kmz you just have to open with GoogleEarth:



_______________________________________________________________________________________________________________

pgsql2kmz -f testfile -u postgres -g the_geom -t the_texture -uv coorduv -kmz -lat 45.34 -lg 7.237 MABASE mytable
_______________________________________________________________________________________________________________












-

COLLADA Import

-





The second step of my work consisted in importing some 3D data in PostGIS. I decided to import data from the Collada format. Collada (COLLAborative Design Asset) is a 3D format which was initially developed by Sony. Indeed, it's the official format of the Playstation 3. Its aim is to be an exchange format. Most of 3d design software such as Blender, Maya, 3DSMax, can export collada. It is also used by GoogleEarth to represent 3d models with informations about colors or textures. In KMZ archives, you can find a kml file or a collada file.
So I developed a command which creates a SQL file from a collada file named collada2pgsql.


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.

__________________________________________________________










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.









EXAMPLE:



Let's see an example with the collada file 'pompidou.dae' providing from a GoogleEarth KMZ archive. 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/textures/pompidou and if I want a give the SRID 27582 to my geometry:




_______________________________________________________________________________________________

collada2pgsql -t -dir /home/mleserre/Desktop/textures/pompidou -s 27582 pompidou.dae mytable
_______________________________________________________________________________________________



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


_____________________________________

COLLADA
version 1.4.1
building sql file...
------------------------------
file pompidou.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 pompidou.dae mytable
_________________________________________________




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





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


_________________________________________

psql -U postgres MABASE < pompidou.sql
_________________________________________











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

_____________________________________________________________________________

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

_____________________________________________________________________________

and you'll see that in your window:

-

FOSS4G 2007

-





This work has been exposed during the FOSS4G 2007. The FOSS4G (Free & Open Source Software For Geospatial) is an annual international conference where all the geospatial open source community can meet. This year, it took place in Victoria, in British Columbia state CANADA, the last week of september.

The presentation was made by Olivier Courtin from Camptocamp, and the slides of the presentation are available here.

It talks a lot about web applications using X3D, but unfortunatly, because X3D plugins don't seems to be enough matured, we decided to reorient the work on PostGIS 3D export and the texture problematic of 3D models.
The description of the presentation is availble here.





-

GEOS isvalid operator

-





Now, PostGIS is able to store 3D geometries as TIN and POLYHEDRALSURFACE and to import 3D data with the collada2pgsql command.
I decided to add functionalities in order to manipulate these new geometries. First, I implemented 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 It must 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.



Some exemples of the isvalid operator use:









-

Adding new geometries in PostGIS

-





The first aim of my work was to add the support of 3D geometries in PostGIS. We have 12 geometries already existing in it : Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, Curve, MultiCurve, PolygonCurve, MultiSurface, GeometryCollection. The two geometries I added are some specific MultiPolygon described in OGC SFS1.2 norm. They are:

• TIN for Triangular Irregular Network
• POLYHEDRALSURFACE.

This last one is a collection of contiguous 3D Polygons which share some edges and describe a 3D volume. A TIN is a POLYHEDRALSURFACE where all Polygons are Triangles.

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

• -MULTIPOLYGON(((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)))
• -TIN(((0 0 0, 1 0 0, 0 1 0, 0 0 0)),((0 0 0, 1 0 0, 0 0 1, 0 0 0)),((0 0 0, 0 0 1, 0 1 0, 0 0 0)),((0 0 1, 1 0 0, 0 1 0, 0 0 1)))

This TIN describes a tetrahedron.

• POLYHEDRALSURFACE(((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)),((1 0 0, 1 1 0, 1 1 1, 1 0 1, 1 0 0)),((1 1 0, 0 1 0, 0 0 1, 1 1 1, 1 1 0)))
  

This POLYHEDRALSURFACE describes a cube






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

It's not possible to create TIN & POLYHEDRALSURFACE with Multirings Polygons. It's in their mathematical definition, faces of PolyhedralSurfaces can't have holes.





Some PostGIS export functions are available for these geometries:

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

Here are some examples of TIN and 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 1, 1 0)))');
______________________________________________________________________________________________________













-