<kml>

Abstract

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


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 the <kml> within a report configuration activates the kml format type 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.

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)
1SneakersPOINT(-105.0906 40.5656)
2Paper towelsPOINT(-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.5.0" id="parcel" name="Parcel Reports">
    <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"> 
            <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 java.util.Arrays;
import java.util.List;

import javax.servlet.ServletContext;

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.ColumnConfiguration.ColumnConfigurationBuilder;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.ReportConfiguration.ReportConfigurationBuilder;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.config.TableConfiguration.TableConfigurationBuilder;
import com.pullreports.model.exportconfig.ExportConfiguration.ExportConfigurationBuilder;
import com.pullreports.model.exportconfig.KmlConfiguration;

public class KmlOutputTransformCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

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

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

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

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
            new ColumnConfiguration[]{
                idColumnConfiguration
                ,descriptionColumnConfiguration
                ,currentGeometryColumnConfiguration});

        TableConfiguration tableConfiguration = new TableConfigurationBuilder(
            new TableId("parcel"),"Parcel",columnConfigurations)
            .setName("parcel").build();

        ReportConfiguration reportConfiguration = new ReportConfigurationBuilder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfigurationBuilder()
                    .setDefaultColumns(Arrays.asList(new ColumnId[]{idColumnConfiguration.getId()}))
                    .setKmlConfiguration(
                        new KmlConfiguration(currentGeometryColumnConfiguration.getId()
                            ,"Parcel: ${@desc}"))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Arrays.asList(
            new ReportConfiguration[]{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.5.0" id="parcel" name="Parcel Reports">
    <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">
            <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 java.util.Arrays;
import java.util.List;
import java.util.Optional;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;

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.ColumnConfiguration.ColumnConfigurationBuilder;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.ReportConfiguration.ReportConfigurationBuilder;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.config.TableConfiguration.TableConfigurationBuilder;
import com.pullreports.model.exportconfig.ExportConfiguration.ExportConfigurationBuilder;
import com.pullreports.model.exportconfig.KmlConfiguration;

public class KmlSubqueryCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

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

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

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

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
            new ColumnConfiguration[]{
                idColumnConfiguration
                ,descriptionColumnConfiguration
                ,currentGeometryColumnConfiguration});

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

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

        ReportConfiguration reportConfiguration = new ReportConfigurationBuilder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfigurationBuilder()
                    .setDefaultColumns(Arrays.asList(new ColumnId[]{idColumnConfiguration.getId()}))
                    .setKmlConfiguration(
                        new KmlConfiguration(currentGeometryColumnConfiguration.getId()
                            ,"Parcel: ${@desc}"))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Arrays.asList(
            new ReportConfiguration[]{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.5.0" id="parcel" name="Parcel Reports">
    <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">
            <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 java.util.Arrays;
import java.util.List;

import javax.servlet.ServletContext;

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.ColumnConfiguration.ColumnConfigurationBuilder;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.ReportConfiguration.ReportConfigurationBuilder;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.config.TableConfiguration.TableConfigurationBuilder;
import com.pullreports.model.exportconfig.ExportConfiguration.ExportConfigurationBuilder;
import com.pullreports.model.exportconfig.KmlConfiguration;

public class KmlViewCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

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

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

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

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
            new ColumnConfiguration[]{
                idColumnConfiguration
                ,descriptionColumnConfiguration
                ,currentGeometryColumnConfiguration});

        TableConfiguration tableConfiguration = new TableConfigurationBuilder(
            new TableId("parcel"),"Parcel",columnConfigurations)
            .setName("parcel_view")
            .build();

        ReportConfiguration reportConfiguration = new ReportConfigurationBuilder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfigurationBuilder()
                    .setDefaultColumns(Arrays.asList(new ColumnId[]{idColumnConfiguration.getId()}))
                    .setKmlConfiguration(
                        new KmlConfiguration(currentGeometryColumnConfiguration.getId()
                            ,"Parcel: ${@desc}"))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Arrays.asList(
            new ReportConfiguration[]{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.