Tag Archives: Database

Join External Table to Revit Database

4 Feb

In my last post, I showed how to filter a table by creating a view and bringing that view in to your MVC Application. You can also join tables. In this post, I will show how to join Revit data to an external Database.

The Database

I export my Revit model to a database called RevitSample. While it appears that I can add additional tables and Revit will not overwrite them, it is probably best to stay out of the tables in the Revit Database. To avoid corruption of my Revit DB, I will create another database called AdditionalData. I will also add a table called MoreData. Below is an image of the Databases and the Table.

MultipleDBsTable

Joining

I now have a table that has room numbers which coincide with the numbers in my Revit Rooms table. Any data in this table can be joined to the Revit table using number as the join field. to create the join, I add a new view and enter the sql shown in the image below.

JoinFromOtherDB

MVC Application

The additional data has been added to the Revit View. I can now create an MVC application and load my view as the model. When I am done, I will have a table like the one in the image below.

TablesJoinedInApp

While you can view this table, because there is no primary key in the view, you cannot edit. There appear to be workarounds, but while I can make the errors go away, the save function does not work. This is acceptable as editing should take place in the correct table, not a view.

Why

why would you want to do this? I spent many hours typing data in to Revit because we needed it in a Report and we could get it from the schedule. Being able to join the data, I can load the outside data in to a table and join it. Then I can export it to my application and it is in a schedule or report. The Revit model remain untouched and my outside data can change and if it gets corrupted, my model is safe. As long as I do not need the data in Revit to generate plans, why not attach it later. I can still query it as if it is in the same table and generate all my reports and schedules. Revit doesn’t exactly create pretty schedules for reporting purposes, so why not use the Database to do it for you using something like Crystal Reports?

 

Advertisements

BIM: Split vs. Merged Models

1 Nov

As we move to BIM, architects need to acquire new skills, particularly in the area of information systems. In school, magazines, and competitions, images are what matter – design is king. Design will always be important, but we are in an information age of architecture. BIM, Energy Modeling, and Form Finding using Scripts are the new architecture. Databases have become a tool of the trade and I fear that architects are lacking in their knowledge of them – if you refer to an Excel spreadsheet as a database, you are lacking. I am not advocating that architects become programmers and information systems experts, but I am suggesting that without at least basic skills you will fall behind. AutoCAD has had a LISP environment since 1986 yet the majority of architects I know would tell you that LISP is a speech impediment. How little knowledge we have of our tools. It’s like using a pencil but ignoring the eraser on the end and starting over whenever you make a mistake.

 

My interest today and the reason for this post is to offer my two cents on the difference between split and merged models.

 

Simply put, a merged model is a model with geometry and data combined. This would be a Revit model where all the data about the objects – rooms, walls, doors – exist in the model. This is the model type that Revit leads you toward. And what happens when you embed large amounts of data in to Revit? Your file size becomes massive and performance takes a hit. For what? So that you can create a schedule later or to color code a floor plan? What if you need this data for something else? Are you going to export it to CSV every time you need it? What about a client? Are they going to need Revit to update the information about their building?

 

A split model, on the other hand, is where the geometry and the data are in different systems. The geometry is in your Revit model and the data is in a database – SQL Server or MySQL. A split model is superior to a merged model and this is what we should be aiming for.

 

There are certain pieces of data that will always be in Revit. The area of a room or a wall should be determined by the geometry, not entered in a database. But the overriding point is that the model should have the geometry and an ID for each object. That is all. You should have a database with tables for Walls and Rooms and these tables should have an ID and the rest of the data – use, type, department, fire rating, load bearing.

 

So how do we create schedules and create fancy color coded plans without the data? We use a JOIN and connect the tables of data to the geometry based on the ID.

 

The data about our building will change far more often than the building. Painting walls, replacing doors or carpeting can be updated in a database. The model only needs to be updated when something is added – an addition – or removed – demolition.

 

Another reason for split models is that we should keep our tools separate. Do you really need a 3D modeling application to also be a full blown database application? No. We only need the ability to connect them. Let Revit do what it does best – model.

 

Planners use split models in GIS, programmers use a Model-View-Controller framework and web developers have been working to remove the code and logic from the design of web pages for years. This is nothing new. Architects need to look at how other professions have solved this problem and learn from their best practices. I remember a quote from an officer in the Coast Guard that went something like this: “The data about our buildings is worth more than the buildings themselves.”

Link a QR Code to a Database

2 Feb

In an earlier post, I showed how to link a QR Code to a map by using PHP _GET. This allowed me to pass variables to a script and have the script run in realtime to update the page. I am going to do the same thing in this post, but the data I pass will be used in a SQL statement to retrieve data from the database.

I think this would be a great tool for a facility to use to inventory their equipment. Lets say I have a swamp cooler on the roof of my building — I live in New Mexico. I am on the roof and I need to know something about this particular unit. I can scan a QR Code on the side of the unit, it will then pull up a website that displays everything about this unit: The manufacturer, when it was installed, when it was last serviced, what was done to this unit during the last service, when it is beyond its useful life. On the same page, I could have a form that allows me to update the data. I can enter todays date  as the new last serviced date and put a description of what I did – changed the pad and oiled motor. The next time the code was scanned, this data would be available.

Since I have a room database from Revit already setup, I am going to show how to connect a QR Code to this database to retrieve the room properties. To modify this to work on any other object, just change the database connection.

I wrote a PHP script called get.php. This script selects all the data from a rooms table in a MySQL database where the room number is from the QR Code: SELECT * FROM rooms WHERE number = $_GET[roomchosen].

_GET[roomchosen] is the PHP call to pull the data from the URL that was sent via the QR Code. Here is he full code:

Now I just need a QR Code that has the URL to the script and sends the room number: http://room.educationalfacilityplanning.com/get.php?roomchosen=3. This URL will select room 3. I have 6 rooms in this database, so feel free to change the URL to any number 1-6. If you don’t know where to make a QR Code just google “QR Code Generator” or go to http://www.qrstuff.com/.

Here is my QR Code for room 3.

 

To link to any database, you can pass a part or a full SQL statement through a URL using PHP _GET. Assign your swamp cooler and id=12345. Create a QR Code with the url http://YourDomain.com/swampCoolerGet.php?id=12345. In your script have a statement:  SELECT * FROM swampcoolers WHERE id = $_GET[id].

Don’t forget to add a form for updating the data to make this perfect for facility management.

 

RevitDB: Edit your model in Excel

22 Mar

In an earlier post, I showed how to edit a Revit model from MS Access. In this post, I will show how to edit your model using Excel.

Here is the model.

The model is exported to an Access database using the RevitDB Link plugin. For more information on how this is done, see my earlier post. Once the database is created, import an Excel sheet and link it to an external sheet. In this example, I have a link called ‘WallsFromExcel’ to an external sheet called ‘Walls-Excel.’

When the ‘Walls-Excel’ spreadsheet is modified, it is automatically updated in the database because of the direct link.

Add a Query to the database that updates the Walls table from Revit with the spreadsheet. Run the query….

……and update the Revit model with DB Link.

RevitDB Link

5 Sep

One of the advantages of BIM is the ability to generate large amounts of data from a model with relative ease. In Revit, the data is there but the tools to create reports are lacking. A way around this has been to export Revit models to ODBC and use a database package to generate reports that are attractive and include fields that Revit prevents us from scheduling.

Autodesk has released a new plugin that takes exporting to ODBC to a new level — RevitDBLink. There are two features of this plugin that will revolutionize the way those of us involved in information modeling will work: the bidirectional flow of data, and the ability to store other data and fields within the same database as the model.

I will demonstrate how Revit allows the bidirectional flow of data by exporting a model to a database, changing wall heights in the database, and then sending these changes back to Revit.

I start with a simple Revit model.

Next, I export the model to a MS Access database using the RevitDBLink and selecting a new connection.

My Access database is now populated with a set of tables from Revit. The image below shows the table of walls.

Comparing this table with the data in the Revit model shows that the unconnected heights are all set to 20′.

Within the database I can change these values to new heights.

These changes are loaded back in to the Revit model by selecting ‘edit and import’ from the DBlink plugin dialog box. The changes are then automatically applied to the Revit model.

While Revit allows for the editing of properties within the program itself, it is no match for the power of MS Access in generating reports and performing advanced queries on the models.

When using the built in ‘export to ODBC’ function in Revit, the model would overwrite the existing database. With RevitDB Link, tables associated with the project, but not stored within Revit can be added to the database and will be preserved every time the model is exported. Furthermore, the data will not be loaded in to Revit. This allows us to include information that is relevant to our project but may not need to be in the model, or may not associate with a specific object.

With RevitDB Link we can now add fields to an object and make it invisible to Revit. For example, when we export a model with rooms, the database will show only those fields created in the model. In MS Access, additional fields can be added to the rooms table. When this table is uploaded back to Revit, the new fields are invisible.

Not allowing fields created in Access to be uploaded back in to the model may seem to be a flaw in the bidirectional functionality, but I see it as a brilliant move on the part of the developers.

Revit files can become very large. While we tend to think that file size is directly proportional to the number of objects in a model, the amount of information included in the model also adds to the size. A simple model with additional fields added to objects can be larger than a model with many objects and no information. Do we really need all those fields in Revit?

RevitDB Link allows for almost unlimited access to our models and the information contained within them. If anyone has any ideas that they would like to see implemented, post a comment.