Spatiotemporal join

Spatiotemporal Join transfers fields from one DataFrame to another based on spatial, temporal, and field attribute relationships, or some combination of the three. Optionally, statistics can be calculated for the joined records.

Usage notes

• A spatial join matches records from the input DataFrames (target_dataframe and join_dataframe) based on their spatial relationships. A temporal join matches records from the target DataFrames based on their temporal relationships. An attribute join matches records based on field values.

• You can join records based on a spatial relationship, a temporal relationship, an attribute relationship, or a combination of the three.

• If the target DataFrame and join DataFrame are in different coordinate systems, the coordinate system of the target DataFrame will be used.

• When 'NearPlanar' is specified with setSpatialRelationship(), it is required that the target DataFrame's geometry has a projected coordinate system or the tool will fail. You can transform your data to a projected coordinate system by using ST_Transform.

  Learn more about coordinate systems and transformations

• If multiple join records match the same target record, you can decide whether all the matching records will be joined (setJoinOneToMany()) or all the matching records will be summarized together (setJoinOneToOne()) as follows:

  • One-to-many—Joins all the matching records in the join DataFrame to the target DataFrame. The resulting DataFrame will contain multiple records of the target record. If True is specified for setLeftJoin(), all input records are written to the output DataFrame.

  • One-to-one—Summarizes all of the matching join records with each record in the target DataFrame. Only the records that have a match will be included in the summary and output. The count of joined records will be added, in addition to other standard statistics such as sum, minimum, maximum, range, mean, variance, standard deviation, first, and last, as well as string statistics including count, any, first, and last. By default, only those join records that have the specified relationships will be maintained in the output DataFrame (inner join). If True is specified for setLeftJoin(), all input records are written to the output DataFrame.

  • To add first or last statistic field in the output, time needs to be enabled on the input DataFrames.

    Learn more about enable time on a DataFrame

• Examples of a one-to-many and one-to-one attribute join are shown below. In this example, the one-to-one join only includes the count; additional statistics that can be calculated using addSummaryField().

• You can optionally build an expression to join records using setJoinCondition(). This should always be used with a spatial, temporal, or field attribute relationship, or some combination of the three. If you specify an expression, only records that meet the condition will be used. For example, using the expression $target['Magnitude'] > $join['Explosion'], you only join target records if the Magnitude field is greater than the Explosion field in the join record. See Arcade expressions for more information.

• You can optionally return the the spatial distance and temporal difference of the joined records by setting the includeDistance() parameter. Spatial distance calculations are returned in a field named distance, a double column. The distance method used is determined by the spatial_relationship parameter. For example, if you specify 'NearGeodesic', the geodesic distance method is applied when calculating the distance. Similarly, temporal difference calculations are returned in a field named timegap, represented as milliseconds in a long column. The output fields and values are outlined below:

  • The distance field can have the following values:

    • 0—This represents the target and join geometries intersect
    • Greater than 0—This represents the distance between the join and target records geometries. The default distance unit is meters.
    • Null values—This represents the case where geometries are further apart than the near_distance parameter in the setSpatialRelationship() setter.

  • The time_gap field can have the following values:

    • Less than 0—This represents the time difference between the target and join records. Negative values mean the join record occurs before the target record.
    • 0—This represents times that intersect.
    • Greater than 0—This represents the time difference between the target and join records. Positive values mean the target records occurs before the join record.
    • Null values—This represents the case where records did not mean the join condition. For example, they are further apart than the near_duration in the setTemporalRelationship() setter, or the ordering of the target and join record did not match given relationship, such as NearBefore.

Results

The fields in the output DataFrame differ depending on the join operation.

One-to-many join

• The following fields are included in the output DataFrame when running a one-to-many join:

  • All fields from the target DataFrame
  • All fields from the joined DataFrame

If the same non-geometry field name exists in the target and join DataFrame, the joined field will start with the word join (for example join_fieldname).

If the same geometry field name exists in the target and join DataFrame, the result will have multiple geometry fields with the same name, where the first geometry field in the result schema is from the target DataFrame and the second geometry field is from the joined DataFrame. You can rename the geometry fields of your input DataFrames to be unique before joining to avoid confusion.

If you set the includeDistance() parameter, your output will have fields named distance and time_gap representing the spatial distance and temporal difference between the joined features.

One-to-one join

In addition to all fields from the target DataFrame, the following fields are included in the output records when running a one-to-one join:

Performance notes

Improve the performance of Spatiotemporal Join by doing one or more of the following:

• Only analyze the records in your area of interest. You can pick the records of interest by using one of the following SQL functions:

  • ST_Intersection—Clip to an area of interest represented by a polygon. This will modify your input records.
  • ST_BboxIntersects—Select records that intersect an envelope.
  • ST_EnvIntersects—Select records having an evelope that intersects the envelope of another geometry.
  • ST_Intersects—Select records that intersect another dataset or area of intersect represented by a polygon.
• Make the join conditions as selective as possible. For example, a near join of 1 kilometer will perform better than a near join of 5 kilometers on the same DataFrame.
• Add more than one join condition. Additional join conditions make the join more selective. For example, a near join of 1 kilometer and 1 hour will perform better than a near join of 1 kilometer on the same data.
• Apply a join expression.
• Calculating the distance and time_gap fields may increase processing time.
• Planar distance calculation (specified with the 'NearPlanar' option in setSpatialRelationship) will perform better than geodesic.
  • If the data is in geographic coordinate system but for small, local areas, project it to a local coordinate system, and specify 'NearPlanar' in the spatial_relationship parameter. You can transform your data to a projected coordinate system by using ST_Transform.

Similar capabilities

• Aggregate Points
• Summarize Within

Syntax

For more details, go to the GeoAnalytics for Microsoft Fabric API reference for spatiotemporal join.

Examples

Run Spatiotemporal Join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# Imports
from geoanalytics_fabric.tools import ReconstructTracks
from geoanalytics_fabric.tools import SpatiotemporalJoin
from geoanalytics_fabric.sql import functions as ST

# Path to the hurricane track points data and USA counties data
hurricanes_data_path = r"https://services2.arcgis.com/FiaPA4ga0iQKduv3/arcgis/rest/" \
            "services/IBTrACS_ALL_list_v04r00_points_1/FeatureServer/0"
counties_data_path = "https://services.arcgis.com/P3ePLMYs2RVChkJx/ArcGIS/rest/services/"\
            "USA_Counties_Generalized/FeatureServer/0"

# Create DataFrames for hurricane track points and USA counties
hurricanes_df = spark.read.format("feature-service").load(hurricanes_data_path)
hurricanes_df = hurricanes_df.withColumn("usa",
                                         ST.bbox_intersects("shape",
                                         xmin=-14165303, xmax=-7384568,
                                         ymin=1947768, ymax=7426938)).where("usa=true")
counties_df = spark.read.format("feature-service").load(counties_data_path)

# Reconstruct hurricane points into tracks using Reconstruct Tracks
rt_result = ReconstructTracks() \
            .setTrackFields("NAME") \
            .setDistanceMethod(distance_method="Geodesic") \
            .run(dataframe=hurricanes_df)

# Use Spatiotemporal Join to join the hurricane tracks to each county
join_result = SpatiotemporalJoin() \
            .setLeftJoin(left_join=True) \
            .setJoinOneToOne()\
            .setSpatialRelationship(spatial_relationship="Intersects") \
            .run(target_dataframe=counties_df, join_dataframe=rt_result)

Plot results

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Plot the join result and visualize the frequency of hurricanes for each county
result_plot = join_result.st.plot(cmap_values="COUNT",
                                  cmap="coolwarm",
                                  legend=True,
                                  figsize=(14,8),
                                  geometry="shape",
                                  basemap="light")
result_plot.set_title("USA counties visualized by count of intersecting hurricanes")
result_plot.set_xlabel("X (Meters)")
result_plot.set_ylabel("Y (Meters)")
result_plot.set_xlim(left=-14200000, right=-7300000)
result_plot.set_ylim(bottom=2100000, top=7200000);

Version table

See also

• Visualizing results
• Spatial relationships
• Temporal relationships
• Enabling time on a DataFrame
• Using Arcade expressions