The SchemaMapper transformer will do exactly this, except... it doesn't work with ranges. So you would have to map each value individually.
Otherwise, it's the AttributeRangeMapper, rather than the AttributeValueMapper, that you'd want (though as you note it can't point to the Excel file dynamically).
The SchemaMapper transformer will do exactly this, except... it doesn't work with ranges. So you would have to map each value individually.
Otherwise, it's the AttributeRangeMapper, rather than the AttributeValueMapper, that you'd want (though as you note it can't point to the Excel file dynamically).
My mind first went to the SchemaMapper, and then when I checked, ranges were a no go .
My mind first went to the SchemaMapper, and then when I checked, ranges were a no go .
Yes, I guess you could read the data, "explode" the ranges, write it back to an Excel file, then use that new Excel file as a SchemaMapper source?! But the InlineQuerier is probably the best idea. I've been thinking it over for a while and I can't think of anything better.
Yes, I guess you could read the data, "explode" the ranges, write it back to an Excel file, then use that new Excel file as a SchemaMapper source?! But the InlineQuerier is probably the best idea. I've been thinking it over for a while and I can't think of anything better.
Eh, I would probably do it in python, though I would use a csv file rather than an excel file for the reference file.
I'd probably do it in python as well, but if you're not too concerned about efficiency, you could use a featuremerger with duplicate suppliers set to yes and build a list of all the from to values on each feature, explode the list then test for if the value is between the from to value to only keep that result
Eh, I would probably do it in python, though I would use a csv file rather than an excel file for the reference file.
Because I couldn't resist, I did the exploding part and made a hub transformer of it. I probably wouldn't recommend it here, but I figured it might come in handy for someone.
https://hub.safe.com/publishers/safe-lab/transformers/rangeexploder
Because I couldn't resist, I did the exploding part and made a hub transformer of it. I probably wouldn't recommend it here, but I figured it might come in handy for someone.
https://hub.safe.com/publishers/safe-lab/transformers/rangeexploder
That presupposes the values are integers.
The SchemaMapper transformer will do exactly this, except... it doesn't work with ranges. So you would have to map each value individually.
Otherwise, it's the AttributeRangeMapper, rather than the AttributeValueMapper, that you'd want (though as you note it can't point to the Excel file dynamically).
I also thought about the schema mapper but as you've seen, there's no support for ranges.
Didn't think about this option. MIght be a good one. I'll look into it.
Didn't think about this option. MIght be a good one. I'll look into it.
For what it's worth, I think the inline querier is a better option than my solution
For what it's worth, I think the inline querier is a better option than my solution
You're right: I tried the InlineQuerier and it works perfect for this case!
You're right: I tried the InlineQuerier and it works perfect for this case!
Would you be able to upload your workspace and/or query? I'm trying to do this exact thing but haven't used the inline querier before.
Would you be able to upload your workspace and/or query? I'm trying to do this exact thing but haven't used the inline querier before.
@allyreynolds You just use a Cross Join to create a Cartesian Product of Table A * Table B = A.B, and use a WHERE to test if the value(s) in Table A are within any of the Lookup Ranges in Table B
- So, take Table A as say: ID, Value
- Take Table B as Say: MinRangeValue, MaxRangeValue, Classification
In SQL / InlineQuerier it just becomes,
SELECT A.*, B.Classification
FROM A CROSS JOIN B
WHERE A.Value>=B.MinRangeValue AND A.Value<B.MaxRangeValue
Depending on whether you are dealing with discrete (Eg. Integers) or indiscrete (Eg. Float) values may need to change whether you use > or >= and similarly < vs <=
The CROSS JOIN joins all possible permutations together of Table A and Table B, and the WHERE tests in that cartesian product (matrix) for where the Value intersects a Test Range.
Eg.
ID| Value| MinRangeValue| MaxRangeValue|Classification| Cartesian Intersection
1 | 0.5 | 0 | 5 | "0-5" | TRUE
1 | 0.5 | 5 | 10 | "5-10" | FALSE
2 | 6.7 | 0 | 5 | "0-5" | FALSE
2 | 6.7 | 5 | 10 | "5-10" | TRUE
Would you be able to upload your workspace and/or query? I'm trying to do this exact thing but haven't used the inline querier before.
Simple workspace attached, no cross join used in the inlinequerier, I'm not sure what difference it makes @bwn is probably better to explain if it matters or not
select
A.value,
B.score
from
Results A,
Lookup B
where
A.value >=B."from"
and
A.value < b."to"
Range_lookup.fmwt
Simple workspace attached, no cross join used in the inlinequerier, I'm not sure what difference it makes @bwn is probably better to explain if it matters or not
select
A.value,
B.score
from
Results A,
Lookup B
where
A.value >=B."from"
and
A.value < b."to"
Range_lookup.fmwt
In InlineQuerier it's the same query @ebygomm. SQLite will take that variant as an implicit statement of a Cross Join. Other variants of the syntax by instead using JOIN, INNER JOIN, LEFT OUTER JOIN or no *JOIN* clause at all (like yours) execute identically whenever the following ON or USING is omitted.
I only put the CROSS JOIN syntax for semantics as the descriptor of the actual process the SQL the InlineQuerier was following, just to make it clear, otherwise ironically your variant is the same syntax I first got used to when my introduction to SQL and GIS was through Mapinfo where similarly you could cross join Tables in it just by listing it as "FROM Table A, Table B, Table C" in its query tool! (and wow do I miss this in ArcGIS).
In InlineQuerier it's the same query @ebygomm. SQLite will take that variant as an implicit statement of a Cross Join. Other variants of the syntax by instead using JOIN, INNER JOIN, LEFT OUTER JOIN or no *JOIN* clause at all (like yours) execute identically whenever the following ON or USING is omitted.
I only put the CROSS JOIN syntax for semantics as the descriptor of the actual process the SQL the InlineQuerier was following, just to make it clear, otherwise ironically your variant is the same syntax I first got used to when my introduction to SQL and GIS was through Mapinfo where similarly you could cross join Tables in it just by listing it as "FROM Table A, Table B, Table C" in its query tool! (and wow do I miss this in ArcGIS).
Thank you so much!