[postgis-users] Google Earth/PostGIS integration
Chetna Parbhoo
Chetna at gcs-sa.biz
Mon Jan 11 02:40:51 PST 2010
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-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at 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
-----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¶m2=$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
>
> chetna at gcs-sa.biz <mailto:chetna at 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
> postgis-users at 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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list