<kml>

Abstract

Learn to configure the <kml> element for Pull Reports™ Ad Hoc report and data service software.

Table of Contents

Catalog Configuration Java API reference: KmlConfiguration

The <kml> element identifies a <column> within the report's base table which contains a KML Geometry object as a character string. Including <kml> within a report configuration and specifying the base table's primaryKeyColumns attribute activates the kmlformat within the Export Report REST API. If non-null, the geometry value within the referenced column will be used as the row's Geometry object in the KML response.

Pull Reports™ permits the customization of the KML Placemark <name> element via <placemark_name_template>. The KML Placemark <name> element labels the geometry feature in a KML viewer. See the <placemark_name_template> documentation for how to customize the <name> with values from the report's base table.

Pull Reports™ Standard Edition Configuration

The <kml> element configures the kml export report format. This format is available to Standard Edition deployments or Community Edition deployments in evaluation mode.

Read more in the Administration Guide, License documentation.

Usage

In this example, each row of a the parcel table contains information about one shipping parcel with columns for the unique parcel ID, a description of the parcel contents, and the parcel's current location as a Geometry point. This last column is understood to be of the native Geometry datatype of the database. For instance, if the database were PostgreSQL, the native Geometry datatype would be the PostGIS geometry type.

The following table represents the parcel table with two rows of data. For readability, the values of the current_location column are described in Well Known Text.

Table 1. parcel table

id (int8)	description (text)	current_location (geom)
1	Sneakers	`POINT(-105.0906 40.5656)`
2	Paper towels	`POINT(-101.303 33.1691)`

To create a Pull Report which supports the kml Export Report REST API format type, transform the current_location geometry column into a KML geometry object. One way to accomplish this is via the SQL/MM 3 ST_AsKML function which returns the native geometry type as a text KML geometry. This function is supported within SQL/MM 3 compliant databases.

Use one of the following transformation strategies to accomplish the transformation. Each example below uses the parcel table's description column to uniquely label each exported KML geometry via a <placemark_name_template>.

Using an `<output_transform>`

Example 1. Via an XML Catalog file

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="parcel" name="Parcel Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id'>
            <kml geometryColumnPath="@parcel-geometry-kml">
                <placemark_name_template>Parcel: ${@desc}</placemark_name_template>
            </kml>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel" primaryKeyColumns="id">
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Long"/>
            <column id="desc" name="description" displayName="Parcel Description"/>
            <column id="parcel-geometry-kml" name="kml" displayName="KML" >
                <output_transform>ST_AsKML(${this})</output_transform>
            </column>
        </table>
    </report>
</catalog>

Example 2. Via the Catalog Configuration Java API

The following example is an identical Catalog Configuration Java API configuration.

package com.pullreports.examples.kml;

import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.TableId;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration;
import com.pullreports.model.exportconfig.KmlConfiguration;

import javax.servlet.ServletContext;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

public class KmlOutputTransformCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("id"),"Parcel ID")
            .setParamType(ParamType.LONG)
            .build();

        ColumnConfiguration descriptionColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("desc"),"description")
            .setDisplayName("Parcel Description")
            .build();

        ColumnConfiguration currentGeometryColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("parcel-geometry-kml"),"geom")
            .setDisplayName("KML")
            .setOutputTransform("ST_AsKML(${this})")
            .build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                idColumnConfiguration
                ,descriptionColumnConfiguration
                ,currentGeometryColumnConfiguration);

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("parcel"),"Parcel",columnConfigurations).
                setName("parcel").
                setPrimaryKeyColumnIds(Collections.singleton(idColumnConfiguration.getId())).build();

        ReportConfiguration reportConfiguration = new ReportConfiguration.Builder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfiguration.Builder()
                    .setDefaultColumns(Collections.singletonList(idColumnConfiguration.getId()))
                    .setKmlConfiguration(
                        new KmlConfiguration(currentGeometryColumnConfiguration.getId()
                            ,"Parcel: ${@desc}"))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(reportConfiguration);

        return new CatalogConfiguration(new CatalogId("parcel"),"Parcel Reports",reportConfigurations);

    }
}

Using an `<subquery>`

Example 3. Via an XML Catalog file

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="parcel" name="Parcel Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id'>
            <kml geometryColumnPath="@parcel-geometry-kml">
                <placemark_name_template>Parcel: ${@desc}</placemark_name_template>
            </kml>
       </export_config>
        <table id="parcel" displayName="Parcel" primaryKeyColumns="id">
            <subquery>select id,description,ST_AsKML(geom) as kml from parcel</subquery>
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Long"/>
            <column id="desc" name="description" displayName="Parcel Description"/>
            <column id="parcel-geometry-kml" name="kml" displayName="KML"/>
        </table>
    </report>
</catalog>

Example 4. Via the Catalog Configuration Java API

The following example is an identical Catalog Configuration Java API configuration.

package com.pullreports.examples.kml;

import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.Subquery;
import com.pullreports.model.TableId;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration;
import com.pullreports.model.exportconfig.KmlConfiguration;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

public class KmlSubqueryCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("id"),"Parcel ID")
            .setParamType(ParamType.LONG)
            .build();

        ColumnConfiguration descriptionColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("desc"),"description")
            .setDisplayName("Parcel Description")
            .build();

        ColumnConfiguration currentGeometryColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("parcel-geometry-kml"),"kml")
            .setDisplayName("KML")
            .build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                idColumnConfiguration
                ,descriptionColumnConfiguration
                ,currentGeometryColumnConfiguration);

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("parcel"),"Parcel",columnConfigurations)
            .setSubqueryProvider((HttpServletRequest request) ->
                new Subquery(){

                    @Override
                    public Optional<String> getValue() {
                        return Optional.of("select id,description,ST_AsKML(geom) as kml from parcel");
                    }
                }
        ).setPrimaryKeyColumnIds(Collections.singleton(idColumnConfiguration.getId())).build();

        ReportConfiguration reportConfiguration = new ReportConfiguration.Builder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfiguration.Builder()
                    .setDefaultColumns(Collections.singletonList(idColumnConfiguration.getId()))
                    .setKmlConfiguration(
                        new KmlConfiguration(currentGeometryColumnConfiguration.getId()
                            ,"Parcel: ${@desc}"))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(reportConfiguration);

        return new CatalogConfiguration(new CatalogId("parcel"),"Parcel Reports",reportConfigurations);
    }
}

Using a database view

The ST_AsKML transformation may also occur in a database view like so:

create view parcel_view as select id,description,ST_AsKML(geom) as kml from parcel;

Then reference the view within the <table> name attribute.

Example 5. Via an XML Catalog file

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="parcel" name="Parcel Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="parcel-information" name="Parcel Information">
        <export_config defaultColumns='id'>
            <kml geometryColumnPath="@parcel-geometry-kml">
                <placemark_name_template>Parcel: ${@desc}</placemark_name_template>
            </kml>
       </export_config>
        <table id="parcel" displayName="Parcel" name="parcel_view" primaryKeyColumns="id">
            <column id="id" name="id" displayName="Parcel ID" paramType="java.lang.Long"/>
            <column id="desc" name="description" displayName="Parcel Description"/>
            <column id="parcel-geometry-kml" name="kml" displayName="KML"/>
        </table>
    </report>
</catalog>

Example 6. Via the Catalog Configuration Java API

The following example is an identical Catalog Configuration Java API configuration.

package com.pullreports.examples.kml;

import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.TableId;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration;
import com.pullreports.model.exportconfig.KmlConfiguration;

import javax.servlet.ServletContext;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

public class KmlViewCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("id"),"Parcel ID")
            .setParamType(ParamType.LONG)
            .build();

        ColumnConfiguration descriptionColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("desc"),"description")
            .setDisplayName("Parcel Description")
            .build();

        ColumnConfiguration currentGeometryColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("parcel-geometry-kml"),"kml")
            .setDisplayName("KML")
            .build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                idColumnConfiguration
                ,descriptionColumnConfiguration
                ,currentGeometryColumnConfiguration);

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("parcel"),"Parcel",columnConfigurations).
                setName("parcel_view").
                setPrimaryKeyColumnIds(Collections.singleton(idColumnConfiguration.getId())).build();

        ReportConfiguration reportConfiguration = new ReportConfiguration.Builder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfiguration.Builder()
                    .setDefaultColumns(Collections.singletonList(idColumnConfiguration.getId()))
                    .setKmlConfiguration(
                        new KmlConfiguration(currentGeometryColumnConfiguration.getId()
                            ,"Parcel: ${@desc}"))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(reportConfiguration);

        return new CatalogConfiguration(new CatalogId("parcel"),"Parcel Reports",reportConfigurations);
    }
}

Children

<placemark_name_template>?

Parents

<export_config>

Attributes

geometryColumnPath

The column resource path to the column in the report's base table which contains the row's KML Geometry as a java.lang.String.

Prev		Next
<join_table>	Home	<label_value>

<kml>

Pull Reports™ Standard Edition Configuration

Usage

Using an <output_transform>

Using an <subquery>

Using a database view

Children

Parents

Attributes

geometryColumnPath

Using an `<output_transform>`

Using an `<subquery>`