Dane,
I know it is already working fine for you but be aware of how that
script works, it assumes the contents of the Id field are consecutive
numbers and they always start from 1. If records were once deleted there
may be some IDs missing in between, thus the script might not work as
expected. Imagine your Ids start all at 1000 (1000, 1001, 1002) ... in
that extreme escenario the COUNT will tell you there's 3 records and
your script will loop 3 times looking for the Ids 1, 2 and 3 and since
those don't exist it won't do anything at all. Changing the "for ($i=0
..." by "for ($i=1000..." will do the trick in this case, but that
coding is totally dependant on the data, thus making it hard to reuse it
in other contexts.
That was a worse-case scenario, but even if that's not your case there's
a huge variety of cases where your approach may give you some problems.
For instance, if you allow the deletion of some records you might end up
with something like a table with Ids 1,2,3,5,6,7,8,9,10 ... there's 9
records and Id 4 is missing because it was once deleted, thus the script
will stop at Id 9 and will not create the KML for record with Id 10.
With a small modification you can make the script work with any table,
any Ids:
...
...
$conn= pg_connect($cstring);
$sql="SELECT Id FROM your_table";
$rs=pg_exec($sql);
while ($row=pg_fetch_array($rs)) {
$query = pg_query("Copy (Select '$header' || askml(the_geom) ||
'$footer' From YOUR_TABLE Where id=".$row["Id"].") TO
'DATA_PATH/record_".$row["Id"].".kml'; ");
}
I hope that's of some help.
Marc
En/na Kyle Wilcox ha escrit:
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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
