Compare DGN attribute strings, contains code in Excel, assign value to 3rd attribute

I have been unsuccessful at writing a SQLite routine but the logic:

 

The process flow: Row i, T1(Excel) - read the text in column "NameCode", compare to T2.igds_level_name(MicroStation V8).

 

Where T2.igds_level_name is like T1.NameCode (i) AND T2.Category is empty or null, T2.Category=T1.Category. [there will be hundreds of records that meet this requirement]

 

next i

 

Hopefully this will help someone who is better at SQLite than I am.

 

ebygomm wrote:

I'm not sure that your idea of reading the excel file and working through in order will work in a non-database environment.

The inline querier will allow you to merge the data based on a partial attribute match, e.g.

select "igds_level_name", "CATEGORY" from "Data", "Lookup" WHERE instr("igds_level_name", "NAMECODE") > 0; but I'm not sure if this is exactly what you want.

My question is, what order does this takes place? If it takes the target file (dgn) and just does like comparisons against the Excel table, then this won't work unless it searches the Excel from top to bottom and stops at the first match. I have the Excel in order of priority.

 

But you do have me thinking! After this transformer I have a geometry filter. The geomfilter will separate out the text layers so I may not have a reason to do it here. So having Building-Text can actually go in the Building Category and then be filtered into text geometry. This was the biggest reason for me wanting to increment through the Excel rows... all the text processed first. I do still have the problem of the NameCode being LIKE to more than one levelname. Is there a way of doing the comparison on maybe the first 10 characters of the levelname? Most of the levelname is detail but the primary category is at the front of the name, but not always in a consistent number of characters.

 

 

Just a quick clarification -- the work around the level names and what attributes you want to assign seems to be unrelated to the actual data. So am I right that we might be able to pre-compute this. So if the level name was "ROW_LINE_Existing", you could establish that you wanted to add an attribute named ??? with the value %%% before you started reading the 1.4 million features? For efficiency I think I'd want to do that. Then when you read the features you'd be able to apply the new attirbutes based only on the level name.

 

 

I'd love to explore this one some more but would need more exact examples of what is going on. i.e. given a level named "YYYY", and these rows in the Excel, this is what I want to add to the data feature ultimately.

 

 

I ended up trying a different strategy and takashi may have come up with a solution that will work. https://knowledge.safe.com/questions/76000/inlinequerier-table-update-using-substr-like-where.html

 

I want to be able to read in any CADD drawing and assign a category to the layer/level_name such that OIL_tanks_xxx will have a category of Fuel. The category attribute will then be used in the writer to fanout and create shapefiles for the different categories. The problem is that a level_name such as ROW_EASEMENT and all the other ROW levels can not be created separate unless I can read the Excel in order. If you refer to the thread above takashi has provided a script that compares the read order and only uses the first one. It is not an iteration but effectively does the same thing.