This section uses the iModel Console to execute queries against the Bay Town Process Plant
sample iModel.
- 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
-
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 useLEFT 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