Tutorial: Query API

The Query API allows you to access Mapfluence datasets in powerful ways, much like you would through SQL. With the Query API you can find the needle in the haystack or extract additional data using spatial relationships.

Here we follow up the example from the Visual API tutorial: maps provided the background context. The user finds a region of interest. Now she want socio-economic facts; which, as the developer, you can display as raw text, tables, or charts.

Identify your data source

Here we use the US Census 2000 data tables. There are many other datasets we could choose from; but this best fits our example.

In the Data Catalog, you'll see has the code name:

us_census00.stats

Additionally, notice the table's Associated Geometries, which indicates which what geographies the data is available for. Block groups are the most granular, and the most useful for this application.

A few interesting socio-economic facts from the data table (and their code names) include:
  1. Population in 2000. (us_census00.stats.cnt_pop)
  2. Per capita income in 2000. (us_census00.stats.av_pci)
  3. Number of renters in 2000. (us_census00.stats.cnt_hhro)
  4. Number of single family attached homes in 2000. (us_census00.stats.cnt_hu1unta)
  5. Number of children enrolled in Kindergarten through 12th grade who attended a private school in 2000. (us_census00.stats.cnt_k12priv)

Testing queries with the API Lab

All request to the Query API's web services are made as HTTP GET requests with arguments as URL query parameters; for clarity (because of escaping requirements), the following examples use the Mapfluence Javascript API. You can copy and paste these examples into the Mapfluence API Lab.

Test it out. Copy the snippet of code below, paste it into the lab. Then hit the Run Code button.

// apilab-template: code-evaluates-to-url
// the above is a directive to the lab as to how to evaluate the code
// don't worry about this, just make sure to include it

var s = new MF.SpatialQuery({
    format: 'json', // encode the response as JSON
    ashtml: 1,      // return JSON formated as HTML for readability
    select: ['id','us_census00.stats.cnt_pop'],
    // get the geometry ID and us_census00.stats.cnt_pop value
    from: 'us_census00.blkgrp_geom', // for census block group geometries
    limit: 1 // return only one result
});

s.url()

The query above is a basic query to the Spatial Query web service. Next we examine the structure.

Query Result Structure

Hitting the Run Code button should have caused a bunch of results to appear in the upper pane. Each result record should look something like this:

{
    "data": [
        {
            "id": 361461,
            "us_census00.stats.cnt_pop": [1201],
            "us_census00.stats.periods": ["2000"],
        }
    ],
    "success": true
}

All Query API responses have a success flag. Successful responses have a data section. For this particular result:

  1. "id": a unique identifier for the feature
  2. "name": the name of the feature; in this case, a US FIPS code
  3. "us_census00.stats.cnt_pop": the variable and value you queried
  4. "us_census00.stats.periods": the period the value is valid for

This is marginally useful: what is ID 361461? Thus:

Get Geometry Info

Geometry features have a few intrinsic fields, among them:
  1. name
  2. geometry
  3. centroid

You can add these to the select list:

select: ['id','name','geometry','us_census00.stats.cnt_pop']

Now your results will include the name of the feature (which is not really useful for census block group, but for ZIPs, counties, etc), and a boundary which you could render on the client, if you so desired.

Adding Geometry Filters

A geometry filter constrains a query to a geographic location: a region of interest, a bounding window, or even geometries in a Mapfluence geometery table. In this example, let's presume the region of interest is whatever was under a mouse click on a map: a lat/lng.

Modify your snippet by adding this where clause to the argument list (don't forget commas):

where: new MF.GeoFilter('contains_pct', 'POINT(-121 37.75)', 1)

The geometry filter in this case is a WKT (Well-Known Text) POINT string (notice the order longitude then latitude).

There are three basic geometry filters that you can do: intersects, contains percent, and percent within.

This will return a single result. However, it may be more useful to provide a number of features and their data around the point. For this, use the RANGE extension, which extends the search around the specified geometry a given distance:

where: new MF.GeoFilter('pct_within', 'RANGE(3mi POINT(-121 37.75))', 0.75) // 3 miles from point, and only geometries 75% contained in the search area.

Make sure to remove:

limit: 1

from your snippet; otherwise you'll get only one result.

Data Filters

These results can be filtered further; say our user is interested in locations only where the Per capita income is greater than $30,000.

Modify your where clause:

where: [
  new MF.GeoFilter('pct_within', 'RANGE(3mi POINT(-121 37.75))', 0.75),
  new MF.DataFilter('us_census00.stats.av_pci', 30000, 'gt')
  // the named variable must be greater than the specified threshold
]

in SQL parlance, these conditions joined with AND.

Ordering

Our result set is small and it would be easy to order results on the client. But, if you want, you can request ordered results:

order_by: 'us_census00.stats.av_pci'

Add this the arguments (don't forget to check your commas), and re-run.

The Complete Example

// apilab-template: code-evaluates-to-url

var s = new MF.SpatialQuery({
    format: 'json', // encode the response as JSON
    ashtml: 1,      // return JSON formated as HTML for readability
    select: [
      'id',
      'name',
      'us_census00.stats.av_pci', // per capita income
      'us_census00.stats.cnt_pop', // population count
      'us_census00.stats.cnt_hhro', // # of renters
      'us_census00.stats.cnt_hu1unta', // # of single family homes
      'us_census00.stats.cnt_k12priv' // # children private school
      ],
    // get the geometry ID and us_census00.stats.cnt_pop value
    from: 'us_census00.blkgrp_geom', // for census block group geometries
      where: [
   new MF.GeoFilter('pct_within', 'RANGE(3mi POINT(-121 37.75))', 0.75),
   new MF.DataFilter('us_census00.stats.av_pci', 30000, 'gt')
   // the named variable must be greater than the specified threshold
 ],
 order_by: 'us_census00.stats.av_pci'
});

s.url()