[postgis-users] Write out KML for each database record?

[Marc Compte]

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
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>       
>
>