Kyle,
Your php script worked perfectly. Thank you!
I've now written a SQL query that creates a relative http link within
respective database rows that points to those kml files.
Finally a separate php script outputs all database records to a web
page. From that page a one can now click on one of over a thousand
sortable records to view the corresponding kml file (in my case these
are watershed polygons of pacific salmon populations). There are
likely better ways of doing this, but this is exactly what I was
shooting for this time around.
Anyone that is interested can view the huge table here (http://
dev.riverchange.org/postgis2KMLtable.html) to see what I am getting at.
Thanks for your help!
Dane
ps. I've pasted the SQL query that embeds the html link, and the two
php scripts BELOW in case anyone else is interested in hacking around
with this approach.
---------------------------------
PORTION of SQL QUERY TO EMBED HTML LINK TO "GID" field
select gid, '<a href=' || '"kml/' || gid || '.kml">' || population
|| '</a>' as Link from salmon_populations;
---------------------------------
PHP SCRIPT TO WRITE LOTS OF KML FILES
<?php
$first_header = '<?xml version="1.0" encoding="UTF-8"?><kml
xmlns="http://earth.google.com/kml/2.2";><Document><name>';
$second_header = '</name><Style id="sh_ylw-
pushpin_copy0"><IconStyle><color>0a000000</color><scale>0</
scale><Icon><href>http://maps.google.com/mapfiles/kml/pushpin/ylw-
pushpin.png</href></Icon><hotSpot x="20" y="2" xunits="pixels"
yunits="pixels"/></IconStyle><LineStyle><color>cc20057b</
color><width>7</width></LineStyle><LabelStyle><color>e3ffffff</
color><scale>0.7</scale></LabelStyle><PolyStyle><color>99ffffff</
color><fill>0</fill></PolyStyle></Style><Style id="sn_ylw-
pushpin_copy0"><IconStyle><color>0a000000</color><scale>0</
scale><Icon><href>http://maps.google.com/mapfiles/kml/pushpin/ylw-
pushpin.png</href></Icon><hotSpot x="20" y="2" xunits="pixels"
yunits="pixels"/></IconStyle><LineStyle><color>cc20057b</color><width>';
$poly_width = '7';
$third_header = '</width></LineStyle><LabelStyle><color>e3ffffff</
color><scale>0.7</scale></LabelStyle><PolyStyle><color>99ffffff</
color><fill>0</fill></PolyStyle></Style><StyleMap id="msn_ylw-
pushpin_copy0"><Pair><key>normal</key><styleUrl>#sn_ylw-
pushpin_copy0</styleUrl></Pair><Pair><key>highlight</
key><styleUrl>#sh_ylw-pushpin_copy0</styleUrl></Pair></
StyleMap><Placemark><name>';
$forth_header = '</name><styleUrl>#msn_ylw-pushpin_copy0</styleUrl>';
$footer = '</Placemark></Document></kml>';
$cstring = "host=localhost port=5432 dbname=postgis user=postgis
password=postgis";
$conn = pg_connect($cstring);
$rows = pg_query("SELECT COUNT(gid) as count from salmon_pops");
$num_rows = pg_fetch_result($rows,'count');
for ($i=1 ; $i <= $num_rows ; $i++) {
$row_names = pg_query("SELECT Population From salmon_pops Where gid=
$i");
$listed_name = pg_fetch_result($row_names,'Population');
$query = pg_query("Copy (Select '$first_header' || '$listed_name' ||
'$second_header' || '$poly_width' || '$third_header' ||
'$listed_name' || '$forth_header' || askml(the_geom) || '$footer'
From salmon_pops Where gid=$i) TO '/Users/Shared/".$i. ".kml';");
}
// Free resultset
pg_free_result($rows);
pg_free_result($row_names);
pg_free_result($query);
// Closing connection
pg_close($conn);
?>
------------------------
PHP SCRIPT TO GENERATE A WEBPAGE TO VIEW RESULTS WITH LINK TO KML FILES
------------------------
<p> <b>Sample Sortable table of Pacific Northwest Salmon Populations</
b></p>
<p>The "sorttable.js" allows dynamic row sorting by clicking a column
title. Output is via php from postgis database</p>
<p>Click on a population name to view kml of population in google
earth</p>
</div>
<div align="center">
<?php
// Connecting, selecting database
$dbconn = pg_connect("host=localhost dbname=postgis user=postgis
password=postgis")
or die('Could not connect: ' . pg_last_error());
// Performing SQL query
$query = 'SELECT link, Status, Region, Ecoregion, State, ESU,
Percent_Natural, Viability, Diversity, Round(Score), Round(Area) FROM
salmon_pops order by Region, ESU';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
// Printing results in HTML
echo pg_query($result);
echo "<table cellpadding='2' class='sortable'>\n<tr><td>Population</td>
<td>Status</td>
<td>Region</td>
<td>State</td>
<td>ESU</td>
<td>Percent Natural</td>
<td>Viability</td>
<td>Diversity</td>
<td>Score</td>
<td>Population Area</td></tr>";
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
pg_free_result($result);
// Closing connection
pg_close($dbconn);
?>
</div>
</body>
</html>
------------------------------------------
On Oct 24, 2007, at 8:06 AM, Kyle Wilcox wrote:
Life is to short to program in Java.
Not the most elegant solution below, but works. Note that column 'id'
must be integer unique (pk or something).
$header = "XML_HEADER_HERE";
$footer = "XML_FOOTER_HERE";
$cstring = "host=YOUR_HOST port=5432 dbname=YOUR_DATABASE user=USER
password=PASSWORD";
$conn = pg_connect($cstring);
$rows = pg_query("SELECT COUNT(*) as count from YOUR_TABLE");
$num_rows = pg_fetch_result($rows,'count');
for ($i=0 ; $i <= $num_rows ; $i++) {
$query = pg_query("Copy (Select '$header' || askml(the_geom) ||
'$footer' From YOUR_TABLE Where id=$i) TO 'DATA_PATH/record_".
$i.".kml'; ");
}
Guido Lemoine wrote:
Dane,
Why not drop PHP and learn Java instead? You can combine JDBC
access to
your data base (simply looping through your resultset, which can have
askml() as
one of the columns) with Java's XML document processing (JDOM) to do
all you want to do.
GL
Dane Blakely Springmeyer wrote:
Hello,
I have been using the PostgreSQL COPY command and the Postgis
AsKml()
function to write out a KML file for a single database record that
matches a WHERE clause, which is very handy.
Here is the generalized SQL query:
Copy (SELECT 'XML HEADER HERE' || askml(the_geom) || 'XML FOOTER
HERE'
from mytable where name = unique record) TO '/recordname.kml';
I would like to do this for every database record, therefore
creating
a single, independent KML file for every geometry row. I think this
undoubtedly requires a bit of scripting to loop through every
database
record and save to a new .kml file by running an individual SQL
query
as many times are there are rows.
I am learning PHP, but at this point turning query results into
arrays
and then passing those back to another query is beyond my skill
level.
Has anyone else used PHP, PL/PGSQL, or other approaches to try
something like this?
Dane
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Kyle Wilcox
NOAA Chesapeake Bay Office
410 Severn Avenue
Suite 107A
Annapolis, MD 21403
office: (410) 295-3151
[EMAIL PROTECTED]
"It is from the wellspring of our despair and the places
that we are broken that we come to repair the world."
- Murray Waas
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
