[postgis-users] AsKML() how to create kml from postgis data

[Dylan Beaudette]

On Friday 14 September 2007, John Smith wrote:
> as_kml()?! there's another askml() in line with asgml(). why this
> alias/name change/confusion?
>
> anyway how does it really work? getting a "Must contain
> 'geometry_column from table_name' or 'geom from (subselect) as foo'
> (couldnt find ' from ')". what do i do after passing bbox=... and
> type=kml to export script? the sql doesn't go inside .map, right? can
> some share a detailed use/script? not much in
> http://postgis.refractions.net/docs/postgis.pdf

I'll add a bit to this discussion, as I have tackled it before.

> btw super example at
> http://casoilresource.lawr.ucdavis.edu/ka-map/htdocs/?map=casoil&cps=-20125
>07.9410234897,-132997.150403761,500000&layers=__base__ 

Thanks! Here is a shorter link to the corresponding page:
http://casoilresource.lawr.ucdavis.edu/drupal/node/429

>although i didn't 
> quite get this part:
> 1. select geometry overlapping the bounding box defined by the current
> viewport in AEA coordinates, using the PostGIS spatial indexing system
> 2. compute the intersection between the boinding box and the
> overlapping polygons
> 3. compute the centroids of the new geometry
> 4. convert the polygon geometry to KML
> 5. convert the centroid geometry to KML
> 6. return resulting text

This is a list of how the PHP code is functioning; some background--

I have a massive table of polygons, stored in a custom AEA projection. Google 
Earth is not happy about massive KML files, so PostGIS is used to 'cut-out' 
(intersection) a chunk. This chunk is then converted into KML, with all of 
the extra XML styling etc. added by a PHP wrapper script. A second pass is 
done with PostGIS to get the centroids of the polygons, which are used as a 
labeling mechanism. All of the KML / XML chunks are stuck into a dynamically 
generated KML document and returned to the user. 

I was able to construct this by
1. looking over the KML specs
2. looking at some example KML files
3. experimentation

Further work should definitely use something other than string functions to 
build the XML.

Another hint: be sure to send the KML header by PHP first:

		header("Pragma: public"); // required
		header("Expires: 0");       
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Content-Type: application/kml; charset=utf8");
		header('Content-Disposition: attachment; filename="file.kml"') ;


check out the examples on the above link, and dissect the resulting KML file 
for hints.

Cheers,

Dylan





> cheers, jzs
>
> On 9/14/07, Jose Gomez-Dans <jgomezdans at gmail.com> wrote:
> > Hi Maciej,
> > I sent you an e-mail, but your spam protection system wouldn't allow
> > the message to go through.  If you have another address, I'll resend
> > it to you :)
> >
> > On 9/14/07, Maciej Skorczewski <maciej.skorczewski at procad.pl> wrote:
> > > how sql should looks like?
> >
> > Simply (and this is already quite advanced: you can use simplify() to
> > reduce the number of points in your vector data. Ours are very
> > detailed, and we don't want that on our KML files):
> >
> > SELECT AS_KML(SIMPLIFY(the_geom,100)) from MY_TABLE;
> >
> > > Question about Python class...i am not python programer can you
> > > describe my how i can use it?
> >
> > You just write a small python program to use it. You should be able to
> > do the same with PHP.
> >
> > Cheers,
> > José
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users



-- 
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341