How to export from PostGIS to KML

Question

What is the best way to export from PostGIS to KML using open source tools?

Can I do it directly, or do I need to first export to shape files?

Answer 1

You can use ogr2ogr to perform the operation:

 ogr2ogr -f "KML" mykml.kml PG:"host=myhost user=myloginname \ 
             dbname=mydbname password=mypassword" "myschema.mytable" \
             -s_srs EPSG:<myEPSGcode> -t_srs EPSG:4326

Answer 2

As I mentioned in the comment to the first answer, ogr2ogr will get you a very simplified KML (with a name and description field if you specify them with -dsco, but little else).

So long as you're careful about structures, you ought to be able to write a query that will output what you need, including conditional styles and so forth.

For example, here's an example query that will extract single-style data and save it to a KML (including adding a JSON-like data object to the description tag so that aspatial - 'attribute' - data can be amended by end-users). It's the simplest example - it has no conditional styles and only one variable.

It's a bit of a schemozzle... but it works. Square brackets [] indicate variables - i.e., should be replaced by your own variables, schemas, table names etc.

    COPY (
SELECT xmlelement(
    NAME kml,
    XMLATTRIBUTES ('http://www.opengis.net/kml/2.2' AS xmlns),
    xmlelement(
        NAME "Document", XMLATTRIBUTES ('[yrDocID]' AS id),
        xmlelement(NAME NAME, '[yrKMLName].kml'),
        xmlelement(NAME OPEN, 1),
        xmlelement(
            NAME "Folder",
            xmlelement(NAME NAME, '[yrFolderName]'),
            xmlelement(
                NAME "Schema",
                XMLATTRIBUTES (
                '[yrSchemaName]' AS NAME,
                '[yrSchemaID]' AS id
                ),
                xmlelement(
                    NAME "SimpleField",
                    XMLATTRIBUTES (
                    '[yrFieldType]' AS TYPE,
                    '[yrFieldName]' AS NAME
                    )
                )
            ),
            xmlelement(
                NAME "Style",
                XMLATTRIBUTES ('[yrStyle_01]' AS id),
                xmlelement(
                    NAME "LineStyle",
                    xmlelement(NAME "color", 'ff0000ff'), /* alpha first, then B G R (i.e., RGBA, backwards) */
                    xmlelement(NAME "width", 4)
                ),
                xmlelement(
                    NAME "PolyStyle",
                    xmlelement(NAME "fill", 0)
                )
            ),
            xmlagg(
                xmlelement(
                    NAME "Placemark",
                    xmlelement(NAME NAME, [yrFieldName]),
                    xmlelement(
                        NAME "TimeSpan",
                        xmlelement(NAME "begin", 2009),
                        xmlelement(NAME "end", 2017)
                    ),
                    xmlelement(
                        NAME "description",
                        '{editMe: , editMeToo: , Notes: }'
                    ),
                    xmlelement(NAME "styleUrl", '#[yrStyle_01]'),
                    xmlelement(
                        NAME "ExtendedData",
                        xmlelement(
                            NAME "SchemaData",
                            XMLATTRIBUTES ('#[yrSchemaID]' AS "schemaUrl"),
                            xmlelement(
                                NAME "SimpleData",
                                XMLATTRIBUTES ('[yrFieldName]' AS NAME )
                                , [yrFieldName]
                            )
                        )
                    ),
                    ST_AsKML(ST_Transform(g1, 4326)) :: XML
                )
            )
        )
    )
)
    FROM [schema].[table]
    WHERE [conditions]
)
TO '[outfile].kml'

The best reason to do it this way, is that you can incorporate any part of the KML spec - which you can't do with ogr2ogr.

For example, you could conditionally style each polygon based on its attributes, or add <TimeSpan> behaviour (for Tours: in the example above they're actually implemented in the wrong place in the hierarchy).

It's a bit of a pfaff, though: you either need to generate a set of Document or Folder-level <Style> tags (and possibly <StyleMap> if you have style hierarchies) and apply them conditionally to individual <Placemark> using <StyleUrl>.

Alternatively, put each different 'class' into its own <Folder>: to do this requires

• an extra xml_agg() enclosing the xmlelement(NAME "Folder",[snip];
• GROUP BY some set of things that generates your thematic classes at the end of the query; and
• the name for each new class-based Folder should be created as part of the query.

Then you can have Folder-level styles, one per Folder.

I'm not really a fan of using PostgreSQL to do this (the code example is legacy code). It makes more sense to drag the data (with geometry ST_AsKML()::xml) into Python or PHP and generate the XML iteratively.

If you examine the code at this Google Developer page about using PHP and mySQL to generate KML, you can see the bits that need to be changed to get the data from PostgreSQL instead. That plus the KML Reference should get you over the line.

Answer 3

postgis has this functionality out of the box, if you linked it to proj -- check this using PostGIS_Full_Version -- that addressed, an example of the functionality follows:

SELECT ST_AsKML(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326)) as kml

kml
--------
<Polygon><outerBoundaryIs><LinearRing><coordinates>0,0 0,1 1,1 1,0 0,0</coordinates></LinearRing></outerBoundaryIs></Polygon>