常用ECSQL整理

和元素相关的一些属性解释

ECInstanceId

FederationGuid

Used to federate (i.e. correlate) an Element with other Repositories capturing the same Guid for the same "Entity" that an Element represents. It is ensured to be unique in an iModel (i.e. unique-index on the associated db column).

CodeValue

Used to capture a Human-friendly Identifier that captures Business-meaning and it is unique in some context defined by the Business or Application creating them

ExternalSource Identifier

Elements that were synchronized from an External Source carry their Provenance via an Aspect called "ExternalSourceAspect". Such Aspect has a string-property called "Identifier" that the application synchronizing the external data uses to capture an Id from the external repository that such app (i.e. typically a Connector) uses to compare an Element with the corresponding "external record" it came from (to detect changes) during subsequent runs. Each app (Connector) stores something different in that "Identifier" property - thus, an "External Source Identifier" is only meaningful in context of the External DataSource it is associated with.

ECSQL

ECSQL - iTwin.js

 

1 查找父元素

SELECT ECInstanceId, Parent.Id FROM bis.ElementSELECT ECInstanceID, SourceECInstanceID FROM bis.ElementOwnsChildElements// SourceECInstanceID为Parentid

2 查找元素的aspect

SELECT e.ECInstanceId, e.Parent.Id, a.Identifier
FROM bis.Element e
LEFT JOIN bis.ExternalSourceAspect a ON a.Element.Id = e.ECInstanceId

3 查找元素的ExternalSource Identifier

如果是dgn，通常是Element Id，如下是根据Element Id 来找EcInstanceId

SELECT e.*
FROM bis.Element e
JOIN bis.ExternalSourceAspect a ON a.Element.Id = e.ECInstanceId
WHERE a.Identifier = ?

4 查找PhysicalModel名称

SELECT e.CodeValue from BisCore.Element e JOIN BisCore.PhysicalModel p on e.ECInstanceId = p.ECInstanceId

5 查找一个model中的所有几何元素

SELECT ECInstanceId, Model.Id AS ModelId, Category.Id AS CategoryId
FROM BisCore.GeometricElement3d  WHERE Model.Id IN (your modelId) AND GeometryStream IS NOT NULL

6 根据范围找元素

SELECT ECInstanceId FROM BisCore.SpatialIndex WHERE minX>=-81.08 AND maxX<=-80.58 AND minY>=35.00  AND maxY<=35.44// https://www.itwinjs.org/learning/spatialqueries/