|Functions / Aggregation Functions|
Computes the sum of an aggregation of values which are adjusted based on the proportion of two geometry objects.
ProportionSum ( n, value_geometry, reference_geometry )
n is a numeric expression indicating the values to add,
value_geometry is a geometry expression, and
reference_geometry is a geometry expression.
Returns the proportional sum of values between two geometry objects (one an envelope using MI_Box, the other passing in a geometry object).
SELECT proportionSum(1, obj, MI_BOX(-104.493426, 39.399710, -101.282362, 41.474182, 'epsg:4269')) as ProportionSum from states
A use case for this operation is finding the proportional number of people within a given region. In the following example, the population within a given area of the United States is returned:
SELECT proportionSum(Pop_2000, obj, MI_BOX(-110.515461, 38.525829, -106.905772, 42.324814, 'epsg:4326')) from "/Samples/NamedTables/USA"
This example can also be duplicated using the sum operation. The following example returns the same results as the above proportionSum example:
SELECT SUM(Pop_2000 * MI_AREA(MI_INTERSECTION(obj, MI_Box(-110.515461, 38.525829, -106.905772, 42.324814, 'epsg:4326')), 'sq mi', 'Spherical') / MI_AREA(obj, 'sq mi', 'Spherical')) from "/Samples/NamedTables/USA" WHERE obj intersects MI_BOX(-110.515461, 38.525829, -106.905772, 42.324814, 'epsg:4326')
ProportionSum is an aggregation function which can only be used in an aggregating Select statement. Null values for n, value_geometry, and/or reference_geometry are ignored. If there are only null values supplied in the aggregation, then a null value is returned.
The values of n times a proportion are accumulated. The proportion is determined to be the area of the overlap of the value_geometry and the reference_geometry divided by the area of the value_geometry.
For example, a reference geometry might be a region representing a flood risk which intersects several counties. The population for each county could be proportionally summed based on the proportion of the county’s area which overlaps the risk region.
The ProportionSum aggregation function can be replaced with the following formula:
Sum (n * AreaOverlap(value_geometry, reference_geometry)) / Area(value_geometry))
AreaOverlap(value_geometry, reference_geometry)) is also referred to as Area(Intersection(value_geometry, reference_geometry)).
If the coordinate system of the value_geometry is Non-Earth, a Cartesian area calculation will be performed.