Trailism: Python Script for Batch Analyzing GPX Files & Reporting Statistics for Curves & Stretches

Run this script from a folder with GPX files and get a markdown formatted report with statistics for curves and stretches!

import math
import os
import gpxpy

def calculate_distance(point1, point2):
    # Create gpxpy points for distance calculation
    p1 = gpxpy.gpx.GPXTrackPoint(point1['latitude'], point1['longitude'])
    p2 = gpxpy.gpx.GPXTrackPoint(point2['latitude'], point2['longitude'])
    return p1.distance_2d(p2)

def calculate_bearing(lat1, lon1, lat2, lon2):
    # Convert to radians
    lat1, lon1, lat2, lon2 = map(math.radians, [lat1, lon1, lat2, lon2])
    
    # Calculate bearing
    dlon = lon2 - lon1
    y = math.sin(dlon) * math.cos(lat2)
    x = math.cos(lat1) * math.sin(lat2) - math.sin(lat1) * math.cos(lat2) * math.cos(dlon)
    bearing = math.atan2(y, x)
    
    # Convert to degrees
    return math.degrees(bearing)

def calculate_angle_change(p1, p2, p3):
    # Calculate bearings for both segments
    bearing1 = calculate_bearing(p1['latitude'], p1['longitude'], 
                               p2['latitude'], p2['longitude'])
    bearing2 = calculate_bearing(p2['latitude'], p2['longitude'], 
                               p3['latitude'], p3['longitude'])
    
    # Calculate the change in bearing
    angle_change = bearing2 - bearing1
    
    # Normalize to -180 to +180
    if angle_change > 180:
        angle_change -= 360
    elif angle_change < -180:
        angle_change += 360
    
    return angle_change  # Positive for right turns, negative for left turns

def combine_segments_by_curvature(points, curve_angle_threshold=120, look_ahead_distance=40):
    print("Starting curve detection...")
    combined = []
    i = 0
    straight_points = [points[0]]  # First point is included
    
    while i < len(points) - 2:
        print(f"Processing point {i} of {len(points)}")
        
        look_ahead_points = []
        cumulative_distance = 0
        cumulative_angle = 0
        last_angle_sign = 0
        j = i + 1
        
        while j < len(points) - 2 and cumulative_distance < look_ahead_distance:
            angle = calculate_angle_change(points[j-1], points[j], points[j+1])
            
            if last_angle_sign == 0:
                cumulative_angle = abs(angle)
                last_angle_sign = 1 if angle > 0 else -1
            elif (angle > 0 and last_angle_sign > 0) or (angle < 0 and last_angle_sign < 0):
                cumulative_angle += abs(angle)
            else:
                cumulative_angle = abs(angle)
                last_angle_sign = 1 if angle > 0 else -1
            
            look_ahead_points.append(points[j])
            cumulative_distance += points[j]['distance']
            
            if cumulative_angle >= curve_angle_threshold:
                # Found a curve - add any collected straight points first
                if straight_points:
                    # Include the distance TO the first point of the curve
                    total_distance = sum(p['distance'] for p in straight_points[1:])
                    straight_segment = {
                        'start_point': straight_points[0],
                        'end_point': straight_points[-1],
                        'distance': total_distance,
                        'elevation_change': straight_points[-1]['elevation'] - straight_points[0]['elevation'],
                        'points': straight_points.copy(),
                        'type': 'straight',
                        'gradient': ((straight_points[-1]['elevation'] - straight_points[0]['elevation']) / 
                                   total_distance * 100) if total_distance > 0 else 0
                    }
                    combined.append(straight_segment)
                    straight_points = []
                
                # Add the curve segment - include ALL points from i to j inclusive
                curve_points = []
                for k in range(i, j + 1):  # Include point j
                    curve_points.append(points[k])
                
                # Calculate curve distance including the distance TO point j
                curve_distance = sum(points[k]['distance'] for k in range(i + 1, j + 1))
                
                curve_segment = {
                    'start_point': points[i],
                    'end_point': points[j],
                    'distance': curve_distance,
                    'elevation_change': points[j]['elevation'] - points[i]['elevation'],
                    'points': curve_points,
                    'type': 'curve',
                    'cumulative_angle': cumulative_angle,
                    'turn_direction': 'right' if last_angle_sign > 0 else 'left',
                    'gradient': ((points[j]['elevation'] - points[i]['elevation']) / 
                               curve_distance * 100) if curve_distance > 0 else 0
                }
                combined.append(curve_segment)
                i = j
                straight_points = [points[j]]  # Start new straight section from end of curve
                break
            
            j += 1
        
        if cumulative_angle < curve_angle_threshold:
            if i < len(points) - 1:
                straight_points.append(points[i+1])
            i += 1
    
    # Add any remaining straight points as final segment
    if straight_points:
        total_distance = sum(p['distance'] for p in straight_points[1:])
        if total_distance > 0:
            straight_segment = {
                'start_point': straight_points[0],
                'end_point': straight_points[-1],
                'distance': total_distance,
                'elevation_change': straight_points[-1]['elevation'] - straight_points[0]['elevation'],
                'points': straight_points,
                'type': 'straight',
                'gradient': ((straight_points[-1]['elevation'] - straight_points[0]['elevation']) / 
                           total_distance * 100) if total_distance > 0 else 0
            }
            combined.append(straight_segment)
    
    print("Finished curve detection")
    return combined
    
def analyze_gpx(gpx_file):
    with open(gpx_file, 'r') as f:
        gpx = gpxpy.parse(f)

    # Get the true track length
    true_length_2d = gpx.length_2d()
    
    # Process points and calculate distances between them
    points = []
    
    for track in gpx.tracks:
        for segment in track.segments:
            # First, collect all points without distances
            track_points = []
            for point in segment.points:
                track_points.append({
                    'latitude': point.latitude,
                    'longitude': point.longitude,
                    'elevation': point.elevation,
                    'distance': 0
                })
            
            # Then calculate distances, starting from the first point
            for i in range(len(track_points)):
                if i > 0:  # For all points except the first
                    p1 = gpxpy.gpx.GPXTrackPoint(
                        track_points[i-1]['latitude'], 
                        track_points[i-1]['longitude'], 
                        elevation=track_points[i-1]['elevation']
                    )
                    p2 = gpxpy.gpx.GPXTrackPoint(
                        track_points[i]['latitude'], 
                        track_points[i]['longitude'], 
                        elevation=track_points[i]['elevation']
                    )
                    track_points[i]['distance'] = p1.distance_2d(p2)
            
            points.extend(track_points)

    # Continue with segment analysis...
    segments = combine_segments_by_curvature(points)
    
    # Calculate overall statistics
    curve_segments = [s for s in segments if s['type'] == 'curve']
    straight_segments = [s for s in segments if s['type'] == 'straight']
    
    curve_distance = sum(s['distance'] for s in curve_segments)
    straight_distance = sum(s['distance'] for s in straight_segments)

    # Verify distances
    calculated_total = sum(p['distance'] for p in points)
    print(f"Check: Curve distances and straight distances should match total length: {curve_distance + straight_distance:.1f} m")
    print(f"Check: The distance diff. from cum. segments and gpx length is: {calculated_total:.1f} m, {true_length_2d:.1f} m\n")
    if abs(calculated_total - true_length_2d) > 1:  # Allow 1m difference for rounding
        print(f"Warning: Distance mismatch!")
        print(f"  True GPX length: {true_length_2d:.1f} m")
        print(f"  Calculated total: {calculated_total:.1f} m")
        print(f"  Difference: {abs(true_length_2d - calculated_total):.1f} m")
    
    return {
        'filename': os.path.basename(gpx_file),
        'total_distance': true_length_2d,  # Use 2D length
        'curve_distance': curve_distance,
        'straight_distance': straight_distance,
        'curve_percentage': (curve_distance / true_length_2d * 100) if true_length_2d > 0 else 0,
        'total_elevation_gain': sum(max(0, s['elevation_change']) for s in segments),
        'total_elevation_loss': abs(sum(min(0, s['elevation_change']) for s in segments)),
        'segments': segments,
        'curve_count': len(curve_segments),
        'straight_count': len(straight_segments)
    }
    
def generate_report(analysis_results, output_file):
    with open(output_file, 'w') as f:
        f.write("# Track Analysis Report (Curve-based)\n\n")
        
        # Add curve detection parameters info
        f.write("## Analysis Parameters\n")
        f.write("Curve detection is based on the following parameters:\n")
        f.write("- Look-ahead distance: 40 meters\n")
        f.write("- Curve angle threshold: 120 degrees\n")
        f.write("- A curve is detected when the cumulative angle within the look-ahead distance exceeds the threshold\n")
        f.write("- Angles are only accumulated when consecutive turns are in the same direction\n\n")
        f.write("---\n\n")
        
        for result in analysis_results:
            f.write(f"## {result['filename']}\n\n")
            f.write("### Overall Statistics\n\n")
            f.write(f"- Total Distance: {result['total_distance']:.1f} m\n")
            f.write(f"- Distance in Curves: {result['curve_distance']:.1f} m ({result['curve_percentage']:.1f}%)\n")
            f.write(f"- Distance in Straight Sections: {result['straight_distance']:.1f} m\n")
            f.write(f"- Number of Curves: {result['curve_count']}\n")
            f.write(f"- Number of Straight Sections: {result['straight_count']}\n")
            f.write(f"- Check Sum of Straight + Curve Sections: {result['straight_distance'] + result['curve_distance']:.1f} m\n")
            f.write(f"- Total Elevation Gain: {result['total_elevation_gain']:.1f} m\n")
            f.write(f"- Total Elevation Loss: {result['total_elevation_loss']:.1f} m\n")
            f.write("### Segment Analysis\n\n")
            f.write("| Type | Direction | Start Distance (m) | End Distance (m) | Length (m) | Elevation Change (m) | Gradient (%) | Curve Angle (°) |\n")
            f.write("|------|-----------|-------------------|-----------------|------------|-------------------|-------------|----------------|\n")
            
            cumulative_distance = 0
            for segment in result['segments']:
                direction = segment.get('turn_direction', 'N/A')
                curve_angle = f"{segment.get('cumulative_angle', 0):.1f}" if segment['type'] == 'curve' else 'N/A'
                segment_length = segment['distance']
                
                f.write(
                    f"| {segment['type']} | {direction} | "
                    f"{cumulative_distance:.1f} | {(cumulative_distance + segment_length):.1f} | "
                    f"{segment_length:.1f} | {segment['elevation_change']:.1f} | "
                    f"{segment['gradient']:.1f} | {curve_angle} |\n"
                )
                
                cumulative_distance += segment_length
            
            f.write("\n---\n\n")

def export_segments_to_gpx(segments, original_filename):
    # Create output directory
    base_name = os.path.splitext(original_filename)[0]
    output_dir = f"{base_name}_segments"
    os.makedirs(output_dir, exist_ok=True)
    
    # Create GPX files for curves and straights
    curves_gpx = gpxpy.gpx.GPX()
    straights_gpx = gpxpy.gpx.GPX()
    
    # Add points to respective GPX files
    for segment in segments:
        for i, point in enumerate(segment['points']):
            gpx_point = gpxpy.gpx.GPXWaypoint(
                latitude=point['latitude'],
                longitude=point['longitude'],
                elevation=point['elevation'],
                name=f"{segment['type']}_{i+1}"
            )
            if segment['type'] == 'curve':
                curves_gpx.waypoints.append(gpx_point)
            else:
                straights_gpx.waypoints.append(gpx_point)
    
    # Save combined GPX files
    with open(os.path.join(output_dir, "curves.gpx"), 'w') as f:
        f.write(curves_gpx.to_xml())
    
    with open(os.path.join(output_dir, "straights.gpx"), 'w') as f:
        f.write(straights_gpx.to_xml())
    
    print(f"Exported combined segments to {output_dir}/")

def main():
    # Process all GPX files in current directory
    gpx_files = [f for f in os.listdir('.') if f.endswith('.gpx')]
    
    if not gpx_files:
        print("No GPX files found in current directory")
        return
    
    analysis_results = []
    for gpx_file in gpx_files:
        print(f"\n\nProcessing {gpx_file}...\n")
        result = analyze_gpx(gpx_file)
        analysis_results.append(result)
        # Export segments to separate GPX files
        export_segments_to_gpx(result['segments'], gpx_file)
    
    # Generate report
    generate_report(analysis_results, 'track_analysis_curves_report.md')
    print("Analysis complete. Results written to track_analysis_curves_report.md")

if __name__ == "__main__":
    main()      

QGIS3: Aggregate Over Layer with Expression Builder Using a Filter

array_sum(
	array_foreach(
		generate_series(0, layer_property('MY_LAYER', 'feature_count'), 1),
		if(attribute(get_feature_by_id('MY_LAYER', @element), 'MY_FIELD_TO_FILTER') = 'MY_FILTER_VALUE',		
			attribute(get_feature_by_id('MY_LAYER', @element), 'MY_FIELD_TO_AGGREGATE'),
			0
		)
	)
)

is the same as

aggregate(
	layer:='MY_LAYER',
	aggregate:='sum', 
	expression:="MY_FIELD_TO_AGGREGATE",
	filter:="MY_FIELD_TO_FILTER"='MY_FILTER_VALUE'
)

Strava Heatmap in QGIS - working TMS 2024

TMS Source:

https://heatmap-external-a.strava.com/tiles-auth/ride/hot/{z}/{x}/{y}.png?Key-Pair-Id=LONGSTRING&Signature=LONGERSTRING

Get credentials with Chrome Plugin JOSM Strava Heatmap:

Workflow for Replacing DOM With DEM Values in a Zone Specified by Vector Layer

1. Create polygon file with same CRS as DOM and DEM and draw polygon mask (obv. polygons with holes don't work!) 

2. Use the "Rasterize (overwrite with fixed value)" algorithm, using the polygon mask as vector layer and the DOM as raster layer (set the DOM as reference layer!). Set the burn value to -9999. BEAWARE: The original file will be overwritten - if you don't want to alter the original file, make a copy of the DOM first!

3. Use the raster calculator to rewrite the values in the DEM with values from the DOM by using this formula: 

(DOM@1 = -9999) * DGM@1 + (DOM@1 != -9999) * DOM@1

Windows Batch Script for Zipping Shapefile Components

Windows Batch File to zip ESRI shapefile-components and delete original files. Put the code to a text file and save with .bat extension. Save the file to the folder you want to run the commands.

@ECHO OFF
set /p $dum="Hit enter to zip Shapefiles in %~dp0 ..."
FOR %%F IN (*.shp) DO "C:\Program Files\7-Zip\7zG.exe" a "%%~nF.zip" "%%~nF.shp" "%%~nF.dbf" "%%~nF.prj" "%%~nF.shx"
for /f "delims=" %%F in ('dir /b /a-d ^| findstr /vile ".bat .zip"') do Echo "%%F"
set /p $dum="Hit enter to delete original files, listed above.."
for /f "delims=" %%F in ('dir /b /a-d ^| findstr /vile ".bat .zip"') do del "%%F"
Echo Done !!
set /p $dum="Hit [Enter] to exit..."

QGIS3 Virtual Layer & SQL: Calculation of Intersection Areas from (Segmented) Line Buffers and Polygon

Usecase: For a set of lines in one layer you would like to know the areas of (segmented) line buffers, that intersect with polygons from another layer. I.e., (segmented = with different buffer width segments) trail buffers, that intersect with wood patches. 

An inner buffer segment of, say 10 meters (5 meters to each side of the middle line) and an oute buffer segment, inbetween 5 and 10 meters offset from the middle line. 

 In QGIS Virtual Layers SQL you can not call Buffer function with specification of the end ("square", "flat", "round") and Buffer() will make round ends by default - so you need the workaround with ST_union() of single sided buffers to get the correct flat ends. 

Then, for the final calculations, all the feature's buffer polygons were merged into one polygon with outer ST_Union() call. 

The ST_difference() call is used to make the outer buffer segment by clipping the narrower ("Breite_Forst") from the wider buffer ("Breite_Forst"+"Breite_F_bef"). 

The final output will be to poylgons, one with the inner, narrower buffer, and one with the outer wider buffer, and the calculated areas for each polygon, and merged into one table with the UNION ALL statement.

SELECT 
	T.geom as geometry,
	ST_area(T.geom) AS Fl_Rod
FROM (
	SELECT
		ST_union(
			ST_union( 
			  intersection(st_SingleSidedBuffer(t.geometry,t.Breite_Forst/2,0), w.geometry),
			  intersection(st_SingleSidedBuffer(t.geometry,t.Breite_Forst/2,1), w.geometry)
			) 
		) AS geom
	FROM trails AS t, wood AS w
	UNION ALL 
	SELECT 
		ST_difference(
			ST_union(
				ST_union( 
				  intersection(st_SingleSidedBuffer(t.geometry,t.Breite_Forst/2+Breite_F_bef,0), w.geometry),
				  intersection(st_SingleSidedBuffer(t.geometry,t.Breite_Forst/2+Breite_F_bef,1), w.geometry)
				) 
			),
			ST_union(
				ST_union( 
				  intersection(st_SingleSidedBuffer(t.geometry,t.Breite_Forst/2,0), w.geometry),
				  intersection(st_SingleSidedBuffer(t.geometry,t.Breite_Forst/2,1), w.geometry)
				) 
			)
		) AS geom 
	FROM trails AS t, wood AS w    
) as T

QGIS3: Elevation Gain for LinestringZ Geometry

Use this Expression in the QGIS3 Field Calculator to get the eöevation gain for a LinestringZ geometry!

array_sum(
	array_filter(
		array_foreach(generate_series(1,num_geometries(nodes_to_points($geometry))),
			z(geometry_n( nodes_to_points($geometry), @element+1))-
			z(geometry_n( nodes_to_points($geometry), @element))
		), @element>0
	)
)

QGIS3: Neat trick to convert color name into color rgb string

color_mix_rgb('blue', 'white', 0)

Arguments to the function are color1: a color string, color2: a color string, ratio: the ratio at which the two colors will be mixed. The output is:

'0,0,255,255'

The usecase is converting a color name, stored in a field (field name is "color_name_code"), to a color string to be used for symbolizing layer features, like so:

color_mix_rgb("color_name_code", 'white', 0.2)

which will give me a bit of a pastel tone, by mixing white into the given "color_name_code" (blue, red, green, etc.) with a ratio of 0.2

QGIS3: Find Minimum/Maximum/Mean Raster Value (Elevation) for Polygon Overlay

For finding raster value statistics (Min, Max, Mean) for a polygon overlay, I apply this expression in a virtual field by using the field calculator. The raster layer that I'm querying is named

'DGM_merged'

The functions is looking for the min. raster value at the polygon's nodes

array_min(
	array_foreach(
		generate_series(1, num_points($geometry), 1),
		raster_value('DGM_merged', 1, 
			point_n(nodes_to_points($geometry), @element)
		)
	)
)

QGIS3: Virtual Layers & Spatialite/SQLite/SQL Queries - Intersecting Lines With Polygons

SQL and Spatialite make super effective table and geometry operations possible. With the implemantation of Virtual Layers, QGIS has now a built in functionality to run such queries very easily and without the need for preparation of databases. The below SQL snippet, i.e., can be used to intersect features of a line and a polygon layer, and calculate the summed segment lengths for each single polygon. The usecase here was, that I needed to know the cummulated lengths of trail crossing single parcels.

SELECT 
  t.Name AS Trail_Name,
  g.GNR AS Parcel_NR,
  SUM(LENGTH(INTERSECTION(t.geometry, g.geometry))) AS SUM_L_inP,
  COLLECT(INTERSECTION(t.geometry, g.geometry)) as geom
FROM Trails AS t JOIN Parcels AS g ON INTERSECTS(t.geometry, g.geometry)
GROUP BY g.GNR

QGIS REGEX Expression to Find Last Vaue of an XML/Html Image Source of the Description Field of a KML File

if(
regexp_substr(description, 'img src="!?.*img src="(.*)"') = '',
regexp_substr(description, 'img src="(.*)"'), 
regexp_substr(description, 'img src="!?.*img src="(.*)"') 
)

GDAL: Bash Script for Converting All GPKG-Files From Directory To GPX-Format

Since lately QGIS (QGIS 3.12) comes equipped with a nice tool ("Split Vector Layers"), that will split your layers based on an attribute and ame your files accordingly. Th only drawback is, that you can not choose different output format - so you'll need to go with the only possibility, namely the GPKG-format. Now, I rather needed GPX files and had to find a way of converting the gpkg-files in batch mode. This I achieved by writing a small bash script looping over the files with GDAL's ogr2ogr command, and convert each file to GPX.

#!/bin/bash
## first two lines and last line prevent whitspace problems in filenames (see: https://unix.stackexchange.com/questions/9496/looping-through-files-with-spaces-in-the-names
## then loop over all files with gpkg extension an convert to gpx format preserving the old filename!
## the script would throw an error and discard attribute fields for all fieldnames that do not match the GPX XML definition (name, cmt, etc…).
## if you dont put the GPX_USE_EXTENSION=YES paramter..
## with <-t_srs epsg:4326=""> the target CRS, to which the input coordinates will be transformed, is given..

OIFS="$IFS"
IFS=$'\n'
for f in *.gpkg
	do ogr2ogr -f GPX -dsco GPX_USE_EXTENSIONS=YES -t_srs EPSG:4326 -overwrite ${f%.*}.gpx $f
done
IFS="$OIFS"
read -p "Hit [Enter] to exit..."

QGIS 3: Symbology with Geometry Generator - Draw One Convex Hull For All Features With Same Attribute

I have a layer named "Trails" with an attribute "Trail ID", which contains unique, consecutive Feature/Trail IDs and an attribute "Schwierigkeit" (trail difficulty). For all features with the value "black" for the attribute "Schwierigkeit" I want to render one convex hull. The below code will select the last element of all the features in the layer and apply the code for drawing the polygon only once. The first

array_foreach()

in the code will create an array of all features (series generated from 1 to feature count number). Over this array, the second array_foreach() will apply the geometry function on each element that meets the condition of the

array_filter()

function. The

collect_geometries()

function finally puts all those single geometries within the resulting array into one multiline geometry, for which I then draw the hull. The purpose of this procedure, is to check if the trails in my dataset show a spatial aggregation according to their trail difficulty..

if($id = maximum($id),
convex_hull(
collect_geometries(
with_variable('my_arr', 
array_foreach(
generate_series(1,  layer_property( 'trails', 'feature_count'), 1),
get_feature('trails', 'Trail ID', @element)
),
array_foreach(
array_filter(@my_arr, attribute(@element, 'Schwierigkeit')='black') , geometry(@element)))))
, NULL)

QGIS 3: Geometry Generator Expression for Points at Line Intersections

A solution for symbolizing line intersections between the current layer's features and another layer's features ('Tracks Subset=Detail'). The

generate_series

is used with an

aggregate

which yields the number if features in the other layer used for the intersection. The

array_foreach

iterates over all features of the other layer and intersects each line with the input layer's current feature. The

array_filter

is needed to filter out the epmty geometries that could result after the intersection. If you wouldn't do this, the code would break when calling

collect_geometries

, which is needed to finally convert the array of geometries into a valid, digestible geometry collection, fed into the geometry generator with checking Point/Multi-POint as geometry type..

with_variable ('my_series', generate_series(1, aggregate(layer:='Tracks Subset=Detail', aggregate:='max', expression:="id")),
	collect_geometries(
		array_filter(
		array_foreach(@my_series, if(is_empty_or_null( 
			intersection(geometry(get_feature('Tracks Subset=Detail', 'id', @element)),
			$geometry)), 'x', intersection(geometry(get_feature('Tracks Subset=Detail', 'id', @element)),
			$geometry))
			), 
		@element != 'x'
	)
  )
)

QGIS3: Aggregate Data of Intersecting Features: Concatenate String Attributes of Polygons Crossed by Line Features

regexp_replace(aggregate(layer:='Prcl_Lyr', aggregate:='concatenate', expression:="Prp_Ownr",filter:=intersects( $geometry, geometry(@parent)), concatenator:='/'), '[/]{2,}', '/')

Polygon Layer "Parcell_Lyr" Field holding Owner Data "Prop_Ownr" The parent layer is the line layer with the lines, for which we want to collect the polygon data, intersected by the single line features. For aggregated NULL or empty strings the concatenator character would be replicated without strings inbetween - for this, the regex "[/]{2,}" which matches a forward slash, repeated twice or more, will replace those doubled slashes with a single one.

QGIS 3: Layout expression to get all features of a layer within the current map view

In a map layout template you can insert an expression which gets all feature's names of a certain layer, which are contained in the current layout's view/extent. For wxample you could concatenate all feature's names contained in the current map view like so:

aggregate(layer:='Corridors_86ef2ea9_d1a1_4d9f_9735_7b7b2fb54cb2', 
aggregate:='concatenate', 
expression:="Name", filter:=within($geometry, map_get( item_variables('Main Map'), 
'map_extent')), concatenator:=', ')

Aggregation of Different Layers in QGIS 3.x with Field Calculator Expression Alone!!

Here's an example of how to "spatially aggregate" Polygon attribute values over features of a point layer by intersecting the two layers. Go in "Edit mode" with the target layer and check "Virtual Field" with type "decimal". Then use this in expression builder, where "layer" is the point layer, and aggregate the attribute values that intersect the target layer's geometry. With "Expression" you can define the attribute, that should aggregated!

aggregate(layer:='BIKFFH_Karwendel BIKFFH_PL', aggregate:='sum', expression:="LNUMMER", 
filter:=intersects($geometry,geometry(@parent))) 

Return Excel Row Indices of non-empty Cells in Column

Column with Data in A1:A20

Formula in Cell B1

=WENNFEHLER(AGGREGAT(15;6;ZEILE($A$1:$A$20)*N(LÄNGE($A$1:$A$20)>0);SUMMENPRODUKT(N(LÄNGE(Datenablage!$A$1:$A$20)=0))+ZEILE());"")

SUMMENPRODUKT(N(LÄNGE(Datenablage!$A$1:$A$20)=0)) is needed because each LEN=0 will be a smallest value, so if you have 3 cells with LEN=0 the ksmallest with k=4 will be the first non empty value..

..for details on aggregate check: https://www.youtube.com/watch?v=He3dblboncw

Excel VBA User Defined Function for Transformation of Braun-Blanquet Values to Precentages of Vegetation Cover

Function Transf_BraunBlanquet(ByVal BB_Str As String) As String

'Transformation of Braun-Blanquet 'Artmächtigkeit' to percentage cover (similar to usage in TurboVeg or twinspan)
'The key value mapping can be altered depending on specific requirements
'This UDF is used in the UDF SumKum_BraunBlanquet(), which will apply the Transformation on a range of values and
'will sum the transformed percentages. This cumulative sum can be used to check if the Braun-Blanquet estimation for
'a vegetation layer is reasonable.

    With CreateObject("Scripting.Dictionary")
        '~~> first transfer your list in Dictionary
        .Add "r", "0"
        .Add "+", "0"
        .Add "1", "1"
        .Add "2m", "2"
        .Add "2a", "10"
        .Add "2b", "20"
        .Add "3", "37,5"
        .Add "4", "67,5"
        .Add "5", "87,5"
        
        If Len(BB_Str) = 0 Then
        '~~> case: empty cell
            Transf_BraunBlanquet = 0
            Exit Function
        End If
        
        For Each elem In .keys
            key = elem
            If key = BB_Str Then
                Transf_BraunBlanquet = .Item(elem) * 1
                Exit Function
            End If
        Next elem
        
    End With
    
End Function


Function SumKum_BraunBlanquet(Rng As Range) As Double
'See comments on Transf_BraunBlanquet() for explanations

    Dim Sum As Double
    Dim RngArr As Variant
    
    RngArr = Application.Transpose(Rng) 'dumps range values to array
    
    For Each elem In RngArr
        Sum = Sum + Transf_BraunBlanquet(elem)
    Next elem
    
    SumKum_BraunBlanquet = Sum
    
End Function

VBA Macro to Export Data from Excel Spreadsheet to CSV

Resources: http://stackoverflow.com/questions/13496686/how-to-save-semi-colon-delimited-csv-file-using-vba
and: http://stackoverflow.com/questions/35655426/excel-vba-finding-recording-user-selection

Sub Export_CSV()

    '***************************************************************************************
    'author:    kay cichini
    'date:      26102014
    'update:    16122016
    'purpose:   export current spreadsheet to csv.file to the same file path as source file
    '
    ' !!NOTE!!  files with same name and path will be overwritten
    '***************************************************************************************
  
    Dim MyPath As String
    Dim MyFileName As String
    Dim WB1 As Workbook, WB2 As Workbook
    
    Set WB1 = ActiveWorkbook

    '(1) either used range in active sheet..
    'ActiveWorkbook.ActiveSheet.UsedRange.Copy
    
    '(2) or alternatively, user selected input range:
    Dim rng As Range
    Set rng = Application.InputBox("select cell range with changes", "Cells to be copied", Default:="Select Cell Range", Type:=8)
    Application.ScreenUpdating = False
    rng.Copy

    Set WB2 = Application.Workbooks.Add(1)
    WB2.Sheets(1).Range("A1").PasteSpecial xlPasteValues
    
    MyFileName = "CSV_Export_" & Format(Date, "ddmmyyyy")
    FullPath = WB1.Path & "\" & MyFileName
    
    Application.DisplayAlerts = False
    If MsgBox("Data copied to " & WB1.Path & "\" & MyFileName & vbCrLf & _
    "Warning: Files in directory with same name will be overwritten!!", vbQuestion + vbYesNo) <> vbYes Then
        Exit Sub
    End If
    
    If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
    With WB2
        .SaveAs Filename:=FullPath, FileFormat:=xlCSV, CreateBackup:=False
        .Close False
    End With
    Application.DisplayAlerts = True
End Sub