<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 <geojson> within a report configuration and specifying the base table's primaryKeyColumns attribute 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.

Pull Reports™ Standard Edition Configuration

The <geojson> element configures the geojson and map export report formats. These formats are 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 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.7.0" id="parcel" name="Parcel Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <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" 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-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 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.GeojsonConfiguration;

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

public class GeojsonOutputTransformCatalogConfigurationFactory 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-geojson"),"geom")
            .setDisplayName("GeoJSON")
            .setOutputTransform("ST_AsGeoJSON(${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()))
                    .setGeojsonConfiguration(new GeojsonConfiguration(currentGeometryColumnConfiguration.getId()))
                .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.7.0" id="parcel" name="Parcel Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <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 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.GeojsonConfiguration;

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

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

        ReportConfiguration reportConfiguration = new ReportConfiguration.Builder(
            new ReportId("parcel-information"),"Parcel Information",tableConfiguration)
            .setExportConfiguration(
                new ExportConfiguration.Builder()
                    .setDefaultColumns(Collections.singletonList(idColumnConfiguration.getId()))
                    .setGeojsonConfiguration(new GeojsonConfiguration(currentGeometryColumnConfiguration.getId()))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(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.7.0" id="parcel" name="Parcel Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.7.0
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.7.0.xsd">
    <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" 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-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 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.GeojsonConfiguration;

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

public class GeojsonViewCatalogConfigurationFactory 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-geojson"),"geojson")
            .setDisplayName("GeoJSON")
            .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()))
                    .setGeojsonConfiguration(new GeojsonConfiguration(currentGeometryColumnConfiguration.getId()))
                .build()).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(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.

Prev		Next
<filter_value_transform>	Home	<global_label_value_list>

<geojson>

Pull Reports™ Standard Edition Configuration

Usage

Using an <output_transform>

Using an <subquery>

Using a database view

Parents

Attributes

geometryColumnPath

Using an `<output_transform>`

Using an `<subquery>`