Building a Table Joining (Like) Service with Web Processing Services

Ian Turton, Astun Technology Ltd.

[email protected]

[email protected]

@ijturton

Table Joining Service

In 2010 the Open Geospatial Consortium (OGC) proposed a new standard called the Table Joining Service

The Table Joining Service enables the publication of tabular information about geographic features, and the joining of these tables to their geographic features so that the information can be mapped or processed in a geographic information system.

What?

Why?

Allows the publication of a single geographic base map/dataset and then publication of many different datasets for that geography.

• Census Bureaux
• National Statistics Offices
• National Governments
• Local Governments
• Any other data provider

But

8 years later no one (to my knowledge) has implemented one.

Why?

• Complexity
  • The TJS standard is 77 pages long.
• Overhead
  • Requires the implementation of a new format (GDAS)
  • Must implement 6 or 7 new endpoints

So basically a completely new service and format to carry out a relatively simple task.

Or, Could we find a simpler way?

Web Processing Service (WPS) - another OGC standard for carrying out arbitrary operations over the web.

So if we:

• develop a process to join data to another (spatial) dataset.
• deploy with an existing WPS
• ?

A Joining Process

• GeoTools already have a class to handle 'joins' in WFS 2.0, so leverage that.
  • allows a filter to be used to describe the join c.f. SQL where statement
  • allows use of an alias for datasources
  • BUT assumes that both sides of the join are in the same database.

But

• We want to use arbitrary data sources on both sides of the join.
• We want to import CSV files.
• Also GeoServer doesn't currently handle CSV as a WPS input.

Leverage CSVDataStore

• Currently languishing in the GeoTools unsupported modules, mainly used for training.
• GeoServer WPS uses a set of Process Parameter Input Output (PPIO) to map between the representation and a POJO memory representation.
• So develop a CSVPPIO class to read in and write out CSV data.

@Override
 public Object decode(InputStream input) throws Exception {
     // this will be deleted for us when the process finishes
     Resource tmp = resourceManager.getTemporaryResource(".csv");

     IOUtils.copy(input, tmp.out());
     HashMap<String, Object> params = new HashMap<>();
     params.put(CSVDataStoreFactory.FILE_PARAM.key,
       tmp.file().getAbsoluteFile());
     params.put(CSVDataStoreFactory.STRATEGYP.key,
       "CSVAttributesOnlyStrategy");
     CSVDataStore store = (CSVDataStore)
        DataStoreFinder.getDataStore(params);
     SimpleFeatureCollection collection = store.getFeatureSource()
        .getFeatures();
     LOGGER.info("read in "+collection.size()+
        " features from CSV source");
     store.dispose();
     return collection;
 }

@Override
public void encode(Object value, OutputStream os)
   throws Exception {
    // will be deleted when the process finishes
    Resource tmp = resourceManager.getTemporaryResource(".csv");
    SimpleFeatureCollection collection =
       (SimpleFeatureCollection) value;
    HashMap<String, Object> params = new HashMap<>();
    params.put(CSVDataStoreFactory.FILE_PARAM.key,tmp.file()
       .getAbsoluteFile());
    params.put(CSVDataStoreFactory.STRATEGYP.key,
      "CSVAttributesOnlyStrategy");
    CSVDataStore store = (CSVDataStore) DataStoreFinder
      .getDataStore(params);
    store.createSchema(collection.getSchema());
    String name = store.getTypeName().getLocalPart();
    Transaction transaction = Transaction.AUTO_COMMIT;
    SimpleFeatureSource featureSource = store
       .getFeatureSource(name,transaction );
    if(featureSource instanceof FeatureStore) {
        CSVFeatureStore csvFeatureStore =
           (CSVFeatureStore)featureSource;
        csvFeatureStore.addFeatures(collection);
    }
    store.dispose();
    IOUtils.copy(tmp.in(),os);
}

Important Note

If you use curl to send text to a remote web service, it will remove line feeds if you send the data as ASCII. If you need the line feeds to be present you must use BINARY.

Build the process

• Need to define inputs
  • target dataset (Geography)
  • source dataset (Table)
  • Filter (where clause)
  • alias (if needed)
• Or
  • target dataset (Geography)
  • source dataset (Table)
  • XML encoded WFS2.0 Join

Filters

Simple:

STATE_NAME = State

Complex

A.STATE = STATE
(Set alias A)

Functions

strToLowerCase(State) = strToLowerCase(STATE_NAME)
district = substring(0,6,ward)
contains(geom,point)

How do we handle a "Joining" filter?

• Currently GeoTools (and hence GeoServer) has no way of handling these filters (outside of a carefully constructed SQL store).
• But we can reformulate them to work.

  • for each feature
  • find the attribute value required in the filter
  • substitute this into the filter and run against the other dataset to find the match
  • if matched add to output

• use a ReplacingFilterVisitor to walk the filter and update.

Coming Soon to a Repo near you

Thanks to Astun Ltd for funding this work