• Jump To … +
    addDefaultsToFilter.js config.js displayFiltersToWhere.js filterObjectToWhere.js filterObjectUtils.js filtersToTables.js healthCheck.js makeSql.js server.js units.js

  • makeSql.js

  • ¶ 
    "use strict";
  • ¶

    Functions to create Grainstore SQL queries. Grainstore expects to receive a subquery it can plug into another statement. Postgres requires subqueries to be named.

  • ¶

    A single request to the tiler will have two separate components that are used to build a single query. This is done because the specification format for these two operations is significantly different. By processing them separately in the tiler, the client-side code of the application sending requests can build these parameters more simply.

    • filterQuery: this contains model/value clauses that easily mapped to predicates in SQL WHERE clauses
    • displayQuery: these contain models to show, which then get grouped into an additional predicate in the SQL where clause. This was created as a sidecare to filterQuery because the case of ANDing a plot predicate with a null tree value is prohibitively complicated using filterQuery syntax.
  • ¶

    Performance note: We tried using ST_SnapToGrid to reduce the number of trees rendered. While rendering does get faster, database queries slow down by a factor of at least five. That particularly hurts in production where we have just one DB server and four renderers.

    var _ = require('underscore');

var util = require('util');

var filterObjectToWhere = require('./filterObjectToWhere');
var displayFiltersToWhere = require('./displayFiltersToWhere');
var filtersToTables = require('./filtersToTables');
var addDefaultsToFilter = require('./addDefaultsToFilter');
var config = require('./config');
var units = require('./units');
var utils = require('./filterObjectUtils');
  • ¶

    Create a SQL query to return info about map features. Assumes that instanceid is an integer, ready to be plugged directly into SQL

    function makeSqlForMapFeatures(filterString, displayString, restrictFeatureString, instanceid,
                               zoom, isUtfGridRequest, isPolygonRequest, instanceConfig) {
    var geom_spec = config.sqlForMapFeatures.fields.geom,
        geom_field = isPolygonRequest ? geom_spec.polygon : geom_spec.point,
        parsedFilterObject = filterString ? JSON.parse(filterString) : {},
        displayFilters = displayString ? JSON.parse(displayString) : undefined,
        restrictFeatureFilters = restrictFeatureString ? JSON.parse(restrictFeatureString) : undefined,

        filterObjectWithDefaults = addDefaultsToFilter(parsedFilterObject, zoom, isPolygonRequest),
        filterObject = units.convertFilterUnits(filterObjectWithDefaults, instanceConfig),
        tables = filtersToTables(filterObject, displayFilters, isPolygonRequest, isUtfGridRequest),

        where = '',
        displayClause = displayFiltersToWhere(displayFilters, restrictFeatureFilters, displayPlotsOnly(filterObject)),
        filterClause = filterObjectToWhere(filterObject),
        instanceClause = (instanceid ? _.template(config.sqlForMapFeatures.where.instance)({instanceid: instanceid}) : null);

    function addToWhere(clause) {
        return where ? '( ' + clause + ' ) AND ' + where : clause;
    }

    if (filterClause) {
        where = filterClause;
  • ¶

    Because some searches (e.g. on photos and udf’s) join to other tables, add DISTINCT so we only get one row.

            geom_field = util.format('DISTINCT(%s)', geom_field);
    } else if (showingPlotsAndTrees(displayFilters)) {
        var showAtZoom = _.template(config.showAtZoomSql)({zoom: zoom});
        where = addToWhere(showAtZoom);
    }
    if (displayClause) {
        where = addToWhere(displayClause);
    }
    if (instanceClause) {
        where = addToWhere(instanceClause);
    }
    if (where) {
        where = 'WHERE ' + where;
    }

    var otherFields;
    if (isUtfGridRequest) {
        otherFields = config.sqlForMapFeatures.fields.utfGrid;
    } else if (isPolygonRequest) {
        otherFields = config.sqlForMapFeatures.fields.polygon;
    } else {
        otherFields = config.sqlForMapFeatures.fields.base;
    }

    geom_field = util.format("%s AS %s",
                             geom_field, config.customDbFieldNames.geom);
    return _.template(
        'SELECT <%= fields %> FROM <%= tables %> <%= where %>'
    )({
        fields: geom_field + ', ' + otherFields,
        tables: tables.sql,
        where: where
    });
}
  • ¶

    If there are trees referenced in the filter object, narrow the display filters to only tree display filters.

    function displayPlotsOnly(filterObject) {
    var isTreeFilterObject = function(s) {return s.substring(0, 4) === 'tree'; };
    var treeKeys = _.filter(utils.filterObjectKeys(filterObject), isTreeFilterObject);
    return treeKeys.length > 0;
}

function showingPlotsAndTrees(displayFilters) {
    var isEmpty = (displayFilters === undefined || displayFilters === null),
        hasPlotAndTree = _.contains(displayFilters, 'Plot') && _.contains(displayFilters, 'Tree');
    return isEmpty || hasPlotAndTree;
}
  • ¶

    Create a SQL query to return info about boundaries. Assumes that instanceid is an integer, ready to be plugged directly into SQL

    function makeSqlForBoundaries(instanceid) {
    return _.template(config.boundaryGrainstoreSql)({
        instanceid: instanceid
    });
}

function makeSqlForCanopyBoundaries(instanceid, canopy_min, canopy_max, category) {
    return _.template(config.canopyBoundarySql)({
        instanceid: instanceid,
        canopy_min: canopy_min,
        canopy_max: canopy_max,
        category: category
    });
}

exports = module.exports = {
    makeSqlForMapFeatures: makeSqlForMapFeatures,
    makeSqlForCanopyBoundaries: makeSqlForCanopyBoundaries,
    makeSqlForBoundaries: makeSqlForBoundaries
};