6

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?

5

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
  • One shortcoming of ogr2ogr in this instance is that you can't generate attribute-dependent styles or a load of other stuff (like <TimeStamp> and <TimeSpan> tags for 'tour' creation). Far better to bite the bullet and hand-craft (1) the query to get attributes and geometry in almost-XML (an xml_agg() of a bunch of nested xmlelement()s and an ST_AsKML(geom)::xml); then (2) wrap (1) in the XML tags required to document namespaces, styles, schema etc. You only ever have to get it right once: then template that shizzle, brah. – GT. Apr 24 '17 at 7:10
  • ogr2ogr was not designed in the first place to accomplish what you referring as a shortcoming. Templating a kml and then filling in the values later on as you describe, yes its an interesting functional approach. Another way would be if you'd be really familiar with the kml schema, to directly draw the locations from a database, and go lower with programming library (ie pyKML) to develop more general solutions. – nickves Apr 25 '17 at 0:20
4

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.

2

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.