iModel.js meets CSV (Part 2/3)

check out full sample | code diff

In the last post, we discussed how to read a CSV file from the iModel.js backend server using a custom RPC interface. We got that information onto the frontend and now we get to have some fun with it!

Let’s take a look at our data again.

The component IDs are associated with elements within the 3D model. Our next step is to find the exact coordinates for these elements. This information is contained within the iModel, but how exactly do we get to it?

Answer: ECSql — the database query language for an iModel.

We can use the iModel query API on the frontend and pass in an ECSql statement that will give us coordinates for each gasket listed in the CSV. But how do we figure out our query statement?

On to the iModel Console app! The web-based client that quietly sits there for hours just waiting for you to log into it, so you can open your iModel, and write ECSql queries that can help discover the exact contents for your data.

Let’s give the poor thing some attention. First, we will log into the app and open our target project and iModel:

Next, we need to put together some ECSql to run. This should be easy…all we need is the origin of a gasket with a given component ID.

In order for us to construct this query, we need the schema and the class which this component_id property belongs to.

I know there is a component_id property in the PipingComponent class in the AutoPlantPDWPersistenceStrategy schema. I also know that PipingComponent inherits from the PhysicalElement class and contains all the properties that a PhysicalElement contains. One of those properties is the origin which should contain the exact coordinates of the element we are looking for.

Let’s try to find the origin of one of the components from our list ‘AT_HRZF7AQ5_4II’:

Good news and bad news. We found an element; the ECInstanceId uniquely identifies it. However, that value of the origin is not gonna get us anywhere.

Maybe the piping component is an assembly ie. it is a PhysicalElement that is a parent of one or more other PhysicalElements that contain the actual geometric information we need. Yes, that can happen. If that were the case, the piping component would have an ElementOwnsChildElement relationship which would point to its children.

Relationships within an iModel connect SourceECInstanceId(s) to TargetECInstanceId(s). In the case of ElementOwnsChildElements, the source ID identifies the parent and the target IDs identify the children.

Let’s use the above ECInstanceId to see if the gasket has a child:

Aha! It does. We have the (Target)ECInstanceId of the child which is likely a PhysicalElement.

Now, let’s see if we can use that to find our origin:

And there it is! We have the location and the ECSql queries we need. All it took was a couple of noisy workplace coffees.

Now we can individually query each component’s location. However, every time we call the query API from the frontend, we are making a round-trip to the backend. We needed three separate queries to get all the way from the component_id to the origin. If we don’t do something better for our 22 components, that’s 66 round trips!

Imagine running this app in a low latency situation and going back and forth 66 times just to populate one array!

Let’s see if we can do this in one shot. We need to:

1. Join the PipingComponent table to the ElementOwnsChildElements table where the ECInstanceId of the PipingComponent is equal to the SourceECInstanceId (parent) in ElementOwnsChildElements.
2. Join the resulting table to the PhysicalElement table where the ECInstanceId of the PhysicalElement is equal to the TargetECInstanceId (child) in ElementOwnsChildElements.
3. Query the combined table to get a list of all the origins where the component_id matches the ones from our file.

After some head-scratching, and a little trial-and-error in the console…this is what I ended up with:

Let’s get that in our code. We can execute queries once we have an iModel open under the _onIModelSelected function in App.tsx.

Build and run to see the result:

Sweet! We can now parse out the origins from the result and add them to our original array obtained from the CSV:

Let’s see what our final result looks like in real-time:

Perfect! We got it all in one place.

Something interesting I just realized, we are already making a round trip to the backend when we query the CSV data in our custom RPC function. We could just throw all this logic into that implementation and save ourselves one more trip.

Let’s move our query code to the RPC implementation class and call it from our original fetchInfo function:

The only thing new here is the imodelDb handle (line 27). In the previous case, we were using an iModelConnection which is the frontend API to access the iModel briefcase opened by the app. IModelDb is its backend counterpart. The token variable that was passed into the RPC interface allows us to retrieve the same iModel that was opened on the frontend.

Now our frontend only needs to call the fetchInfo function:

Bam! Single round trip and single line of code.

This is what our final array looks like and we have all the pieces of information we need to prepare a solid demo that will (hopefully) knock the socks off of those clients.

Our last task is to show this data on our model:

Don’t worry. I know we could both use a break.

Until next time.

-Roop, reporting live from the basement.

<- previous post | home | next post ->