Learn to configure the <geojson>
element for Pull Reports™ Ad Hoc
report and data service software.
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.
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.
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.
parcel
tableid (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.
<output_transform>
<?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>
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);
}
}
<subquery>
<?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>
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); } }
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.
<?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>
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);
}
}
<export_config> |
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
.