Jump to content

How to Link Two Tables in Spotfire Based on Spatial Location Coordinates Without a Common Key?


KaDee79

Recommended Posts

I'm working on a project in Spotfire where I need to join two tables based on spatial proximity rather than a common key. Here's a detailed breakdown of my use case:

I have two tables, each containing data about various shapes and their locations. However, there is no common key between the tables that I can use to directly join them. Instead, both tables contain information about location boundaries, represented as X- and Y-coordinates. Each shape has a defined range with minimum and maximum values for both X and Y.

Here's what I need to achieve:

  • I want to link or associate data from one table to data in the other table whenever a shape from Table A falls within the spatial range of a shape in Table B.
  • Specifically, I want to check if the coordinates (X_min, X_max, Y_min, Y_max) in one table overlap or fall within the coordinate range of the other table.

I understand that Spotfire doesn't directly support this type of "between-tables" calculation for linking data. I'm wondering if there’s an efficient workaround, like custom expressions, data functions, or scripting, that could help perform this spatial join.

Any insights, workarounds, or examples would be greatly appreciated!

Thank you in advance for your help!

Link to comment
Share on other sites

Depending on the size of your tables a "dirty" or heavy way could be to full outer join a copy of both tables
(based on my experience with Spotfire 11.4 Client you should not have more than 5.000.000- 10.000.000 rows after full outer join, which could slow down the perfomance - but give it a try 👍)

Now you can calculate for each combination you're desired insights like overlapping or not. 

After that you should filter down this big table and work with this informations e.g. as a mapping table to connect both tables somewhere else in your data model / data canvas. 

Link to comment
Share on other sites

I'm not sure the exact script that you'd have to write, but I imagine this would be entirely possible using a Python Data Function. I think the general process would be: 

  1. Load both tables as input parameters
  2. Using GeoPandas, obtain overlapping geospatial shapes using the Identity overlay (this should return a table of boolean value with columns for each table)
  3. Output table of overlapping shapes
  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...