How to join an external table to a set of polygons, and symbolize on the joined values, in a web map?

Question

Say I have a table showing a number of statistics - for example, various gun crime by US State measures. I also have a dataset of state boundaries.

How can I join the table to the features in a web map, and symbolize the polygons using attributes from the table? The joined table must be specified within the web application (rather than hard-coded via ArcMap) since the statistics will be derived on-the-fly and will be specific to each user.

I need the ability to change the symbology on-the-fly, for example by choosing a different crime statistic to map, in which case the map should update immediately.

ArcGIS Server

Using ArcGIS Server 10.1 the REST API supports table joins. Are there any sample scripts which step through how to set this up?

Other environments?

Can the same thing be achieved using FOSS software?

Thanks. (This is a modified version of my earlier question since that hasn't yet received any responses)

Answer 1

It looks like the API supports a "Query table data source" with query element. So you should be able use a SQL statement to handle the join and summarize the data with a group by clause. Something along the lines of this:

select st.*, gc."Handguns murders" as hg_murders
from states as st
    inner join gun_crimes as gc on gc.State=st.STATE_NAME

As for FOSS, OGR has a stripped down SQL syntax that can perform table joins. You could set this up in virtual format pulling data from a variety of underlying data sources.