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?
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this communityYou 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
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.
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
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 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.
<?xml version="1.0" encoding="utf-8"?>
at the really begining of the KML?
Mar 8, 2021 at 11:35
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>