[postgis-users] Google Earth/PostGIS integration

[Chetna Parbhoo]

I am currently creating a spatial database for a SME. Their project data is currently within folders (including all GIS data). I have been doing a bit of research when I came across PostgreSQL and PostGIS. I plan to use PostGreSQl with PostGIS as a spatial extender. I have installed the PostGreSQL and PostGIS as well as viewed data stored in the database with QGIS. I need an easy method whereby the employees in the company can access data from the database on a user friendly interface. Everyone in the company is familiar with Google Earth. Is there a way that I can link Google Earth with PostGIS to achieve this?  

 

Many thanks.

 

 

Chetna Parbhoo

GIS specialist

che...@gcs-sa.biz

 

63 Wessel Road Woodmead
PO Box 2597 Rivonia 2128
South Africa

Tel +27(0)11 803 5726
Fax +27(0)11 803 5745

www.gcs-sa.biz

 

[Pedro Doria Meunier]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Chetna,

Quick answer: yes you can! :)

Through *network link* in Google Earth.

Take the following example (in PHP, use you favourite "poison" ):

1st part - Create the network link to feed to Google Earth
<?php
header("Content-type: application/vnd.google-earth.kml xml kml");
header('Content-Disposition: attachment; filename="g_loc.kml"');
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
echo "<kml xmlns=\"http://earth.google.com/kml/2.1\">";
echo "<Document>";
echo "<name>"._("My Network Link")."</name>";
echo "<NetworkLink>";
echo "<name>"._("My Description")."</name>";
echo "<flyToView>1</flyToView>";
echo "<Link>";
echo
"<href>$mysite/show_my_thing.php?param1=$param1&amp;param2=$param2</href>";
echo "<refreshMode>onInterval</refreshMode>";
echo "<refreshInterval>30</refreshInterval>";
echo "</Link>";
echo "</NetworkLink>";
echo "</Document>";
echo "</kml>";
?>

2nd part - Get your data:

<?php
// connect to database
include('./include/my_database_connection_script.php');

// start your kml document.
$response = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
$response .="<kml xmlns=\"http://earth.google.com/kml/2.1\">";
$response .= "<Document>";

$sql="SELECT my_label, astext(my_geom_field) AS location FROM my_table";
$myresult = pg_exec($connection, $sql);

for ($i=0; $i<pg_numrows($myresult); $i++) {
$my_label=pg_result($myresult, $i, 0);
$my_location = pg_result($myresult, $i, 1);

// extract the geometry
$geometry = str_replace("POINT(","", pg_result($myresult, $g, 1));
$geometry = str_replace(")","",$geometry);
$coordinate = explode(" ",$geometry);
$lon=$coordinate[0];
$lat=$coordinate[1];

// continue filling your document
$response .= "<Placemark>";
$response .= "<name>$my_label</name>";
$response .= "<description><![CDATA[your
description]]></description>";
$response .= "<Style>";
$response .= "<IconStyle>";
$response .= "<scale>1</scale>";
$response .= "<Icon>";
$response .= "<href>$my_site/$my_icon</href>";
$response .= "</Icon>";
$response .= "</IconStyle";
$response .= "<LabelStyle>";
$response .= "<color>ff00ffff</color>";
$response .= "<scale>0.9</scale>";
$response .= "</LabelStyle>";
$response .= "</Style>";
$response .= "<Point>";
$response .= "<coordinates>$lon,$lat,$altitude</coordinates>";
$response .= "</Point>";
$response .= "</Placemark>";
} // result loop

$response .= "</Document>"; // close your document
$response .= "</kml>"; // close your kml
echo $response; // feed it to the network link

pg_close($connection); // don't forget to close the
connection...
?>

This is an example for POINT geometry, of course. Other types are as
easily managed as this.
Your "interface" would be Google Earth :]

You can of course have some "middleware" in a form of a php web page
that allows users to select what they want and then feed it to Google
Earth.

Hope this helps,
BR,
Pedro Doria Meunier

On 11/16/2009 10:49 AM, Chetna Parbhoo wrote:
>
> I am currently creating a spatial database for a SME. Their project
> data is currently within folders (including all GIS data). I have
> been doing a bit of research when I came across PostgreSQL and
> PostGIS. I plan to use PostGreSQl with PostGIS as a spatial
> extender. I have installed the PostGreSQL and PostGIS as well as
> viewed data stored in the database with QGIS. I need an easy method
> whereby the employees in the company can access data from the
> database on a user friendly interface. Everyone in the company is
> familiar with Google Earth. Is there a way that I can link Google
> Earth with PostGIS to achieve this?
>
>
>
> Many thanks.
>
>
>
>
>
> Chetna Parbhoo
>
> GIS specialist
>

> che...@gcs-sa.biz <mailto:che...@gcs-sa.biz>

>
>
>
> 63 Wessel Road Woodmead
> PO Box 2597 Rivonia 2128
> South Africa
>
>
> Tel +27(0)11 803 5726
> Fax +27(0)11 803 5745
>

> www.gcs-sa.biz <http://www.gcs-sa.biz/>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgi...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAksBQccACgkQ2FH5GXCfxAviywCfZY0R/zgoQogO9kSA8f+NaqZ5
J5wAoIFi+IbdEfVAY336xCXd9d/5ct30
=oXxJ
-----END PGP SIGNATURE-----

_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Andreas Neumann]

Yes, as pedro said you can write your own script (PHP, Perl, Python, Java,
whatever) and use the Postgis AsKML() command. Network Links can embed
db-resources from such a script from a parent KML file.

Another option would be to use Geoserver or gdal/ogr or FME (commercial).

Many GIS also provide KML output, as an example, since you already use
QGIS, there is a KML export plugin from QGIS.

Hope this helps as a starter.
Andreas

On Mon, November 16, 2009 11:49 am, Chetna Parbhoo wrote:
> I am currently creating a spatial database for a SME. Their project data
> is currently within folders (including all GIS data). I have been doing
> a bit of research when I came across PostgreSQL and PostGIS. I plan to
> use PostGreSQl with PostGIS as a spatial extender. I have installed the
> PostGreSQL and PostGIS as well as viewed data stored in the database
> with QGIS. I need an easy method whereby the employees in the company
> can access data from the database on a user friendly interface. Everyone
> in the company is familiar with Google Earth. Is there a way that I can
> link Google Earth with PostGIS to achieve this?
>
>
>
> Many thanks.
>
>
>
>
>
> Chetna Parbhoo
>
> GIS specialist
>

> che...@gcs-sa.biz <mailto:che...@gcs-sa.biz>

>
>
>
> 63 Wessel Road Woodmead
> PO Box 2597 Rivonia 2128
> South Africa
>
>
>
>
> Tel +27(0)11 803 5726
> Fax +27(0)11 803 5745
>

> www.gcs-sa.biz <http://www.gcs-sa.biz/>
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgi...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--
Andreas Neumann
http://www.carto.net/neumann/
http://www.svgopen.org/

[Chetna Parbhoo]

Thank you Andrea and Pedro. Much appreciated. I'm going to attempt this
and will let you know my progress. Wish me luck!

[Chetna Parbhoo]

Hi there

Thanks for your help below Pedro. As I was a beginner, I had to do quite
a bit of learning before I could understand your instructions below. I
installed an apache server. I am able to connect to a Postgres database
using PHP scripting.

From the first piece of code (to create the Network Link) I am able to
run the projects.php code.

<?php
header("Content-type: application/vnd.google-earth.kml xml kml");
header('Content-Disposition: attachment; filename="g_loc.kml"');
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"; echo "<kml
xmlns=\"http://earth.google.com/kml/2.1\">";
echo "<Document>";

echo "<name>'A Network Link to Google Earth'</name>"; echo
"<NetworkLink>"; echo "<name>'GCS Projects'</name>"; echo

"<flyToView>1</flyToView>"; echo "<Link>"; echo

"<href>http://localhost:8080/projects.php</href>";
echo "completed execution of projects";

echo "<refreshMode>onInterval</refreshMode>";
echo "<refreshInterval>30</refreshInterval>";
echo "</Link>";
echo "</NetworkLink>";
echo "</Document>";
echo "</kml>";
?>

Thereafter I am able to connect to the database

//Projects.php

<?php
// connect to database

include('connection.php');

// start your kml document.

$response = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"; $response .=
"<kml xmlns=\"http://earth.google.com/kml/2.1\">";

$response .= "<Document>\n";

$sql="SELECT gcs_projects.project_num, astext(proj_geom) AS location
FROM gcs_projects"; $myresult = pg_exec($db_handle, $sql); echo
"executed sql";

for ($i=0; $i<=pg_numrows($myresult); $i++) {

$my_label=pg_result($myresult, $i, 0);
$my_location = pg_result($myresult, $i, 1);

// extract the geometry
$geometry = str_replace("POINT(","", pg_result($myresult, $i, 1));

$geometry = str_replace(")","",$geometry);
$coordinate = explode(" ",$geometry);
$lon=$coordinate[0];
$lat=$coordinate[1];

$altitude=1000;

// continue filling your document
$response .= "<Placemark>";

$response .= "<name>$my_label=i</name>";

$response .= "<description><![CDATA[your
description]]></description>";
$response .= "<Style>";
$response .= "<IconStyle>";
$response .= "<scale>1</scale>";
$response .= "<Icon>";

$response .= "<href>C:/water.png</href>";

$response .= "</Icon>";
$response .= "</IconStyle";
$response .= "<LabelStyle>";
$response .= "<color>ff00ffff</color>";
$response .= "<scale>0.9</scale>";
$response .= "</LabelStyle>";
$response .= "</Style>";
$response .= "<Point>";
$response .= "<coordinates>$lon,$lat,$altitude</coordinates>";
$response .= "</Point>";
$response .= "</Placemark>";

} // result loop

$response .= "</Document>"; // close your document

$response .= "</kml>\n"; // close your kml

echo $response; // feed it to the network link

pg_close($db_handle); // don't forget to close the
connection...
echo "connection closed";
?>

The contents of the g_loc.kml is as follows:

<?xml version="1.0" encoding="UTF-8"?><kml
xmlns="http://earth.google.com/kml/2.1">
<Document>
<name>'A Network Link to Google Earth'</name>
<NetworkLink><name>'GCS Projects'</name>
<flyToView>1</flyToView>
<Link><href>http://localhost:8080/projects.php</href>
completed execution of projects
<refreshMode>onInterval</refreshMode>
<refreshInterval>30</refreshInterval>
</Link>
</NetworkLink>
</Document></kml>

Obviously the geometries are not being written to the KML file. Am I
missing a step to write each geometry in the for loop.

// SELECT ST_AsKML(ST_GeomFromText('POINT($lon,$lat))',4326));

Also, at the point you are extracting the geometry, you have a variable
named $g. This is supposed to be $i.

I am obviously missing an important step. Your help will be appreciated.

Thanks,
Chetna

-----Original Message-----
From: postgis-us...@postgis.refractions.net
[mailto:postgis-us...@postgis.refractions.net] On Behalf Of
Pedro Doria Meunier
Sent: 16 November 2009 02:13 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Google Earth/PostGIS integration