Iterative approach to create points from multiple X,Y coordinate columns

[TR]

Hey Friends,

Feeling a bit rusty here and looking for suggestions on how to tackle this logic.

I have a table with the following columns (item_01 - item_25):

RecordID

item_01_lat

item_01_lon

item_01_name

item_01_type

item_02_lat

item_02_lon

item_02_name

item_02_type

item_02_other

My intent is to create the following (removing the unnecessary x,y columns and creating points via [ST_CreatePoint(x,y)]:

RecordID

item_01_pt

item_01_name

item_01_type

item_02_pt

item_02_name

item_02_type

item_02_other

The consistent table schema includes column names item_xx_lat and item_xx_lon (where x = 01 to 25). I believe the best approach would be to create an iterative macro but not quite sure how to account for the changing column names and inconsistent column counts for each item_xx_.

Thank you in advance as I knock the dust off... 

[cmcclellan]

The inconsistency of fields might be a problem, for ease I would suggest 

Input File > Formula (25 expressions, 1 for each point you want to create) > Select tool to drop the lat, long and other columns that you don't need.

Because it's really only 1 formula tool, I think it would be more work to get the macro setup and working.

[fmvizcaino]

Hi @TR ,

Here is an example showing how to automatically do it. The idea was to create a row for each lat, lon to create the spatial point and, from there, to recreate your dataset once again.

Best,

Fernando Vizcaino

[TR]

Hi @fmvizcaino,

That is sooo close, thank you! In your final Browse you create a 'item_01_other' which is not an existing column from the initial Text Input. When I run 50+ columns with the logic I create improperly named columns.

After Transpose(10) and before/during where you recreate the headers in Formula(11) may be the spot to tweak? Maybe removing any Null records in the Value column?

Thanks,

Todd

[fmvizcaino · Accepted solution]

Hi @TR ,

That is correct! I altered the workflow and inserted that additional condition.

Best,

Fernando Vizcaino

[TR]

Thanks. Just a couple more steps necessary to complete this:

Next, we have to add a Union after the Join(18) and include the records from the output of the right join. Resort on the Select(19) tool and it works perfectly.

I'm inputting 50 locations with/without x,y,z coordinates with varying attributes for each location. The workflow, with the current test data gets me to a point where I have to go back and create more spatial data with attributes to test further. Thank you for your help, especially with the Transform and Transpose. For years they seemed to be my kryptonite, hahaha.

Have a great weekend!

Todd