How to put a semantically enabled autocomplete control into your applications

One of the most common application design patterns is to implement a lookup table - some piece of business data has been given a description, and possible a code or identifier.

When creating new data, a user is often needing to select a code/identifier for a piece of information. This is usually done as a dropdown, or if they are many entries, an autocomplete control is often used.

This works well - some people will just make hashes storing the key/value pair in their code, others will ensure it's published into their relational data store.

Where it starts to fall down is in multiple applications working together - who can agree on the meaning of a code?
Your code of CASE_NIGHTMARE_GREEN is applied by a user and treated by one application as the coming of Chthulu, but after an ETL, CSV export or webservices message, the next application treats it as something different - users not up to date with the latest Lovecraftian spy thrillers start to misinterpret the data and apply it to anything involving green suitcases in horrible colours.

How do you fix this?
The next logical step often becomes to add a description, so that a UI can explain the term, but in a non services oriented environment, that's trapped in your datastore.

This won't work in a multiple vendor scenario, at least not unless you want to share your DB with them.

Another approach is the Code Table service - a service that has a focus on only retrieving data about a given input identifier.

I've seen this done in at least one SOA, and it's not a terrible pattern - but each vendor still has to stand up their own code table services, and there's a lot of repetition.

What else can you do?
Soon it becomes obvious that you want a decent way to find a code and the related data, but you also want to support aliases - my CASE_NIGHTMARE_GREEN is your WALK_IN_THE_PARK.

This gets tricky, quickly, as 1-1 mappings are difficult - and either a collection of vendors pull together and standardise on a list and the mappings, or no one really collaborates and fragile mapping code is introduced.

By this point, fear of change often sets in as the interfaces between parties are fragile, or to push changes through the consortium of vendors becomes a nightmare of project management and communication.

If you haven't had to roll out minor enhancements to a standard with a number of other parties who just aren't quite interested, take my word for it - it's painful.

All is not lost, there is another way - and it's simple.

What's the way forward?

My recommendation here is to push your codes into a triplestore. It doesn't fix everything, but it becomes trivial to relate information to the code - aliases, for example, or descriptions.

A triplestore is a RESTful service that allows you to execute queries - if you can deal with mongodb or mysql, you should be able to comprehend what's going on.

Don't just take my word for it - here's one prepared earlier - SNOMED, SPARQL powered autocomplete UI components. Pretty neat stuff.

Here's what wikipedia has to say about SNOMED, if you haven't heard of it.

SNOMED CT Concepts are representational units that categorize all the things that characterize health care processes and need to be recorded therein. In 2011, SNOMED CT includes more than 311,000 concepts, which are uniquely identified by a concept ID, i.e. the concept 22298006 refers to Myocardial infarction. All SNOMED CT concepts are organized into acyclic taxonomic (is-a) hierarchies; for example, Viral pneumonia IS-A Infectious pneumonia IS-A Pneumonia IS-A Lung disease. Concepts may have multiple parents, for example Infectious pneumonia is also a child of Infectious disease. The taxonomic structure allows data to be recorded and later accessed at different levels of aggregation. SNOMED CT concepts are linked by approximately 1,360,000 links, called relationships

That's one big code table, and you can see it's grown beyond just code/name pairing to include more data.

One of the key things that has been highlighted by the freebase folks and a few other places is the common problem - from a bunch of user input, go locate an object or identifier related to that term.

The moment you have an autocomplete control like these, it instantly kicks your application from "user is entering data into a text field" into "user is describing a semantic object, and I can grab all of the information about it that is relevant to my user".

Unlike standard, relational powered applications, SKOS + SPARQL makes this trivial - you simply write out a preferred label (skos:prefLabel), and many alternative labels (skos:altLabel).
What does that look like? Here's a sample query showing a user searching for... ear wax.

Note the URIs (try clicking on them to find out more information), and the preferred label/aliased label in the resultset, and try the resultset as JSON.

Even if no other parts of your application is aware of linked data, you can see how this graph of information can be flattened and pushed into a standard data store, for later use.

How can I build myself one of these?

Installing 4store

For this exercise, let's install some of the requirements:
$ sudo apt-get install 4store

Now we'll instantiate a new store (think database):

$ sudo 4s-backend-setup reference_store
4store[5196]: backend-setup.c:185 erased files for KB reference_store
4store[5196]: backend-setup.c:310 created RDF metadata for KB reference_store

Fire up the backend service (think of it like /etc/init.d/mysql start)
$ sudo 4s-backend reference_store

Populate some data - we'll use something I've prepared earlier as in turtle format. It helps to think of turtle as yaml but with URIs and a bit more magic.


$ git clone git://github.com/CloCkWeRX/4store-reference-service.git
$ cd 4store-reference-service

$ 4s-import reference_store --format turtle data.ttl

We're good to go - let's put the endpoint up

$ 4s-httpd -p 8000 reference_store

Now there's a (restful) endpoint living at
http://127.0.0.1:8000/sparql/

and you can run queries on it via http://127.0.0.1:8000/test/ - though until Issue #93 is solve, you probably just want to open the test-query.html page - this query will bring back both sets of data.

$ chrome test-query.html

From here, you can see the plain text, csv, JSON or XML results.

How do I do this in PHP, Rails, etc?

There's a lot of client libraries out there - I'd suggest having a quick read through of http://www.jenitennison.com/blog/node/152 for most rails developers, or looking at the sparql-client gem.

Failing that, peruse the ClientLibraries.

Where can I learn more about SPARQL?

Step 1, learn Turtle. If you can comprehend YAML, you should feel fairly comfortable.

Step 2, I'd try SPARQL by example. There's a good chance that if you are thinking of an SQL concept you want, such as LIKE matching; there's a SPARQL equivalent (FILTER regexp).

Luckily 95% of what you learned with turtle is simply reused by SPARQL - it introduces variables, where clauses/graphs, filters, and a few other things... but that's really all that's new.

Where to from here?

If you were to deploy this internally within an organisation, your service is pretty much good to go. You may want to look at Graph Access Control to add in some security, and the related SparqlServer/Update APIs.

Was this easy enough?

In comparison to the other approaches I have seen, it's fairly good.

• It's trivial to put a front-end on your triplestore.
  You can roll your own with a minimum of fuss, or use things like https://github.com/kurtjx/SNORQL to provide an 'expert user' ability to inspect your data.
• Adding, removing, etc aliases is trivial - there's no schema to migrate or anything else troublesome, and you can add in extra data at the drop of a hat - even if it's unrelated to your core set.
• It's trivial to relate concepts to each other.
• Your ontology (schema) is already there for code tables - http://www.w3.org/TR/skos-reference/ - you'll never have to reinvent that
• There's products available that let you tie in your application behaviour/code tables right into Confluence or other platforms.

Generating Nutritional Data RDF from USDA, Part 2

I has a bit of a whinge yesterday about copyright, nutrition data, and so forth.

Today, my inbox has a nice copy of the NUTTAB data I want, I've located SR21, I've had someone else point me at canadian nutritional data too.

To get the NUTTAB data, you have to email Food Standards Australia, but thats not a huge deal.


So; progress:
I've made a script to import the USDA SR21 data into a database (ie, mysql), and render it out as RDF.

Installing it

Pretty easy stuff! Its in PHP, and makes use of PEAR.

# Get the code:
$ svn co svn checkout http://freebase-owl.googlecode.com/svn/trunk/nutrition/

# 0. Install dependencies
$ sudo apt-get install php-pear mysql wget unzip
$ sudo pear install -fa MDB2 XML_Beautifier

# 1. Get the SR21 data, extract it
$ wget http://www.nal.usda.gov/fnic/foodcomp/Data/SR21/dnload/sr21.zip
$ unzip sr21.zip

# 2. Make configuration
$ cp config.php.dist config.php
$ vim config.php

# 3. Create a database of your choosing, with the same settings as configuration
mysql -u root -p

CREATE DATABASE usda;

# 4. Run the install script. This will take a while as it imports all data. If it fails, just DROP the database and start again
$ php install.php

# 5. Give it a shot from the command line. "1002" is the USDA food id.
$ php rdfizer.php 1002

$ php rdfizer.php 1002 > 1002.rdf

#  6. Generate the whole set:
php generate-all.php

The basic plan: import everything, render out individual items, publish them statically on the web. Maybe later, get someone to stuff them all into a SPARQL endpoint.

Rinse, repeat with Canadian, Australian data. Grow a common ontology for Food, Nutrients, etc.

You can view some of the output RDF, I've not generated the whole set yet as my poor computer is far too old and creaky to do so.

Additionally, there are lots of linked data connections I want to make.

I want to link the sources with pubmed, the units with... something (side note: there's not much in the way of unit and measurement ontologies I could find!), the USDA style names with wikipedia/dbpedia/freebase; the compound names (PROCNT - protein content) with... something.

How silly is this: there's no semantic web url for milligrams. The best I could do was a few related concepts, because someone at wikipedia decided to merge all of the sub-articles for measurements into the single unit (ie, mg to g).

xOperator, the Valuation Industry, XSLT, GRDDL and RDF

I just discovered the most amazing thing. It's a jabber/xmpp sparql interface, called xOperator.

It sits between you, the end user, and multiple other agents and datastores. It uses XMPP (Google talk!) to interact with you.

You say things like:

me: add ds DBPedia http://dbpedia.org/sparql

me: query SELECT ?name WHERE {
   ?musician skos:subject <http://dbpedia.org/resource/Category:Australian_musicians> .
   ?musician foaf:name ?name .
}

bot: (Alright, this is dummied up, but I *did* have it working with other stuff before)
"Ben Lee"@de
"Billy Thorpe"@de
"Brody Dalle"@de
"Charlie McMahon"@de
"Craig Nicholls"@de
"Daevid Allen"@de
"Delta Goodrem"@de
"Fritz Hart"@de
"James George Thirlwell"@de
"Joe Dolce"@de
"Johnny Diesel"@de
"Kasey Chambers"@de
"Kevin Johnson"@de
"Natalie Gauci"@de
"Percy Aldridge Grainger"@de
"Peter Garrett"@de
"Peter Allen"@de
"Rick Springfield"@de
"Richard Franklin"@de
"Sophie Monk"@de
"Steven Kilbey"@de
"Stevie Wright"@de
"Tina Arena"@de
"Toni Collette"@de

me: add template "Tell me about *" "PREFIX owl: <http: org="" 2002="" 07="">
PREFIX xsd: <http: org="" 2001="">
PREFIX rdfs: <http: org="" 2000="" 01="">
PREFIX rdf: <http: org="" 1999="" 02="">
PREFIX foaf: <http: com="" foaf="" 1="">
PREFIX dc: <http: org="" dc="" elements="" 1="">
PREFIX : <http: org="" resource="">
PREFIX dbpedia2: <http: org="" property="">
PREFIX dbpedia: <http: org="">
PREFIX skos: <http: org="" 2004="" 02="" skos="">


SELECT ?name WHERE {
   ?thing skos:subject <http: org="" resource=""> .
   ?thing foaf:name ?name .
}"

bot: template added

me: Tell me about German_musicians

me: Tell me about German_artists
bot: Store dbpedia answered:
     name
"A. R. Penck"@de
"Adolf Ziegler"@de
"Adolph Menzel"@de
"Albrecht Altdorfer"@de
"Alfred Harth"@de
"Alfred Rethel"@de
"Alfred Mahlau"@de
"André Butzer""@de
"Bruno Paul"@de
"Bärbel Bohley""@de
"Carl Grossberg"@de
"Carsten Höller""@de
"Charles Crodel"@de
"Christian Möller""@de
"Christoph Meckel"@de
"Christoph Ruckhäberle""@de
"Cosmas Damian Asam"@de
"Dieter Grossmann"@de
"Dieter Roth"@de
"Eberhard Bosslet"@de
"Eberhard Havekost"@de
"Egid Quirin Asam"@de
"Elsa von Freytag-Loringhoven"@de
"Ernst Deger"@de
"Hugo Höppener""@de
"Franz Joseph Spiegler"@de
"Franz Wilhelm Seiwert"@de
"Franz Xaver Feuchtmayer der Jüngere""@de
"Franz Xaver Feuchtmayer"@de
"Frieder Nake"@de
"Fritz Maurischat"@de
"Gerd Aretz"@de
"Gerd Arntz"@de
"Gert Heinrich Wollheim"@de
"Gert Neuhaus"@de
"Hans Haacke"@de
"Hannah Höch""@de
"Hans Grundig"@de
"Hans Richter"@de
"Heiko Daxl"@de
"Heinrich Aldegrever"@de
"Herma Auguste Wittstock"@de
"Herbert Holzing"@de
"Horst Janssen"@de
"Horst Antes"@de
"Ignaz Günther""@de
"Jankel Adler"@de
"Hans Arp"@de
"Jim Avignon"@de
"Johann Peter Melchior"@de
"Johann Baptist Straub"@de
"Johann Joachim Kändler""@de
"Johann Joseph Christian"@de
"Johann Melchior Dinglinger"@de
"Johann Baptist Zimmermann"@de
"Johann Michael d.J. Feuchtmayer"@de
"Johannes Baader"@de
"Jonas Burgert"@de
"Joseph Anton Feuchtmayer"@de
"Gotthard Johnny Friedlaender"@de
"Jonathan Meese"@de
"Joseph Beuys"@de
"Karin Ulrike Soika"@de
"Karl von Fischer"@de
"Karl Hubbuch"@de
"Klaus Herlitz"@de
"Konrad Peter Cornelius Klapheck"@de
"Kurt Wegner"@de
"Leo Kahn"@de
"Franz Karl Leopold von Klenze"@de
"Lore Lorentz"@de
"Ludwig Fahrenkrog"@de
"Martin Kippenberger"@de
"Manfred Mohr"@de
"Manfred W. Jürgens""@de
"Maria Innocentia Hummel"@de
"Mariele Neudecker"@de
"Werner Mathias Goeritz Brunner"@de
"Mary Bauermeister"@de
"Max Ernst"@de
"Michael Buthe"@de
"Matthäus Günther""@de
"Michael Sowa"@de
"Otto Eckmann"@de
"Otto Hupp"@de
"Ottmar Hörl""@de
"Otto Pankok"@de
"Otto Piene"@de
"Peter Vischer"@de
"Peter Dreher"@de
"Rebecca Horn"@de
"Ruben Talberg"@de
"Rosemarie Trockel"@de
"Sandra Rauch"@de
"Silvia Quandt"@de
"Thomas Eller"@de
"Thomas Schütte""@de
"Tilman Riemenschneider"@de
"Tomma Abts"@de
"Torsten Amft"@de
"Uwe Laysiepen"@de
"Veit Stoß""@de
"Virgil Solis"@de
"Wilfried Behre"@de
"Wilhelm Heine"@de
"Wilm Weppelmann"@de
"Wolfgang Joop"@de
"Wolfgang Müller""@de
"Wolfgang Tillmans"@de
"Evi Moechel"@de

(10:22:30 PM) clockwerx@livejournal.com/Home: Tell me about Australian_musicians
(10:22:32 PM) daniel.oconnor: Store dbpedia answered:
     name
"Ben Lee"@de
"Billy Thorpe"@de
"Brody Dalle"@de
"Charlie McMahon"@de
"Craig Nicholls"@de
"Daevid Allen"@de
"Delta Goodrem"@de
"Fritz Hart"@de
"James George Thirlwell"@de
"Joe Dolce"@de
"Johnny Diesel"@de
"Kasey Chambers"@de
"Kevin Johnson"@de
"Natalie Gauci"@de
"Percy Aldridge Grainger"@de
"Peter Garrett"@de
"Peter Allen"@de
"Rick Springfield"@de
"Richard Franklin"@de
"Sophie Monk"@de
"Steven Kilbey"@de
"Stevie Wright"@de
"Tina Arena"@de
"Toni Collette"@de

Big whoop you say.

Well, I've been working on both an XML_GRDDL package (php5), and the relevant GRDDL transformations to turn LIXI Valuation documents into the relevant RDF/XML representation.

I'm also re-deploying work's internal Jabber server in the not too distant future.

So:
1. We handle a pretty big chunk of all residential valuations within Australia.
2. A lot of that gets described adequately in LIXI
3. There's now or soon to be a GRDDL transformation or two for that
4. And a GRDDL parser for PHP
5. Add in xOperator
6. Add in robust jabber services
7. Mix in a few microformats as well

Viola:
My workplace is half a step away from becoming a knowledge enterprise as well as a valuation panel manager.

That's not bad for a few days off!

Imagine these future scenarios:

Working smarter with internal data and external clients:

Internal xOperator to answer questions like "show me similar valuations we've done to address xyz", and "show me everything valuer X has completed over $1million"

Paid commercial access to a SPARQL service as well, for larger clients who want to better understand the marketplace
Paid commercial access to an xOperator style service for smaller clients, who just want a couple of pre-built, on demand answers


Publishing old (> 12-24 months) LIXI data on the web
Merge our data up with other countries, like the US, for trend analysis
Provide anonymized public data, with http basic auth. to protect names, addresses, privacy
Merge diverse sets of data - ie, imagine EuroStat data for Australia, but with an atomic level of detail available about the raw input

Provide semi-automated valuations based on known data
Suburb X has a population of Y and an area of Z (db/wikipedia), factor that into calculations