Placing points into bounding boxes based off of X and Y values from a lookup table

ccwingfi

I have two jmp data tables that I need to be able to evaluate against each other without joining them (they are both large tables) in JSL.

The first is a table that consists of different items that are assigned X and Y coordinates.

The second table is a master reference table which contains the XY coordinates only for the lower-left corner and Upper-Right Corner of the bounding box/partition that the points can sit inside of (within the same coordinate system). I need to be able to search the reference table using the generated table of XY coordinates to determine which bounding box each item sits inside of.

I am able to do a Cartesian join of the two data tables and filter with a (xmin_ref < x_item < xRef_max) style column for both tables, but the table sizes generated are too large and not practical.

Is there a function/method that allows me to do what I need without doing a Cartesian join?

Thanks!

pmroz

Here's a brute force attempt.  Performance was instant for 1000 xy rows, 17 seconds for 100,000 xy rows, and 159 seconds for 1 million xy rows.  Might get a speedup using the "in polygon" function.

// Small table of XY values

xydt = New Table( "xy data", Add Rows( 10 ),

      New Column( "x", Numeric, Continuous, Format( "Best", 12 ),

            Set Values(

                  [0.206704897573218, 0.188453091308474, 0.402824054239318,

                  0.946105436887592, 0.209207338048145, 0.783533588517457,

                  0.15626378194429, 0.293116413988173, 0.0382238682359457,

                  0.0646856701932847] ) ),

      New Column( "y", Numeric, Continuous, Format( "Best", 12 ),

            Set Values(

                  [0.258698139106855, 0.395224047359079, 0.0813549624290317,

                  0.158546321559697, 0.874619059031829, 0.787837725598365,

                  0.173847715603188, 0.736063659423962, 0.782933217706159,

                  0.28023144742474] ) ),

      New Column( "Box", Character, Nominal )

);

// Table of lower left and upper right coordinates

refdt = New Table( "Box Reference", Add Rows( 26 ),

      New Column( "xll", Numeric, Continuous, Format( "Best", 12 ),

            Set Values(

                  [0, 0, 0, 0, 0, 0.2, 0.2, 0.2, 0.2, 0.2, 0.4, 0.4, 0.4, 0.4, 0.4, 0.4,

                  0.6, 0.6, 0.6, 0.8, 0.8, 0.8, 0.9, 0.9, 0.9, 0.9] ) ),

      New Column( "yll", Numeric, Continuous, Format( "Best", 12 ),

            Set Values(

                  [0, 0.2, 0.4, 0.6, 0.8, 0.1, 0.3, 0.5, 0.7, 0.9, 0.1, 0.3, 0.5, 0.7, 0.8,

                  0.9, 0.2, 0.4, 0.6, 0.2, 0.4, 0.6, 0.3, 0.5, 0.7, 0.9] ) ),

      New Column( "xur", Numeric, Continuous, Format( "Best", 12 ),

            Set Values(

                  [0.2, 0.2, 0.2, 0.2, 0.2, 0.4, 0.4, 0.4, 0.4, 0.4, 0.6, 0.6, 0.6, 0.6,

                  0.6, 0.6, 0.8, 0.8, 0.8, 1, 1, 1, 1, 1, 1, 1] ) ),

      New Column( "yur", Numeric, Continuous, Format( "Best", 12 ),

            Set Values(

                  [0.2, 0.4, 0.6, 0.8, 1, 0.3, 0.5, 0.7, 0.9, 1, 0.2, 0.4, 0.6, 0.8, 0.9,

                  1, 0.4, 0.6, 0.8, 0.4, 0.6, 0.8, 0.5, 0.7, 0.9, 1] ) ),

      New Column( "Box", Character, Nominal,

            Set Values(

                  {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N",

                  "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"} ) )

);

start_dt = today();

for (i = 1, i <= nrows(xydt), i++,

      xval = xydt:x[i];

      yval = xydt:y[i];

      ref_list = refdt << get rows where(:xll <= xval & :yll <= yval &

                                                            xval < :xur & yval < :yur);

// See if we found a match

      if (nrows(ref_list) > 0,

            match_row = ref_list[1];      // take the first one; should only be one!

            xydt:box[i] = refdt:box[match_row];

      );

);

end_dt = today();

elapsed = end_dt - start_dt;

print(elapsed);