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

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...

Datasets

Regex

Python

Parse

Macros

Spatial Analysis

Preparation

Iterative Macro

Accepted answers

fmvizcaino

Hi @TR ,

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

Best,

Fernando Vizcaino

2020-07-09 Iterative approach to create points from multiple X,Y coordinate columns.yxmd

All comments

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

2020-07-09 Iterative approach to create points from multiple X,Y coordinate columns.yxmd

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

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167