Support for Spatial Aggregation

Large data visual analysis is supported for SQL Server point tables. You can perform this analysis on both XY columns (where longitude and latitude and or XY values are stored as separate scalar values) and geometry columns (where point geometries are stored using the SQL Server type Geometry or Geography). Spectrum Spatial supports spatial aggregation (also known as spatial clustering) using a geohash ID to make tables scale-aware, offering better visualization of large amounts of data. This allows you to, for example, aggregate sum of sales or total number of points that you want to represent in a map. As the clustered point data is accessed by the client as vector data (in GeoJSON format), the client application can apply various styles to the data (including graduated symbols and different color ranges) and can also present the data as client-side heat maps.

The Enable Spatial Aggregation option is enabled only when the spatial aggregation is applicable. In case of XY tables, this is enabled only when a database connection is selected, and X and Y columns are already defined in the Map Catalog.

Note: In case of modifying XY tables, the Enable Spatial Aggregation option remains disabled when you switch to another table while performing modifications. This is applicable in case of XY tables only. For SQL-based Table, it remains enabled even when you switch to another point-based table.

Understanding Geohash

Geohash is a gridding technique that divides the world recursively into grid cells and assigns each grid a unique ID value. There are 12 geohash lengths. The first length divides the world into 32 grid cells (8x4 cells). The next length divides the grid cells into a further 32 (4x8 cells), resulting in 1024 cells covering the world, and so on.

Understanding Scale

When a table is made scale aware, each point in the table is assigned a geohash ID allowing it to be aggregated into higher length cells. When client applications make feature requests for a scale-aware table, they are expected to pass in the scale they wish to use as part of the MI SQL expression that is sent. The value for the scale is sent as a "meters per pixel" value, which is also called resolution in mapping libraries such as OpenLayers. The resolution is a value that is usually readily available to client-side mapping libraries.

On the server side, Spectrum Spatial will then choose the appropriate geohash length to use to aggregate the data. The table below shows how the geohash length is chosen based on the input meters per pixel value.


Geohash length chosen for aggregation	Where meters per pixel is
2	>= 19000
3	>= 2400 and < 19000
4	>= 600 and < 2400
5	>= 80 and < 600
6	>= 10 and < 80
7	>= 2 and < 10
8	>= 0.3 and < 2
9	>= 0.06 and < 0.3
10	< 0.06

For example, if the resolution of the map in the client is 600 meters per pixel or greater (and below 2400), then geohash length 4 is chosen as the aggregation length for the data. This relationship ensures that an appropriate number of aggregated features are returned to allow a sufficiently detailed map to be rendered in the client as a vector layer. The exact number of aggregated features returned will vary depending on the size of the map on the client device and the density and distribution of the map features being aggregated.

Feature Limit

For most scale-aware queries on a typical client-side map of 1024 pixels wide by 780 pixels high the response will be under 1000 features. However, the query can return over 1000 features for some resolutions. If your client map is larger than 1024 pixels then there is a greater possibility of returning over 1000 features. It is advised that the feature limit be increased in Spectrum Spatial™ Manager under Services > Features to a value of around 5,000 to 10,000 to ensure that responses over 1000 are returned without an error.

Adding a Geohash ID to a Table

A geohash ID must be manually added to an existing table to make it scale-aware and therefore available for spatial aggregation. For detailed instructions, see Adding a Geohash Index to a Table.

Enabling Tables for Spatial Aggregation

Spatial Aggregation can be enabled using Spectrum Spatial™ Manager when creating or modifying a named table (for more information, see Creating a Table and Creating an XY Table). You can see if a table has spatial aggregation enabled by viewing the table details page in Spectrum Spatial™ Manager. The Resource Info tab will display the geohash column and precision level, while the Columns tab will display the geohash column along with its attributes (such as its type and whether its indexed, read-only, or nullable).

Important: If spatial aggregation is enabled for a table, the maximum number of features should be increased from the default of 1000 to 10,000 records. This is because in some cases more than 1,000 aggregated features may be returned depending on the zoom level, the spatial extent of the map in the client, and the spatial distribution of features being aggregated (although no more than 2500 records are typically returned for maps where the client map is 1024 x 780 pixels). Use Spectrum Spatial™ Manager to adjust the MaximumFeatures setting for the Feature Service (see Managing the Feature Service Configuration).

Requesting Features from a Scale-Aware Table

Use MI SQL in a client application to request features from a scale-aware table. Clustering at the server is available using the REST version of the Search By SQL request in the Feature Service. The client can query using MI SQL aggregation methods and the SCALE clause.

Note: The MI_Transform function is not supported for aggregate queries on the geometry column. The projection of the data table in Spectrum Spatial should match the projection of the client map for aggregating large data on map. Alternatively, project the GeoJSON response in the client before adding it to a vector layer.

For more information on the SCALE clause (including sample queries) and aggregation methods such as MI_AggregateCentroid, see the MapInfo SQL Language Reference section of the Spectrum Spatial Guide.

Examples of Scale-Aware Queries

http://152.144.226.251:8080/rest/Spatial/FeatureService/tables/features.json?
q=SELECT MI_AggregateCentroid(SP_GEOMETRY), Count(*) as feature_count FROM "/LargePoints" 
WHERE MI_EnvelopesIntersect(FromWKT('POLYGON((-8627389.58 4756566.36,-8627389.58 5196843.64,-7844674.42 5196843.64,
-7844674.42 4756566.36,-8627389.58 4756566.36 ))','EPSG:3857'), SP_GEOMETRY) SCALE 611.49622628141

Performance Considerations

Consider these points to optimize performance:

Spectrum Spatial is designed to delegate most of the processing work to the DBMS at run time. Keep in mind in your system design that the performance and scalability depend heavily on the memory and processing power available to the database server.
We recommend using database query optimization tools (such as SQL Server Tuning Advisor) for performance tuning. Consult your database administrator to determine the best way to run small tests and tune the database indexes.
Better performance can be achieved with XY columns than with geometry columns (where point geometries are stored using SQL Server types Geometry or Geography). Where possible, for large data tables we recommend using XY Tables with the following indices: LONGITUDE, LATITUDE (or X and Y), the primary key column (a clustered index), GEOHASH column and any other aggregation columns used in the query. Your DBA or a tuning advisor may also recommend additional indices on a combination of these columns.