IFC for relational databases - ifcSQL

[bsbock]

The idea behind storing IFC-data in a database:

What could be a way to store IFC-data in a relational database?

Strategy no. 1:
Store every IFC-File in a binary large object field.
(-) no advantage over files
(-) not searchable
(-) no rule checking

Strategy no. 2:
Store every IFC-STEP-line in a text-field.
(-) not searchable
(-) no rule checking

Strategy no. 3:
Store every not abstract Entity-classes (over 600) in its own table. Same with enumerations.
(+) high recognition value in tablenames
(-) no rule checking because you can reference only to one other table, not to all inherited tabels
(-) too much tables (confusing)

Strategy no. 4:
Store every inheritance-step in its own table(782).
(+) recognition value
(+) rule checking on base-classes possible
(-) don’t work with SELECT-Types
(-) too much tables (more confusing than strategy no. 3)

Strategy no. 5:
Divide between entities and their attributes. One table for entities and one table for attributes
(+) only 2 tables (totaly not confusing)
(o) rule checking with triggers
(-) attribute values not typesave

Strategy no. 6:
Divide between Entities and their attributes. One table for Entities and for each basetype a table for Attributes
(+) just a few tables (not confusing)
(o) rule checking with triggers
(+) typesave attribute values

This is a picture of Strategy no. 6:

Up to here is only storing and querying data. No rule checking!
Next time I will talk about rule checking with ifcSQL.ifcSchema, the SQL-representation of the express-schema.

[ReD_CoDE]

I think IFC has the potential to be relational based

For instance IfcSite:

http://standards.buildingsmart.org/IFC/RELEASE/IFC4/ADD2_TC1/HTML/schema/ifcproductextension/lexical/ifcsite.htm

We can make it as a table:
GlobalId, OwnerHistory, Name, Description, ObjectType, ObjectPlacement, Representation, LongName, CompositionType, RefLatitude, RefLongitude, RefElevation, LandTitleNumber, SiteAddress

And also add relationships as columns in this table and also Psets and Qsets, etc

In many cases the number of entities will reduce

For instance, you will have just IfcPerson and IfcOrganisation tables and if IfcActor table when has both ThePerson and TheOrganisation then inside the Actor table system will autocomplete as IfcPersonAndOrganisation
So we can drop IfcPersonAndOrganisation completely even

IFC SQLite will cause a lite and better structured IFC schema if we all work on it to develop it

As I know “select” is not any issue in relational detabases like MySQL and for sure in SQLite, we can act with them as “picklists” in Excel

1:1 or 1:M or M:M relationships will cause database or IFC schema be unsearchable?

Then you don’t need to have 130 “Defined Types” you can manage them with “DATA TYPES”
207 Enumeration Types will become tables
60 Select Types will reduce and will manage them as “autocomplete”
776 Entities will reduce and will become tables with relationships, "especially if focus on “END ENTITIES”

IFC Modernization : Relationships

Is it possible to develop a new IFC based on just end entity? I mean a much shallower tree. [NewIFC] For instance, IFC just has end entities like IfcProject IfcProjectLibrary IfcSite IfcBuilding IfcBuildingStory IfcSpace IfcZone IfcActor IfcOccupant So on And drop all behind entities and just end entities (single entities) have types, rules, functions, and “simplified relationships” Kind regards, Ehsan

IfcX and IfcLite projects

[IfcXtreme%20Concept%20--%20Ehsan%20Azari] IfcXtreme base concepts come from cross-platform visual programming and develop IFC entities as visual programming components and objects The concept develops based on “end entities” which explained in IfcLite project as a question The preliminary concept developed with Grasshopper + BHoM

IfcX and IfcLite projects

I developed the idea based that complete tree, but based on end entities, for instance, IfcProject or IfcSite are end entities on the hierarchical approach. What does it mean? It means that I’m going to build a middle-ware which shows an easy to understand end entity like the example IfcActor, which the object on the front-end shows which data and datatype needed? And on the back-end, it converts that object to IFC-SPF or IFC-XML and or any other structured code which is standard I put IfcOcc…

And so on

[bsbock]

What you speak about is Strategy no. 3:
Store every non abstract Entity-classes (over 600, you called “end-entities”) in its own table. Same with enumerations.
(+) high recognition value in tablenames
(-) no rule checking because you can reference only to one other table, not to all inherited tables
(-) too much tables (confusing)

[ReD_CoDE]

The number of entities will reduce

At the moment in EXPRESS (SPF) and XML or RDF (OWL) and all other approaches (formats) we see these huge number of Entities (776), Defined Types (130), Enumeration Types (207), Select Types (60), Functions (47), Rules (2), Property Sets (420), Quantity Sets (93), and Individual Properties (1722)

So in SQLite these will be 60-70% of the existing

I say let’s drop IfcRoot, IfcOwnerHistory, and IfcObjectDefinition, …, and just have the end one
For instance an IfcWindow is just an entity → We can with “CLASSIFICATIONS” say that a window is an object, a product, an element, a building element

And I can’t understand what does this mean?

you can reference only to one other table, not to all inherited tables

So an SQLite table can’t have a lot of relationships to other tables and columns inside those tables?
And if has a lot of relationships will cause it to be unsearchable?

Will not work this methodology?

CREATE TABLE IfcSite (
   IfcSite_id integer NOT NULL CONSTRAINT IfcSite_pk PRIMARY KEY,
   IfcSite_GlobalId varchar(128) NOT NULL,
   IfcSite_OwnerHistory integer,
   IfcSite_Name varchar(40),
   IfcSite_Description varchar(255),
   IfcSite_ObjectType integer,
   IfcSite_ObjectPlacement integer,
   IfcSite_Representation integer,
   IfcSite_LongName text,
   IfcSite_CompositionType varchar(40),
   IfcSite_RefLatitude varchar(20),
   IfcSite_RefLongitude varchar(20),
   IfcSite_RefElevation varchar(20),
   IfcSite_LandTitleNumber varchar(15),
   IfcSite_SiteAddress integer,
   ElementCompositionEnum_ElementCompositionEnum_id integer NOT NULL,
   IfcSite_Pset_LandRegistration integer,
   IfcSite_Pset_SiteCommon integer,
   IfcSite_Pset_PropertyAgreement integer,
   IfcSite_Pset_ThermalLoadAggregate integer,
   IfcSite_Pset_ThermalLoadDesignCriteria integer,
   IfcSite_Qto_SiteBaseQuantities integer,
   IfcSite_SpatialComposition integer NOT NULL,
   IfcSite_SpatialDecomposition integer NOT NULL,
   IfcSite_SpatialContainer integer NOT NULL,
   IfcSite_Placement integer NOT NULL,
   IfcSite_FootPrintGeomSetGeometry integer NOT NULL,
   IfcSite_SurveyPointsGeometry integer NOT NULL,
   IfcSite_BodyGeometry integer NOT NULL
);

I used Sentence case just be easy to read

[bsbock]

@ReD_CoDE

And I can’t understand what does this mean?

you can reference only to one other table, not to all inherited tables

One example (here without ifc-prefix) with the entity-type ExtrudedAreaSolid(ProfileDef SweptArea, …

if you have a table “ExtrudedAreaSolid” with a column “SweptArea”. To what foreign table do you want to refer to make sure that your refer only to an allowed entity?

SQL snippet: FOREIGN KEY([SweptArea]) REFERENCES [???]

These are the possible entity-types you can refer to:
ProfileDef
.ArbitraryClosedProfileDef
. .ArbitraryProfileDefWithVoids
.ArbitraryOpenProfileDef
. .CenterLineProfileDef
.CompositeProfileDef
.DerivedProfileDef
. .MirroredProfileDef
.ParameterizedProfileDef (abstract)
. .TShapeProfileDef
. .TrapeziumProfileDef
. .UShapeProfileDef
. .ZShapeProfileDef
. .AsymmetricIShapeProfileDef
. .CShapeProfileDef
. .CircleProfileDef
. . .CircleHollowProfileDef
. .EllipseProfileDef
. .IShapeProfileDef
. .LShapeProfileDef
. .RectangleProfileDef
. . .RoundedRectangleProfileDef
. . .RectangleHollowProfileDef

[ReD_CoDE]

@bsbock

ExtrudedAreaSolid

This is why I believe that a hybrid methodology can help with
Combination of SQLite and XML or JSON or JavaScript or any other modern technology

SQLite can refer to all of them? a PK can has many 1:1, 1:M, M:M relationships?
Several number of refers is not issue, because at the moment other methods like EXPRESS-SPF, XML, OWL, JSON, all “have to” handle this huge data, datatypes and relationships

I’m not programmer and mainly these days have focused on our startup
But I believe that “if” buildingSMART and some corporations and organizations that control buildingSMART want they can improve IFC or develop a modern one

I think this can help us find our way (a hybrid approach is needed):

What is the difference between a knowledge graph and a graph database?

[sergej]

FYI relational database != graph database.

[ReD_CoDE]

FYI I said a mixed usage --> Relational + Graph

I believe that a mixed approach is good (SQL and No-SQL),
A combination of SQLite, XML and JSON and RDF-OWL
Mainly JSON for real-time usages like IoT

SQLite can’t solve the whole issues alone, but will solve part of and other technologies and methodologies will solve another parts

So for relational side let’s choose SQLite and for other part use Object-Oriented or Graph (Or even both) to build a schema which is more modern and also more flexible

[ylcnky]

While Relational + Graph database combination is technically possible, feasibility and practicality of handling two databases at the same time can be a critical issue.
@bsbock #6 looks like a better approach unless rule checking is a critical requirement. Is there a particular reason for why you want to store in a relational database? Have you reviewed NoSQL alternatives?
Meanwhile, I came up with this paper.

isprs-archives-XLI-B2-625-2016.pdf

[ReD_CoDE]

Maybe I’m wrong, but as I know No-SQL doesn’t have a good performance like SQL (but for sure maybe a mixed approach SQL + No-SQL be good, maybe)

It seems that Esri uses a relational approach and this document explains relationships, especially “Relationship Class - Navigation Property” which I think would be useful

http://resources.esri.com/help/9.3/arcgisengine/dotnet/2e097482-4f99-4224-896e-8027e6ab883e.htm

[bsbock]

@ylcnky

Meanwhile, I came up with this paper.

What the (very interesting) paper speak about is Strategy no. 4:
(+) recognition value
(+) rule checking on base-classes possible
(-) don’t work with SELECT-Types (by use of FOREIGN KEY(…) REFERENCES(…) )
(-) too much tables (more confusing than strategy no. 3)

[bsbock]

@ylcnky

@bsbock #6 looks like a better approach unless rule checking is a critical requirement.

With Strategy no. 6 rule checking workes fine with triggers. Give me some days for posting an example.

[bsbock]

@ylcnky

Is there a particular reason for why you want to store in a relational database? Have you reviewed NoSQL alternatives?

My reasons are described here:

IFC SQLite Project

ok, let’s start with IFC for relational databases. First of all: why to do this? For me the benefits of sql based working are well known data-access (like XML too), no parser required integrated referencial integrity (not by a seperate test) typesave in base-types multiuser functionality (many users can work on the same datapool at the same time) The points 3) and 4) are - as far as I know - not capabilities of SQLite. But SQLite could be a possible target for IFC-Data. It is not only a S…

But most of all: Leaving the world of a thousand files. Growing up from Luggage to Wardrobe
as shown in the picture in IFC for relational databases - ifcSQL

[sergej]

I was going from “SQLite and XML or JSON or JavaScript or any other modern technology” which would essentially mean a hybrid of a database, some sort of encoding format and a programming language. Somewhat mixing pears and apples which is happening here also:

“So for relational side let’s choose SQLite and for other part use Object-Oriented or Graph (Or even both) to build a schema which is more modern and also more flexible”

The IFC schema does not forbid encoding in a database format, in fact SQLite is mentioned as a possible format here. The scheme would not be more modern by being “encoded” into a database. The schema is the schema and encoding of the schema is a format of some kind.

It is not as though software vendors do not have and are working on their own versions of databases for buildings and infrastructure in the form of CDEs. Maybe they are not participating here because they do not want to share their secrets. But how does a relational database solve the data exchange aspect? Are you proposing that bSI does this?

[ReD_CoDE]

Thanks Sergej, I know the schema is schema and database is a database

I’m just practicing to find a way to build the schema based on “reducing schema tree layers from 4-11 to 1-4”

“After ISO 19650, IFC, especially IDM needs some improvements, so “WHAT IF ISO BIM, CLASSIFICATION AND IFC BE MORE CLOSE TO EACH OTHER AND BUILD A BETTER SOLUTION FOR THE INDUSTRY””

As said before we can reduce IFC schema layers and instead use classifications to say a window is an object, a product, an element, a building element

Also, ISO 19650 naming approach can help us develop a better way than just GUID/UUID (in 128-bit approach) for identification

And mainly I’ve focused on developing a “middleware”

Yes, IFC SQLite mentioned there, but it seems that after Tim Chipman from Arup left, nobody has worked on IfcDoc and IFC related projects, it seems that he was playing a key role inside the bSI

IfcDoc exports schema to SQL but have you checked it?

Converting .ifc file to SQL is an obvious method today in the industry, this is not issue
But developing IFC and also IFD, and schema based on relational approach like SQLite (as a file) or SQL (as server solution) is important

And yes, maybe some say it’s CDE, but it’s more than, it’s adding automation, control, in general logic to the schema and to its databases/solutions

So, maybe today some have this as secrets, but I want to give it as a gift to all (specially to a country that has started a national degital platform and soon I will be part of them and their movement). It’s better others find another secrets if this is their secret

[bsbock]

@ReD_CoDE

IfcDoc exports schema to SQL but have you checked it?

Where can I find the ifcDoc-Schema-Export to SQL? Save as MDB produces no file for me.

[bsbock]

@sergej

The scheme would not be more modern by being “encoded” into a database. The schema is the schema and encoding of the schema is a format of some kind.

• IFC-STEP is a textfile for one project with a hint to the express-schema which you can test separately
• ifcXML is a wellknown textfile for one project with an explicit link to the XSD-schema which you can test separately
• ifcSQL could be a database, which include data of many projects including the schema and allows only inserts and updates which match to the schema

[ReD_CoDE]

In IfcDoc you can export publications to any output you want
If need help let me know to explain the process

So the output would be HTML documentation plus formats you have chosen

Forum doesn’t let upload SQL file, so uploaded it here: https://gofile.io/?c=6DXMTU

It seems that bSI has started to focus on UML, I hope UML 2, but again with Object-Oriented approach, so I think O-O is good, but relational approach or Object-Relational approach is better

[bsbock]

Thank you for upload, @ReD_CoDE!

The result looks like this:

CREATE TABLE IfcObject (oid INTEGER,
GlobalId TEXT,
OwnerHistory INTEGER,
Name TEXT,
Description TEXT,
ObjectType TEXT);
…
CREATE TABLE IfcActorRole (oid INTEGER,
Role VARCHAR,
UserDefinedRole TEXT,
Description TEXT);
…
IfcObject is abstract and IfcActorRole is derived from IfcObject, which contains only the attributes, that are not contained in IfcObject.
This is Strategy no. 4:
Store every inheritance-step in its own table(782).
(+) recognition value
(+) rule checking on base-classes possible
(-) don’t work with (every) SELECT-Types
(-) too much tables (more confusing than strategy no. 3)

[jwouellette]

1. Has anyone looked at bimserver.org and discussed/analyzed how they’ve executed their IFC database & server? Is it effective? At least a beginning? It is one way that IS working currently and meeting the needs of many…

2. What if the IFC schema had its root definition/description in UML and then expressed more specifically as EXPRESS, XSD, OWL, etc… Would this help in establishing flexibility between different implementations, but maintain a central, overall context and semantic cohesiveness? Would it help in then defining different database implementations from a common root?

Please also refer to my reply in another topic:

What would you like to see most in the "modernization" of IFC?

For the past few months, there has been discussion within the bSI technical community to look forward at the architecture of IFC. As part of this discussion, it is important to understand that there are different components, such as: Description Method - including the Definition Language (UML vs. EXPRESS, etc.) and Data Formats (STEP, XML, JSON, etc.) Specification - including the structure and content of the schema and possible extensions like Taxonomy and Data Dictionary Implementation M…