<geojson>

Abstract

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


Table of Contents

Catalog Configuration Java API reference:  GeojsonConfiguration

The <geojson> element identifies a <column> within the report's base table which contains a GeoJSON geometry object as a character string. Including the <geojson> within a report configuration activates the geojson and map format types within the Export Report REST API. If non-null, the geometry value within the referenced column will be used as the row's geometry property in the GeoJSON response.

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 geojson and map Export Report REST API format types, transform the current_location geometry column into a GeoJSON geometry object. One way to accomplish this is via the SQL/MM 3 ST_AsGeoJSON function which returns the native geometry type as a text GeoJSON geometry. This function is supported within SQL/MM 3 compliant databases.

Use one of the following transformation strategies to accomplish the transformation.

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'>
            <geojson geometryColumnPath="@parcel-geometry-geojson"/>
       </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-geojson" name="geom" displayName="GeoJSON" >
                <output_transform>ST_AsGeoJSON(${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.geojson;

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.GeojsonConfiguration;

public class GeojsonOutputTransformCatalogConfigurationFactory 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-geojson"),"geom")
            .setDisplayName("GeoJSON")
            .setOutputTransform("ST_AsGeoJSON(${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()}))
                    .setGeojsonConfiguration(new GeojsonConfiguration(currentGeometryColumnConfiguration.getId()))
                .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'>
            <geojson geometryColumnPath="@parcel-geometry-geojson"/>
       </export_config>
        <table id="parcel" displayName="Parcel">
            <subquery>select id,description,ST_AsGeoJSON(geom) as geojson 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-geojson" name="geojson" displayName="GeoJSON"/>
        </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.geojson;

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.GeojsonConfiguration;

public class GeojsonSubqueryCatalogConfigurationFactory 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-geojson"),"geojson")
            .setDisplayName("GeoJSON")
            .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_AsGeoJSON(geom) as geojson from parcel");
                    }
                };
        }).build();

        ReportConfiguration reportConfiguration = new ReportConfigurationBuilder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfigurationBuilder()
                    .setDefaultColumns(Arrays.asList(new ColumnId[]{idColumnConfiguration.getId()}))
                    .setGeojsonConfiguration(new GeojsonConfiguration(currentGeometryColumnConfiguration.getId()))
                .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_AsGeoJSON transformation may also occur in a database view like so:

CREATE VIEW parcel_view as select id,description,ST_AsGeoJSON(geom) as geojson 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'>
            <geojson geometryColumnPath="@parcel-geometry-geojson"/>
       </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-geojson" name="geojson" displayName="GeoJSON"/>
        </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.geojson;

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.GeojsonConfiguration;

public class GeojsonViewCatalogConfigurationFactory 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-geojson"),"geojson")
            .setDisplayName("GeoJSON")
            .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()}))
                    .setGeojsonConfiguration(new GeojsonConfiguration(currentGeometryColumnConfiguration.getId()))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Arrays.asList(
            new ReportConfiguration[]{reportConfiguration});

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

Parents

<export_config>

Attributes

geometryColumnPath

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