Challenge: Display all user labels associated with spatial elements in a confined cube.

Summary

By completing this you will learn how to:

• Create a new imodel to be used with the imodel console.
• Query for specific properties in an ECClass.
• Join classes for additional information.
• Add conditions to find specific elements.

Setup

1. Go to the getting started registration dashboard (If you haven’t registered yet, please register a new account)
2. Click on “New iModel”
3. Name the imodel whatever you like (i.e. “My House Sample”).
4. In the drop down menu, select “House Sample”.
5. Click on “submit” and allow the process to complete.
6. Navigate to the imodel console.
7. Follow the sign in process to gain access to the console.
8. (Optional): Read through the quick tips and do the tutorial.
9. (Optional): If you’re not familiar with ECSQL/SQL, check out our learning page

Instructions

Once the setup is complete, we’ll need to open the imodel you’ve just cloned using the imodel console.

Opening your imodel in the console.

1. Click on your project name in the table under “Listing all Contexts” to display imodels associated with the project
2. Click on your imodel name in the table under “Listing iModels from Project” to display changets associated with the project
3. Click on the named version id to finally open the imodel.

Congratulations! The imodel is now open and ready for queries.

Steps to complete the challenge

1) Begin by listing all EC properties available for all spatial elements in the imodel.

• Write a query to pull all ECProperties from ECClass Bis.SpatialElement
• The qualifier for all properties is ‘*’

Hint For Step 1

The format should look similar to: SELECT ____ FROM ____

Solution Step 1

SELECT * FROM Bis.SpatialElement

2) Instead of displaying all EC properties, write a query that only lists the EC properties we’re interested in: “ECInstanceId”, “UserLabel” and “Origin”

• ECInstanceId is the unique identifier for the element.
• UserLabel is a user created label to describe the element.
• Origin will tell us where the element is in the 3d space.

Hint For Step 2

The format should look similar to: SELECT ____, ____, ____ FROM ____

Solution Step 2

SELECT ECInstanceId, UserLabel, Origin FROM Bis.SpatialElement

3) While the origin locates where the spatial element is, the size is still unknown. We need to query from the class “Bis.SpatialIndex” for this.

• Write a query (similar to step 1) that lists all information available from the class Bis.SpatialIndex
• Class Bis.SpatialIndex contains range information for spatial elements

Solution For Step 3

SELECT * FROM Bis.SpatialIndex

4) Notice how SpatialIndex contains the range but it doesn’t contain ‘UserLabel’. We need to JOIN class Bis.SpatialElement and class Bis.SpatialIndex together.

• Write a query to to display all EC properties from class Bis.SpatialElement and Bis.SpatialIndex together.
• To join the classes, we need to find the key ECProperty that both classes can JOIN on.

Hint 1 For Step 4

The syntax to join is ... FROM Class JOIN OtherClass ON ClassKeyProperty = OtherClassKeyProperty`

Hint 2 For Step 4

The joining property key is ECInstanceId. (i.e. Bis.SpatialElement.ECInstanceId and Bis.SpatialIndex.ECInstanceId)

Hint 3 For Step 4

The format should look similar to:
SELECT * FROM ____ JOIN ____ ON ____ = ____

Solution For Step 4

SELECT * FROM bis.SpatialElement JOIN bis.SpatialIndex ON bis.SpatialElement.ECInstanceId=bis.SpatialIndex.ECInstanceId

5) Once again the data shown is too verbose. Only pull the information we want to see.

• Write a query to pull only UserLabel from Bis.SpatialElement and MinX, MinY, MinZ, MaxX, MaxY, MaxZ from Bis.SpatialIndex
• TIP: We can alias names - instead of writing bis.SpatialElement.ECInstanceId, we can write e.ECInstanceId if we declare SELECT ... FROM bis.SpatialElement e

Hint For Step 5

The format should look similar to:
SELECT e.____, i.____, i.____, i.____, i.____ FROM ____ e JOIN ____ i ON e.____ = i.____

Solution For Step 5

SELECT e.UserLabel, i.MinZ, i.MinY, i.MinZ, i.MaxX, i.MaxY, i.MaxZ FROM bis.SpatialElement e JOIN bis.SpatialIndex i ON e.ECInstanceId=i.ECInstanceId

6) Now that we have all the information we need, it’s time to add a simple condition.

• Write a query with the WHERE clause to show only spatial elements that have a MinX >= 5 AND MinY >= 6.
• The format for WHERE clause is FROM ... WHERE ... AND ...

Hint For Step 6

The format should look similar to:
SELECT e.____, i.____, i.____, i.____, i.____ FROM ____ e JOIN ____ i ON e.____ = i.____ WHERE i.___ >= 5 AND i.___ >= 6

Solution For Step 6

SELECT e.UserLabel, i.MinZ, i.MinY, i.MinZ, i.MaxX, i.MaxY, i.MaxZ FROM bis.SpatialElement e JOIN bis.SpatialIndex i ON e.ECInstanceId=i.ECInstanceId WHERE i.MinX >= 5 AND i.MinY >= 6

7) The final challenge is to retrieve the user labels of all spatial elements that are contained in a cube with the minimum bounding coordinate at (5, 6, 6) and maximum bounding coordinate at (15, 15, 14).

• (X, Y, Z) are cartesian coordinates.

Solution For Challenge

SELECT e.UserLabel, i.MinZ, i.MinY, i.MinZ, i.MaxX, i.MaxY, i.MaxZ FROM bis.SpatialElement e JOIN bis.SpatialIndex i ON e.ECInstanceId=i.ECInstanceId WHERE i.MinX >= 5 AND i.MinY >= 6 AND i.MinZ >= 6 AND i.MaxX <= 15 AND i.MaxY <= 15 AND i.MaxZ <= 14

Conclusion

So, were you up to the challenge? Feel free to explore the console and experiment with your own queries. You can learn much more about how ECSQL queries work here.

Feedback is welcome! Let us know via the iModel.js community.