mercredi 14 novembre 2007

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
_______________________________________________________________________________________________________________












-

2 commentaires:

aswinindra a dit…

Hi,
I am graduate student from Shibaura Institute of Technology. My research is to syncronize 3D GIS data into game engine. Since I have to query 3D data from PostGIS (I use GrassGIS) into Blender, your script (PGSQL2KMZ) it seem very useful to study.
By any possibility, could you give me the link so I could download the script?
Thank you very much

Mathieu a dit…

Hi,
the source code is now available on this blog. If you want to see the code of PGSQL2KMZ, it's located here:

postgis/loader/pgsql2kmz.c