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

[Kyle Wilcox]

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
>>
> 

-- 

 Kyle Wilcox
 NOAA Chesapeake Bay Office
 410 Severn Avenue
 Suite 107A
 Annapolis, MD 21403
 office: (410) 295-3151
 Kyle.Wilcox at noaa.gov

 "It is from the wellspring of our despair and the places
  that we are broken that we come to repair the world."
						- Murray Waas