Tutorial 4: Adding KML and GeoJSON Export

In order to enable spatial export from the Export Report REST API, we first need to create two new columns on the report's base, student table to contain the KML and GeoJSON geometries as character strings. Fortunately, PostGIS provides the st_askml and st_asgeojson functions which may be used within <output_transform> to transform the student.home_geom column to KML and GeoJSON geometries respectively.

For both new columns, we will set export="false" to prevent the raw KML or GeoJSON character strings from exporting to non-spatial export formats such as xml or json. Setting the columns to export="false" is only necessary if you feel the raw geometry columns should be excluded from non-spatial export.

Once the KML and GeoJSON columns are added to the base table, activate the Export Report REST API kml and geojson export formats by referencing the new spatial columns within <kml> and <geojson> elements respectively.

Use a <placemark_name_template> element to create a custom placemark for each KML geometry. This custom placemark labels the geometry with a KML viewer such as Google Earth.

Finally, the KML and GeoJSON export formats require the primaryKeyColumns attribute to be specified on the report's base table.

The complete XML Catalog file is now:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="class" name="Class 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="student-information" name="Student Information">
        <export_config defaultColumns="firstn,lastn">
            <kml geometryColumnPath="@home-kml">
                <placemark_name_template>${@firstn} ${@lastn} Home</placemark_name_template>
            </kml>
            <geojson geometryColumnPath="@home-geojson"/>
        </export_config>
        <table id="student" name="class_info.student" displayName="Student" primaryKeyColumns="id">
            <column id="id" name="id" displayName="Student ID" paramType="java.lang.Integer" export="false"/>
            <column id="firstn" name="first_name" displayName="First Name"/>
            <column id="lastn" name="last_name" displayName="Last Name">
                <filter_column_transform>upper(${this})</filter_column_transform>
                <filter_value_transform>upper(?)</filter_value_transform>
                <url_template>
                    http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                </url_template>
                <label_value_list>
                    <label_value_query_default/>
                </label_value_list>
            </column>
            <column id="height_in" name="height_inches" displayName="Height (in)" paramType="java.lang.Double"/>
            <column id="bdate" name="birth_date" displayName="Birth Date" paramType="java.sql.Date">
                <output_transform>to_char(${this},'DD Mon YYYY')</output_transform>
            </column>
            <column id="bdate_yr" name="birth_date" displayName="Birth Date Year" paramType="java.lang.Integer">
                <output_transform>extract(year from ${this})</output_transform>
                <filter_column_transform>extract(year from ${this})</filter_column_transform>
            </column>
            <column id="bdate_month" name="birth_date" displayName="Birth Date Month" paramType="java.lang.Integer">
                <output_transform>extract(month from ${this})</output_transform>
                <filter_column_transform>extract(month from ${this})</filter_column_transform>
                <label_value_list>
                    <label_values>
                        <label_value value="1">January</label_value>
                        <label_value value="2">February</label_value>
                        <label_value value="3">March</label_value>
                        <label_value value="4">April</label_value>
                        <label_value value="6">May</label_value>
                        <label_value value="7">June</label_value>
                        <label_value value="8">July</label_value>
                        <label_value value="9">August</label_value>
                        <label_value value="10">October</label_value>
                        <label_value value="11">November</label_value>
                        <label_value value="12">December</label_value>
                    </label_values>
                </label_value_list>
            </column>
            <column id="active" name="is_active" displayName="Active?" 
                paramType="java.lang.Boolean" export="false"/>
            <column id="home-kml" name="home_geom" displayName="Home KML" export="false">
                <output_transform>st_askml(${this})</output_transform>
            </column>
            <column id="home-geojson" name="home_geom" displayName="Home GeoJSON" export="false">
                <output_transform>st_asgeojson(${this})</output_transform>
            </column>
            <pre_filter>@active=true</pre_filter>
            <relationship>
                <join_column columnName="student_id" referencedColumnName="id" />
                <table id="assignment" displayName="Assignment" name="class_info.assignment">
                    <column id="id" name="id" displayName="Assignment ID" paramType="java.lang.Integer"/>
                    <column id="grade" name="grade" displayName="Grade"/> 
                    <column id="name" name="name" displayName="Name"/> 
                    <relationship join="inner" cardinality="one">
                        <join_column columnName="class_id" referencedColumnName="id" />
                        <table id="class" displayName="Class" name="class_info.class">
                            <column id="id" name="id" displayName="Class ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Class Name"/> 
                            <relationship>
                                <join_column columnName="class_id" referencedColumnName="id"/>
                                <join_column columnName="student_id" referencedColumnName="id" referencedTablePath="/student"/>
                                <table id="student_assessment" displayName="Student Assessment" name="class_info.student_assessment">
                                    <column id="rating" name="rating" displayName="Rating" paramType="java.lang.Integer"/> 
                                    <column id="assessment" name="assessment" displayName="Assessment"/> 
                                    <column id="assessment_date" name="assessment_date" displayName="Assessment Date" paramType="java.sql.Date"/> 
                                </table>
                            </relationship>
                        </table>
                    </relationship>
                </table>
            </relationship>
            <relationship>
                <join_table name="class_info.student_parent">
                    <join_columns>
                        <join_column columnName="student_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="parent_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="parent" displayName="Parent" name="class_info.parent">
                    <column id="id" name="id" displayName="Parent ID" paramType="java.lang.Integer"/>
                    <column id="firstn" name="first_name" displayName="Parent First Name"/> 
                    <column id="lastn" name="last_name" displayName="Parent Last Name"> 
                        <url_template>
                            http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}
                        </url_template>
                    </column>
                    <column id="phone" name="phone" displayName="Phone"/> 
                    <relationship>
                        <join_table name="class_info.parent_company">
                            <join_columns>
                                <join_column columnName="parent_id" referencedColumnName="id" />
                            </join_columns>
                            <inverse_join_columns>
                                <join_column columnName="company_id" referencedColumnName="id" />
                           </inverse_join_columns>
                        </join_table>
                        <table id="company" displayName="Company" name="class_info.company">
                            <column id="id" name="id" displayName="Company ID" 
                                 paramType="java.lang.Integer" export="false"/>
                            <column id="name" name="name" displayName="Company"/> 
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Here is a Catalog Configuration Java API CatalogConfigurationFactory which is identical to the final XML Catalog file.

package com.pullreports.examples.tutorial;

import com.pullreports.export.request.FilterValue;
import com.pullreports.export.request.Operator;
import com.pullreports.model.Cardinality;
import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.Join;
import com.pullreports.model.JoinColumn;
import com.pullreports.model.JoinTable;
import com.pullreports.model.LabelValue;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.TableId;
import com.pullreports.model.TablePath;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.JoinColumnsRelationshipConfiguration;
import com.pullreports.model.config.JoinTableRelationshipConfiguration;
import com.pullreports.model.config.PlaceholderDefaultQueryLabelValuesProvider;
import com.pullreports.model.config.PreFilter;
import com.pullreports.model.config.RelationshipConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration;
import com.pullreports.model.exportconfig.GeojsonConfiguration;
import com.pullreports.model.exportconfig.KmlConfiguration;

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

public class StudentTutorial4CatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ReportConfiguration studentReportConfiguration = new StudentReportConfigurationBuilder().build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(studentReportConfiguration);

        return new CatalogConfiguration(new CatalogId("class"),"Class Reports",reportConfigurations);
    }

    private static final class StudentReportConfigurationBuilder extends ReportConfiguration.Builder {

        final KmlConfiguration kmlConfiguration =
            new KmlConfiguration(StudentTableConfigurationBuilder.KML_COLUMN_CONFIGURATION.getId()
                ,"${@firstn} ${@lastn} Home");

        final GeojsonConfiguration geojsonConfiguration =
            new GeojsonConfiguration(StudentTableConfigurationBuilder.GEOJSON_COLUMN_CONFIGURATION.getId());

        final ExportConfiguration exportConfiguration =
            new ExportConfiguration.Builder().setDefaultColumns(Arrays.asList(
                StudentTableConfigurationBuilder.FIRSTN_COLUMN_CONFIGURATION.getId()
                    ,StudentTableConfigurationBuilder.LASTN_COLUMN_CONFIGURATION.getId()))
            .setKmlConfiguration(kmlConfiguration)
            .setGeojsonConfiguration(geojsonConfiguration)
        .build();

        StudentReportConfigurationBuilder(){
            super(new ReportId("student-information"),"Student Information"
                ,new StudentTableConfigurationBuilder().build());
            setExportConfiguration(exportConfiguration).build();
        }
    }

    private final static class StudentTableConfigurationBuilder extends TableConfiguration.Builder {

        static final ColumnConfiguration ID_COLUMN_CONFIGURATION,FIRSTN_COLUMN_CONFIGURATION
            ,LASTN_COLUMN_CONFIGURATION,ACTIVE_COLUMN_CONFIGURATION
            ,KML_COLUMN_CONFIGURATION,GEOJSON_COLUMN_CONFIGURATION;

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ID_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
                new ColumnId("id"),"id").setDisplayName("Student ID")
                .setParamType(ParamType.INTEGER).setExport(false).build();

            FIRSTN_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
                new ColumnId("firstn"),"first_name").setDisplayName("First Name").build();

            LASTN_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
                new ColumnId("lastn"),"last_name").setDisplayName("Last Name")
                .setFilterColumnTransform("upper(${this})")
                .setFilterValueTransform("upper(?)")
                .setUrlTemplate("http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}")
                .setLabelValuesProvider(new PlaceholderDefaultQueryLabelValuesProvider()).build();

            ColumnConfiguration heightInColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("height_in"),"height_inches").setParamType(ParamType.DOUBLE)
                .setDisplayName("Height (in)").build();

            ColumnConfiguration bdateColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("bdate"),"birth_date").setParamType(ParamType.DATE)
                .setOutputTransform("to_char(${this},'DD Mon YYYY')")
                .setDisplayName("Birth Date").build();

            ColumnConfiguration bdateYrColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("bdate_yr"),"birth_date").setParamType(ParamType.INTEGER)
                .setOutputTransform("extract(year from ${this})")
                .setFilterColumnTransform("extract(year from ${this})")
                .setDisplayName("Birth Date Year").build();

            ColumnConfiguration bdateMonthColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("bdate_month"),"birth_date").setParamType(ParamType.INTEGER)
                .setOutputTransform("extract(month from ${this})")
                .setFilterColumnTransform("extract(month from ${this})")
                .setDisplayName("Birth Date Month")
                .setLabelValuesProvider((DataSource ds, HttpServletRequest hsr) -> {
                    LabelValue[] labelValues = new LabelValue[]{
                        new LabelValue("1","January")
                        ,new LabelValue("2","February")
                        ,new LabelValue("3","March")
                        ,new LabelValue("4","April")
                        ,new LabelValue("5","May")
                        ,new LabelValue("6","June")
                        ,new LabelValue("7","July")
                        ,new LabelValue("8","August")
                        ,new LabelValue("9","September")
                        ,new LabelValue("10","October")
                        ,new LabelValue("11","November")
                        ,new LabelValue("12","December")
                    };
                    return Arrays.asList(labelValues);
            }).build();
            
            ACTIVE_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
                new ColumnId("active"),"active").setDisplayName("Active?")
                .setParamType(ParamType.BOOLEAN).setExport(false).build();

            KML_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
                new ColumnId("home-kml"),"home_geom").setDisplayName("Home KML")
                .setExport(false).setOutputTransform("st_askml(${this})").build();

            GEOJSON_COLUMN_CONFIGURATION = new ColumnConfiguration.Builder(
                new ColumnId("home-geojson"),"home_geom").setDisplayName("Home GeoJSON")
                .setExport(false).setOutputTransform("st_asgeojson(${this})").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                    ID_COLUMN_CONFIGURATION,FIRSTN_COLUMN_CONFIGURATION,LASTN_COLUMN_CONFIGURATION
                    ,heightInColumnConfiguration ,bdateColumnConfiguration,bdateYrColumnConfiguration
                    ,bdateMonthColumnConfiguration,ACTIVE_COLUMN_CONFIGURATION
                    ,KML_COLUMN_CONFIGURATION,GEOJSON_COLUMN_CONFIGURATION);
        }

        StudentTableConfigurationBuilder(){
            super(new TableId("student"),"Student",COLUMN_CONFIGURATIONS);
            setName("class_info.student");
            setPrimaryKeyColumnIds(Collections.singleton(ID_COLUMN_CONFIGURATION.getId()));
            PreFilter activePreFilter = new PreFilter(ACTIVE_COLUMN_CONFIGURATION.getId()
                ,Operator.EQUALS, FilterValue.asList(Boolean.TRUE));
            setPreFilters(Collections.singletonList(activePreFilter));

            RelationshipConfiguration assignmentRelationship = 
                new JoinColumnsRelationshipConfiguration.Builder(new AssignmentTableConfigurationBuilder().build()
                ,Collections.singletonList(new JoinColumn("student_id","id"))).build();

            RelationshipConfiguration parentRelationship = 
                new JoinTableRelationshipConfiguration.Builder(new ParentTableConfigurationBuilder().build()
                ,new JoinTable("class_info.student_parent"
                    ,Collections.singletonList(new JoinColumn("student_id","id"))
                    ,Collections.singletonList(new JoinColumn("parent_id","id")))).build();

            setRelationshipConfigurations(Arrays.asList(
                assignmentRelationship,parentRelationship));
        }
    }

    private final static class AssignmentTableConfigurationBuilder extends TableConfiguration.Builder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("id"),"id").setDisplayName("Assignment ID")
                .setParamType(ParamType.INTEGER).build();

            ColumnConfiguration gradeColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("grade"),"grade").setDisplayName("Grade").build();

            ColumnConfiguration nameColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("name"),"name").setDisplayName("Name").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                    idColumnConfiguration,gradeColumnConfiguration,nameColumnConfiguration);
        }

        AssignmentTableConfigurationBuilder(){
            super(new TableId("assignment"),"Assignment",COLUMN_CONFIGURATIONS);
            setName("class_info.assignment");

            RelationshipConfiguration classRelationship = 
                new JoinColumnsRelationshipConfiguration.Builder(new ClassTableConfigurationBuilder().build()
                ,Collections.singletonList(new JoinColumn("class_id","id")))
                    .setJoin(Join.INNER).setCardinality(Cardinality.ONE).build();

            setRelationshipConfigurations(Collections.singletonList(classRelationship));
        }

    }

    private final static class ClassTableConfigurationBuilder extends TableConfiguration.Builder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("id"),"id").setDisplayName("Class ID")
                .setParamType(ParamType.INTEGER).setExport(false).build();

            ColumnConfiguration nameColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("name"),"name").setDisplayName("Class Name").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(idColumnConfiguration,nameColumnConfiguration);
        }

        ClassTableConfigurationBuilder(){
            super(new TableId("class"),"Class",COLUMN_CONFIGURATIONS);
            setName("class_info.class");

            RelationshipConfiguration studentAssessmentRelationship = 
                new JoinColumnsRelationshipConfiguration.Builder(new StudentAssessmentTableConfigurationBuilder().build()
                ,Arrays.asList(new JoinColumn("class_id","id")
                    ,new JoinColumn("student_id","id",new TablePath("/student")))).build();

            setRelationshipConfigurations(Collections.singletonList(studentAssessmentRelationship));
        }
    }

    private final static class StudentAssessmentTableConfigurationBuilder extends TableConfiguration.Builder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration ratingColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("rating"),"rating").setDisplayName("Rating")
                .setParamType(ParamType.INTEGER).build();

            ColumnConfiguration assessmentColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("assessment"),"assessment").setDisplayName("Assessment").build();

            ColumnConfiguration assessmentDateColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("assessment_date"),"assessment_date").setParamType(ParamType.DATE)
                .setDisplayName("Assessment Date").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                    ratingColumnConfiguration,assessmentColumnConfiguration
                    ,assessmentDateColumnConfiguration);
        }

        StudentAssessmentTableConfigurationBuilder(){
            super(new TableId("student_assessment"),"Student Assessment",COLUMN_CONFIGURATIONS);
            setName("class_info.student_assessment");
        }
    }

    private final static class ParentTableConfigurationBuilder extends TableConfiguration.Builder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {

            ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("id"),"id").setDisplayName("Parent ID")
                .setParamType(ParamType.INTEGER).build();

            ColumnConfiguration firstnColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("firstn"),"first_name").setDisplayName("First Name").build();

            ColumnConfiguration lastnColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("lastn"),"last_name").setDisplayName("Last Name")
                .setUrlTemplate("http://www.pleasantville-schools.org/contacts/search?q=first_name%3D${@@firstn}+and+last_name%3D${@@lastn}").build();

            ColumnConfiguration phoneColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("phone"),"phone").setDisplayName("Phone").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                    idColumnConfiguration,firstnColumnConfiguration
                    ,lastnColumnConfiguration,phoneColumnConfiguration);
        }

        ParentTableConfigurationBuilder(){
            super(new TableId("parent"),"Parent",COLUMN_CONFIGURATIONS);
            setName("class_info.parent");

            RelationshipConfiguration companyRelationship = 
                new JoinTableRelationshipConfiguration.Builder(new CompanyTableConfigurationBuilder().build()
                ,new JoinTable("class_info.parent_company"
                    ,Collections.singletonList(new JoinColumn("parent_id","id"))
                    ,Collections.singletonList(new JoinColumn("company_id","id")))).build();

            setRelationshipConfigurations(Collections.singletonList(companyRelationship));
        }
    }

    private final static class CompanyTableConfigurationBuilder extends TableConfiguration.Builder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {
            ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("id"),"id").setDisplayName("Company ID")
                .setParamType(ParamType.INTEGER).setExport(false).build();

            ColumnConfiguration nameColumnConfiguration = new ColumnConfiguration.Builder(
                new ColumnId("name"),"name").setDisplayName("Company").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(idColumnConfiguration,nameColumnConfiguration);
        }

        CompanyTableConfigurationBuilder(){
            super(new TableId("company"),"Company",COLUMN_CONFIGURATIONS);
            setName("class_info.company");
        }
    }
}

This concludes the fourth tutorial regarding spatial export configuration. Continue to Tutorial 5 to learn about defining additional <report>s and managing complex Pull Reports™ XML Catalog files.