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.

But

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

Why?

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:

A Joining Process

But

Leverage CSVDataStore

@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

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?

<?xml version="1.0" encoding="UTF-8"?>
<wps:Execute version="1.0.0" service="WPS" ...>
  <ows:Identifier>TableJoin:simpleJoinTables</ows:Identifier>
  <wps:DataInputs>
    <wps:Input>
      <ows:Identifier>target</ows:Identifier>
      <wps:Reference mimeType="text/xml" xlink:href="http://geoserver/wfsa"
      method="POST">
        <wps:Body>
          <wfs:GetFeature service="WFS" version="1.0.0" outputFormat="GML2"
            xmlns:topp="http://www.openplans.org/topp">
            <wfs:Query typeName="topp:states"/>
          </wfs:GetFeature>
        </wps:Body>
      </wps:Reference>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>source</ows:Identifier>
      <wps:Data>
        <wps:ComplexData mimeType="text/csv">
        <![CDATA[State,inc1980,inc1990,inc1995,inc2000,[...],inc2009,inc2012
Alabama,7465,14899,19683,23521,26338,30894,33096,35625
[....]
Wyoming,11018,16905,21514,27372,32808,40655,45705,48670]]>
</wps:ComplexData>
      </wps:Data>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>joinfilter</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>STATE_NAME=State</wps:LiteralData>
      </wps:Data>
    </wps:Input>
  </wps:DataInputs>
  <wps:ResponseForm>
    <wps:RawDataOutput mimeType="application/zip">
      <ows:Identifier>result</ows:Identifier>
    </wps:RawDataOutput>
  </wps:ResponseForm>
</wps:Execute>

Digression

CSVDataStore

WPS PPIO

Well no one has added one of these before (except Andrea) but it should be easy enough!

<wps:Status creationTime="2017-08-03T16:17:23.623Z">
  <wps:ProcessFailed>
    <ows:ExceptionReport version="1.1.0">
      <ows:Exception exceptionCode="NoApplicableCode">
        <ows:ExceptionText>Failed to retrieve value for input target
        net.opengis.wfs.impl.FeatureCollectionTypeImpl
        cannot be cast to java.lang.String</ows:ExceptionText>
      </ows:Exception>
    </ows:ExceptionReport>
  </wps:ProcessFailed>
</wps:Status>

Another issue

curl -u admin:geoserver -XPOST -H 'Content-type: text/xml' \
--data-binary @request.xml \
http://localhost:9000/geoserver/ows?service=wps&version=1.0.0&request=Execute

STATE_NAME,STATE_FIPS,SUB_REGION,STATE_ABBR,LAND_KM,WATER_KM,PERSONS,
Illinois, 17, E N Cen, IL, 143986.61, 1993.335, 1.1430602E7, 2924880
Missouri, 29, W N Cen, MO, 178445.951, 2100.115, 5117073.0, 1368334
Arizona, 04, Mtn, AZ, 294333.462, 942.772, 3665228.0, 940106.0, 13
Oklahoma, 40, W S Cen, OK, 177877.536, 3170.998, 3145585.0, 855321.0

Further Work

Coming Soon to a Repo near you

Thanks to Astun Ltd for funding this work

Slides are at http://www.ianturton.com/talks/tables-foss4g/index.html

Ian Turton, [email protected], twitter ijturton

SpaceForward
Right, Down, Page DownNext slide
Left, Up, Page UpPrevious slide
POpen presenter console
HToggle this help