Tutorial 4: Adding KML and GeoJSON Export

Abstract

Learn how to use the spatial export capabilities of Pull Reports™ to export your relational data to KML and Geojson for search and viewing in a map.


In this tutorial, we build on the catalog configuration from Tutorial 3 by adding Export Report REST API configuration to enable GeoJSON and KML export results. Spatial export is possible in our report because the student table has a home_geom column which contains the PostGIS geometry point of the student's home.

Enabling GeoJSON and KML 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 an <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.

Finally, 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.

The complete XML Catalog file is now:

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.5.0" id="class" name="Class Reports">
    <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"> 
            <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>

Spatial Export Configuration in the Catalog Configuration Java API

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

package com.pullreports.examples.tutorial;

import java.util.Arrays;
import java.util.List;

import javax.servlet.ServletContext;

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.ColumnConfiguration.ColumnConfigurationBuilder;
import com.pullreports.model.config.PlaceholderDefaultQueryLabelValuesProvider;
import com.pullreports.model.config.PreFilter;
import com.pullreports.model.config.RelationshipConfiguration;
import com.pullreports.model.config.JoinColumnsRelationshipConfiguration.JoinColumnsRelationshipConfigurationBuilder;
import com.pullreports.model.config.JoinTableRelationshipConfiguration.JoinTableRelationshipConfigurationBuilder;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.ReportConfiguration.ReportConfigurationBuilder;
import com.pullreports.model.config.TableConfiguration.TableConfigurationBuilder;
import com.pullreports.model.exportconfig.ExportConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration.ExportConfigurationBuilder;
import com.pullreports.model.exportconfig.GeojsonConfiguration;
import com.pullreports.model.exportconfig.KmlConfiguration;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

public class StudentTutorial4CatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ReportConfiguration studentReportConfiguration = new StudentReportConfigurationBuilder().build();
        
        List<ReportConfiguration> reportConfigurations = Arrays.asList(
            new ReportConfiguration[]{studentReportConfiguration});

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

    private class StudentReportConfigurationBuilder extends ReportConfigurationBuilder {

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

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

        ExportConfiguration exportConfiguration = 
            new ExportConfigurationBuilder().setDefaultColumns(Arrays.asList(
                new ColumnId[]{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 TableConfigurationBuilder {

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

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

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

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

            LASTN_COLUMN_CONFIGURATION = new ColumnConfigurationBuilder(
                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 ColumnConfigurationBuilder(
                new ColumnId("height_in"),"height_inches").setParamType(ParamType.DOUBLE)
                .setDisplayName("Height (in)").build();

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

            ColumnConfiguration bdateYrColumnConfiguration = new ColumnConfigurationBuilder(
                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 ColumnConfigurationBuilder(
                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 ColumnConfigurationBuilder(
                new ColumnId("active"),"active").setDisplayName("Active?")
                .setParamType(ParamType.BOOLEAN).setExport(false).build();

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

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

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,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");
            PreFilter activePreFilter = new PreFilter(ACTIVE_COLUMN_CONFIGURATION.getId()
                ,Operator.EQUALS, Arrays.asList(new Boolean[]{Boolean.TRUE}));
            setPreFilters(Arrays.asList(new PreFilter[]{activePreFilter}));

            RelationshipConfiguration assignmentRelationship = 
                new JoinColumnsRelationshipConfigurationBuilder(new AssignmentTableConfigurationBuilder().build()
                ,Arrays.asList(new JoinColumn[]{new JoinColumn("student_id","id")})).build();

            RelationshipConfiguration parentRelationship = 
                new JoinTableRelationshipConfigurationBuilder(new ParentTableConfigurationBuilder().build()
                ,new JoinTable("class_info.student_parent"
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("student_id","id")})
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("parent_id","id")}))).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                assignmentRelationship,parentRelationship}));
        }
    }

    private final static class AssignmentTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

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

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

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

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,gradeColumnConfiguration,nameColumnConfiguration});
        }

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

            RelationshipConfiguration classRelationship = 
                new JoinColumnsRelationshipConfigurationBuilder(new ClassTableConfigurationBuilder().build()
                ,Arrays.asList(new JoinColumn[]{new JoinColumn("class_id","id")}))
                    .setJoin(Join.INNER).setCardinality(Cardinality.ONE).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                classRelationship}));
        }

    }

    private final static class ClassTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

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

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

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,nameColumnConfiguration});
        }

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

            RelationshipConfiguration studentAssessmentRelationship = 
                new JoinColumnsRelationshipConfigurationBuilder(new StudentAssessmentTableConfigurationBuilder().build()
                ,Arrays.asList(new JoinColumn[]{new JoinColumn("class_id","id")
                    ,new JoinColumn("student_id","id",new TablePath("/student"))})).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                studentAssessmentRelationship}));
        }
    }

    private final static class StudentAssessmentTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

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

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

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

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    ratingColumnConfiguration,assessmentColumnConfiguration
                    ,assessmentDateColumnConfiguration});
        }

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

    private final static class ParentTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

        static {

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

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

            ColumnConfiguration lastnColumnConfiguration = new ColumnConfigurationBuilder(
                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 ColumnConfigurationBuilder(
                new ColumnId("phone"),"phone").setDisplayName("Phone").build();

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    idColumnConfiguration,firstnColumnConfiguration
                    ,lastnColumnConfiguration,phoneColumnConfiguration});
        }

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

            RelationshipConfiguration companyRelationship = 
                new JoinTableRelationshipConfigurationBuilder(new CompanyTableConfigurationBuilder().build()
                ,new JoinTable("class_info.parent_company"
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("parent_id","id")})
                    ,Arrays.asList(new JoinColumn[]{new JoinColumn("company_id","id")}))).build();

            setRelationshipConfigurations(Arrays.asList(new RelationshipConfiguration[]{
                companyRelationship}));
        }
    }

    private final static class CompanyTableConfigurationBuilder extends TableConfigurationBuilder {

        private static final List<ColumnConfiguration> COLUMN_CONFIGURATIONS;

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

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

            COLUMN_CONFIGURATIONS = Arrays.asList(
                new ColumnConfiguration[]{
                    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.