11-10-2017 12:58 AM
I would like to inquire about the possibility of iterating through the previous rows for each row and getting the output for this row.
For example, I have a table of 10 rows numbered from 1 to 10. What I need to do is the following:
-Sum the result of row 1 for row 1
-Sum the result of row 1 and row 2 for row 2
-Sum the result of row 1 and row 2 and row 3 for row 3
-Sum the result of row 1 and row 2 and row 3 and row 4 for row 4
-Same process till row 10
Is is possible to do it with functional attributes, or any other tool ?
11-10-2017 09:28 AM
Functional Attributes command would not be appropiate as it process record by record and does not see or use information from other records. This leads us to think about Analytical Merge or Aggegation commands, both of which can use attrbutes from other records. The Analytical Merge command however collapses the records down from many to one (for each logical grouping).. So that leaves us with only Aggregation.
The expression system does not have an expression that explicity processes values from the previous rows (that might be a good product idea) but If your rows have a field that defines the order there may be an odd expession that can be used.
Processing all Records:
Let's pretend our input table is States from the USSampledata.mdb, and for some reason we want sum up all the population that has an ID value that is less than or equal to the id of the output row (input.id). Assuming you want to process the entire table we will need a constant value so that when comparing every record with every record that all records are considered.
Q1. Input the States table into Functional Attributes command and build an expression that is simply a constant value such as "A".
Q2. Select the Aggregation command and define the Q1 query as both the Summary feature and the Detail feature.
Of course you could have used other summarizing expression instead of SUM such as AVG, MIN, MAX, etc.
Example of summarizing in groups (by attribute)
Again we need a field that defines an order by for each goup. In a GeoMedia transportation networks a route is a set of segments that are related by one or more attributes such as Route_Name. Each segment along the route typically also carries a begin and ending measure values (mile or kilometer post values). GeoMedia Transportation has tools to populate and manage this information. We could use a similar summary expression to summarize information for all segments that have lower or higher measure (mile or KM post values) for each route.
For example, Aggregate routes to routes by the Route_Name (Linear Reference Key(s)) and sum up values with lower measures than current seg.
SUM(IF(Detail.End_Meas <= Input.Beg_Meas, Detail.TRAFFIC_VOLUME, 0))
11-14-2017 02:44 AM
Thank u for your hint psmith
It works perfectly in my case !
Is it possible to use a similar expression to order a series of polylines connected together according to their physical order ?
I will explain myself more clearly:
Suppose we have 4 polylines AB,CD,BC,DE connected together by endpoint to startpoint.
Is it to possible to order them in the right order: AB, BC, CD, DE ?
Thank u for your help
11-14-2017 03:36 PM
The short answer to your question is no, I don’t think the previous logic applies to merging line segments in an ordered series; it would be a neat feature for MERGE to support an orderby but such an enhancement could be difficult to implement and may have a performance hit.
Your question is similar to a previous discussion where it was desirable to have the ability to control the order by in Analytical Merge of polylines. Merge however does not provide an orderby option and has it’s own logic (most likely using a scan from lower left to upper right using topological edges).
The CREATEPOLYLINE function does support an ORDERBY attribute but the input CREATEPOLYLINE is POINTS… not existing polylines. On the surface it seems like we could use POINTS to turn the polylines into points then connect the dots using a MIN or MAX value for orderby but if you work through that logic, you will find that the first or last 2 points will have the same MIN or MAX values.
The ability to hook different queries together to achieve a result leaves some room for some creative and possibly strange solutions. Someone else may see an obvious solution but I could only come up with this weird query chain of 5 queries that “might” give you an acceptable solution.
In this workflow we get the start points of all segments, then pick off the last MAX point and union these to use as input to CREATEPOLYLINE. This workflow makes big assumption that you have a common attribute for each segment that is related; we could have related the segments by touching but I’m assuming there’s overlapping routes or intersections. We also make another big assumption that you have the AB BC CD …etc attributes stored in attribute fields such as Begin_Meas and End_Meas … Side Note: GeoMedia Transportation has a Calibration command that can calculate begin and end measure double precision values in either Geometry direction or Cardinal (S to N, and E to W) directions which can provide a nice series of segments ordered by their measures.
Now for the weird workflow:
Q1 FA Start Points
Use Functional Attributes to create start points for each segment
Q2a FA Points
Input segments into Functional Attributes to create points for all segments.
Q2b Merge for Max Point
Input Q2a into Analytical Merge and merge the points using the “By attribute” option, selecting the common attribute (for example I used Route_Name).
Create an expression to get the max measure (A, B, C, D.. etc. in your case)
Use the LOOKUP expression to lookup the point geometry with the max value.
LOOKUP(MAX(Input.End_Meas), Input.End_Meas, Input.FA_Points)
Q3 Union of Q1 and Q2b
Input Q1 and Q2b queries into the Union command so we can essentially append the last point from Q2b to the start points found in Q1. This will require that you use the Attributes button to rename some fields so they “line up”. We could have done this earlier for geometry field names but it’s good practice to see how it’s done.
For Q1 => Rename Geom_StartPoints to Geom_Pts
For Q2 => Rename FA_Geom_Max to Geom_Pts
Rename FA_End_Meas to Beg_Meas
Q4 Merge of Q3 for Polyline
Finally, we can now input the points from Q3 into Analytical Merge to connect the dots using CREATEPOLYLINE.
Merge by attribute using the common attribute fieldname.
You can alter the existing output geometry to something like:
11-15-2017 06:52 AM
The solution (if there is one) really depends on your data.
If your segments have a common attribute such as route_name, and are numbered then we can simplify the process a bit. For example, if the segments have numeric From / To values that asscend in the order needed for the polyline OR if the segments have a single numeric attribute indicating the order (in ascending fashion)..
Seg | From | To |
A 1 2
B 2 4
C 4 5
D 5 8
OR simply a single attribute with an order by numeric value:
In this case you could do the following queries:
Qa FA Points
Input the segments into Functional Attributes to generate points
Qb Merge of Qa
Input the Qa query points into Analytical Merge to merge the points where touching and calculate an AVG FROM/TO value. Merge using the "By attribute and touching" option; select the attribute field name that is common to the segments (for eample Route_Name)
Qc Merge of Qb
Input the Qb merge query into Analytical Merge, using the "Merge by attribute" option (not touching); select the attribute field name that is common to the segments (for example Route_Name).
Highlight (select) the FA_Points in the Output section and use the Properties button to alter the existing expression for FA_Points
*Note: actually you could have built this expression as part of the previous query but the result would be a secondary geometry field requiring you to use Attribute Selection command to unselect the undesirable geometry field to make the polyline the primary geometry.