SCALE

SCALE allows aggregation of multiple point geometries into a single point within a grid. MI SQL query language now supports a SCALE clause when the query has aggregation functions that have native support at the data source level. This SCALE-based aggregation will only work with point geometry features and is currently limited to MS SQL Server databases. The projection of the data table in Spectrum Spatial should match the projection of the client map for aggregating large data on map. SCALE is measured in units of meters or pixels.

For example:

SELECT <AGGREGATION_METHOD>(<COLUMN_NAME>) FROM <NAMED_TABLE> SCALE 10000

where:

  • AGGREGATION_METHOD is a valid MI SQL aggregation method:
    • Avg
    • Count
    • Sum
    • Min
    • Max
    • MI_AggregateCentroid
    • MI_AggregateEnvelope
  • COLUMN_NAME is a valid column in the table. The column should be of type numeric.
  • NAMED_TABLE is the data source for the query in the form of a named table

The SCALE clause is supported in aggregate search queries (Search By SQL in REST) with the following aggregate functions for geometry and XY columns in all supported SQL Server databases and Generic JDBC data access based XY tables:

Examples

SELECT MI_AggregateCentroid(SP_GEOMETRY), Count(*) as feature_count FROM 
"/LargePoints" GROUP BY STABB SCALE 19568 ORDER BY feature_count0
SELECT MI_AggregateEnvelope(SP_GEOMETRY), Count(STABB) FROM 
\"/NamedTables/SQLServer2012/Geohash/GeohashUSPOI\" SCALE 10000
SELECT MI_AggregateCentroid(Obj), Count(STABB), STABB FROM 
\"/NamedTables/SQLServer2014/Geohash/GeohashUSPOI_XY\" GROUP BY STABB 
SCALE 10000 ORDER BY STABB LIMIT 4 OFFSET 40