wherefore_art_thou_data

Exploring an iModel

This section uses the iModel Console to execute queries against the Bay Town Process Plant sample iModel.

Open an iModel in the iModel Console

Go to https://imodelconsole.bentley.com
Select a project, then select an iModel then select a changeset
Wait until the green button in the lower right says Run

Basic Queries

Query for Elements
```
 SELECT * FROM Bis.Element
```
Count the number of elements in the iModel
```
 SELECT COUNT(*) FROM Bis.Element
```
Show the classes which are used in the current iModel
```
 SELECT ECClassId, COUNT(*) FROM bis.Element GROUP BY ECClassId ORDER BY COUNT(*) DESC
```
NOTE 1: GROUP BY, ORDER BY [ASC DESC] are standard parts of the SQLite syntax

NOTE 2: ECClassId is stored as a 64 bit integer but are converted to a more readable format of <SchemaName>.<ClassName> for display. Queries use the internal 64 bit representation which enables very efficient matching.
Select all ProcessPhysical Pipe elements
```
 SELECT * FROM ProcessPhysical.PIPE
```
NOTE: There are a lot more properties shown when querying for PIPE then for bis.Element. ECSql is polymorphic but only selects the properties defined by the FROM class.
Select all ElementRefersToElements relationships
```
 SELECT * FROM Bis.ElementRefersToElements
```
NOTE 1: Every row (Element, Model, Link Table Relationship) has an ECInstanceId, this is a unique 64 bit integer for that type of instance (e.g. an ECInstanceId is unique for all Elements but there may be a Model with the same Id)

NOTE 2: Each Row has a SourceECInstanceId and a TargetECInstanceId, these are the Ids of the Elements on either end of the relationship.

Find Elements which are related to ProcessPhysical.PIPE

 SELECT ere.ECClassId, s.ECClassId, t.ECClassId, COUNT(*) FROM Bis.ElementRefersToElements ere 
   JOIN bis.Element s ON s.ECInstanceId = ere.SourceECInstanceId 
   JOIN bis.Element t ON t.ECInstanceId = ere.TargetECInstanceId 
   WHERE s.ECClassId IS (ProcessPhysical.PIPE) OR t.ECClassId IS (ProcessPhysical.PIPE)
   GROUP BY s.ECClassId, t.ECClassId

NOTE 1: This is an INNER JOIN, if you want an OUTER JOIN use LEFT JOIN

NOTE 2: This query uses the IS keyword to limit classes. See this tutorial on Type Filtering for more details.

Select weight and length of pipes

 SELECT p.LineNumber, p.Length, p.Weight FROM ProcessPhysical.PIPE

Sum up the length and weight of pipe by Line Number

 SELECT p.LineNumber, SUM(p.Length) Length, SUM(p.Weight) Weight, COUNT(*) num_Pipes 
   FROM ProcessPhysical.PIPE p GROUP BY p.LineNumber

Add a grouping by component name

 SELECT p.LineNumber, p.Component_Name, SUM(p.Length) Length, SUM(p.Weight) Weight, COUNT(*) num_Pipes 
   FROM ProcessPhysical.PIPE p GROUP BY p.LineNumber, p.Component_Name

Next: Exploring Schemas in the Schema Explorer