Skip to main content
  • EN

I have multiple MicroStation V8i files and need to add an attribute for what category I want to assign to each feature (1.4 million features) based on the igds_level_name. I also have an Excel file that has a list of level name codes and the category assigned to the code.

I want to read through the excel file in order and find level names that contain the code. For example, the namecode "TEXT", I want to assign an attribute value of "Annotation" to all of the dgn level names that contain the string "text". ["Annotation" is assigned to a created attribute called Category in the dgn feature.] No information will be overwritten so once a feature has a category assigned to it, it is removed from the comparison, so the order read is important. Hope this makes sense. I thought the InlineQuerier might be the way to go but my SQL is pretty much a novice level.

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.

 

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.

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.

 

 

Given the use of the geometry filter I will abandon this line of thinking. If anyone knows a way to do my original request, please post it so others can see how it's done. I am switching to a different line of attack and will post a different question for help on that method. Thanks to everyone for looking and for your response.

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.

 

 

further discussion and solution to assigning a value in order given in an Excel file can be found in this thread

https://knowledge.safe.com/questions/76000/inlinequerier-table-update-using-substr-like-where.html

Reply


Community Stats

30,159
Posts
114,480
Replies
37,807
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.