Archive | Database RSS feed for this section

Graph Databases II: Routing

18 Aug

I wrote a previous post on graph databases that did little more than add some nodes and a relationship. The post required me to install neo4j and python py2neo. It was simple, but it was a start. I have moved on to other things since then, most notably Go. But I have found myself coming back in a round about way. I recently wrote a plugin for Open Trail Data in Leaflet. Trails are a network but the data is really just lines. I thought if we add nodes at the starting points, intersections and eds of the trails, we could use them for routing. I set off to work.

The Data

The first step was grabbing a simple network. I used the Open Trail Data dummy data. I was going to draw nodes then import them in to a graph database, but I decided to shelve that for later – when I know I can route. The data is drawn in the image below.

Open Trail Data

Open Trail Data

The Graph

Next, I needed to create the graph database. I started with all the endpoints of the lines and then added nodes to the intersections. The code below shows how I created two nodes and a path between them.

one = Node(“trail”,id=”1″,junction=”1″)
oneint = Node(“trail”,id=”1″,junction=”1,2,3,4″)
p=Path(one, Rel(“connects”),oneint)

The end result was a graph database as seen in the image below.

Finished Graph Database

Finished Graph Database


Now it was time to route. I do not have weights or costs on the edges. I just wanted to see if I could get from one point to another. I chose node 3 to node 6. They are both on the left. I like it because there are two possibly ways to go. To route, I used the following query:

results=graph.cypher.execute(“START beginning=node(22),end=node(32) MATCH p = shortestPath(beginning-[*..5000]-end) RETURN p”)

The nodes are 22 and 32 because that it the internal ID assigned by the database. My result is a path:

(:trail {id:”3″,junction:”3″})-[:connects]->(:trail {id:”1″,junction:”1,2,3,4″})-[:connects]->(:trail {id:”1″,junction:”1,7″})-
[:connects]->(:trail {id:”7″,junction:”7,6,8,9,12″})-[:connects]->(:trail {id:”6″,junction:”6″})

This tells me the database is going from node 3 to the intersection of 1,2,3,4 to the intersection of 1,7 to the intersection of 7,6,8,9,12 to the end node 6.

You can see the nodes by using

resultPath = results[0][0]
for n in solved:
   print n

Now I know all the nodes required to get from one point to another. If I added (lat,long) values to each node, I could have drawn the path on a map.

I will put this in my pocket for later. I am sure it will come up again and I will be better suited to solve a problem. In the meantime, I think I will work on figuring out how to get a standard data format in to the Database – shapefile or geojson?

Data and Design

8 May

If you have read any of the posts on this blog, you should know I love data. But what you may not know is that I love architecture, design and a good sketch. I spent five hours getting Study For The War Coffer by Eugene Delacroix tattooed on my chest.


Often these two worlds collide. I came across a tweet today:

Mindlessly drawing with data? How dare she. I once thought it a good idea to write computer code that could read an architectural program and develop the floor plan automatically. While I still favor some of this thinking, I have had to think it through. And slowly I have come out against it, and I have sided with Tara on this issue.

There is something to be said for hand drawing. The lines made by a pen, with their varying weights, show movement in a still image. There is something beautiful about them. About the process of sketching. Freely moving your hand across the canvas. The AIA had a podcast on Didactic Drawing that really brought it all home for me. On a computer, scale can change. You can draw a hundred foot line and based on your zoom level (scale) it could be a millimeter long. On paper, your scale is fixed. The movement of your hand across the page lets you know how long the line is.

I am not against BIM. But without pre-sketching designs, these program make it easy to create boxes, squares and overall bland buildings, to draw without a set scale, to fully understand and feel the building you are creating.  To design with data is an idea I am still deeply attached to. But I think we walk a fine line between letting data inform design- on how people use buildings for example – to creating the design for us – as in my program example earlier.

Applications like Revit or Grasshopper make it east to start with a simple form – a box – and twist, pull, rotate and skew it to come up with a whole host of possible forms. The results are soulless – though some look really cool. I do not see the art in it. If we are just going to feed some data in to a model to generate a form and say “look at this cool form I created from using the coordinates of all tweets that had the word Gehry in it” then we might as well give up – though I find these kinds of experiments interesting.

Data is, of course, valuable for facility maintenance. I also find value in data on movements of individuals within buildings and with modeling designs for things like airflow, heat, sunlight, etc. These are the kinds of data that can inform design – or confirm that a specific design is a functional design.

I do not want to live in a City full of bland buildings, just as much as I do not want to live in a world full of monuments to the architect that are outrageously out of context. There needs to exist a balance of the art and the science, of architecture and data. And each needs to compliment the other.



Graph Database and Albuquerque Bus Stops: Neo4j with py2neo

15 Apr

I have been slightly obsessed with the question: “How do you define network service areas client-side on a map.” I know it needs a networked data set and something to do with the Djikstra algorithm (Yes, we could just use an ESRI REST service but there is not one available yet – I will ask the City). After looking at JavaScript implementations of NetworkX, I stumbled upon graph databases, most notably Neo4J.  A networked data set is a graph. Guess what, it has Djikstra built-in, so I must be on the right path. I installed it and added a fake social graph using py2neo. That allowed me to make sure I could do a few things:

  • Add a node
  • Add a relationship
  • add attributes

Now it was time to start with some real data.

My first test was to load Albuquerque Bus Stops for a single route. Here is what I have in my database.

Bus Stops for Route 766. No Relations added yet.

Bus Stops for Route 766. No Relations added yet.

The image above was generated by calling the City of Albuquerque REST Endpoint for bus stops, parsing the response, and putting it in to Neo4J. The image is a view from the DB Manager. The code to do this is below.

from py2neo import Graph
from py2neo import Node, Relationship
from py2neo import authenticate
import urllib2
import json




for x in reply[“features”]:

Notice there are no Relationships! This is crucial if we will ever walk the network. I have manually added on, seen in the image below.

San Mateo links to Louisianna.

San Mateo links to Louisianna.

The code for this is:



I need to think about how to automate the relationship creation based on stop order and direction (there are stops on both sides of the street). Then, I will need to figure out how to make a node have relationships to other routes. For example, many stops are connected to the 777 route and I do not want a separate node for each. I want one with a property showing routes.

Well, a start to say the least. It has been fun learning about graph databases and if GIS doesn’t interest you, you could map your social network and walk it.

.NET WEB API: REST Endpoints with C#

2 Mar

I have posted here and here on using MVC 5 to edit a database with no code but as much as I like MVC 5, there are times that I just want a service and I will code up a front-end from scratch. This also allows anyone else in my organization to use the service as they wish as well. In this post, I will walk you through using the Web API to connect a database table as a REST Endpoint and allow you to view records, add a record, modify a record and delete a record.

The Steps

First, create a new ASP .NET Application



Choose WEB API


Right click on Models and ADD ADO.NET Entity.


Choose the first option.


Enter your database and connection properties. You can then pick the database you want to use.


Select the table or tables you would like to make available.


Build the project. If you do not build at this point, you will get errors in the next steps.


Right click on Controller and add a new scaffolding item.


Pick the Web API 2 controller using Entity Framework.


Select the Model (Name chosen when you brought in the table). Also pick the content class you used in the first steps. It is a drop down and if you pick the wrong one you will get an error. Just retry choosing a different one if you can’t remember what you named it.


Launch the application in Chrome and go to the URL for your controller. The default is http://localhost:xxxx/api/controllername. Notice I am getting XML returned. This is not what I want.


Under the application start folder, open the WebApiConfig.cs file. Add these two lines:

var appXmlType = config.Formatters.XmlFormatter.SupportedMediaTypes.FirstOrDefault(t => t.MediaType == “application/xml”);



Now browse to the URL again. JSON!


You can also add the primary key at the end of the url to get back a single record.


I will use the DHC Chrome plugin to show you how to make calls to the other endpoint not using GET. This is a fast way to test without having to write our front-end. POST to the URL and add Mr. T.

{“id”:5,”name”:”Mr. T”}

15In our application, we can see Mr T was added.


Let’s use PUT and edit Mr. T using his other name: B.A. Baracus.


It has been edited in our application.


Now let’s DELETE Mr. T.


He has been removed.


No code and we can view, add, edit and delete records in our database. Write a front-end using AJAX calls to each of the endpoints and pass a string representation of the object and you can do the same thing.

A note on posting to the endpoint.

Create a JavaScript object like Mr.T.

var mrT={“id”:5,”name”:”Mr. T”};

Now, to send it, you need to make it a string.


This will allow your application to work properly. I made the mistake of trying to use parameters in the URL and that was a no go.


View and Edit Revit Models: Add a Filter Using Code

17 Feb

I have recently shown you how to view and edit Revit models on the web without writing any code. I have also shown you how to filter the views using a very little SQL in your database. In this post, I will show you how, in a few lines of code, you can create a search box to filter the results in your pages.

Application Layout

In an MVC application, you have a model, controllers and views. As explained in an earlier post, The model holds the data from the table or tables selected. The controller sits between the model and view performing actions and responding to events which are sent to the model or passed to the view. The view is what you actually see when you load the page. the image below shows the layout of a simple Revit MVC Application.


I have a Revit mode, controllers for Rooms and Walls and views for the Rooms and Walls. There are separate views for each of the CRUD functions. There are other views, models and controllers that are created by .NET by default. Feel free to ignore these at the moment because we will not be using them.

To add filtering to our application, we will need to modify the Rooms View and Controller.

Rooms View

There are several views in the Rooms folder, but for our application we want to modify Index.cshtml. This is the view that returns the entire model – list of rooms in our database.  You can see in the code that the view creates a table with all the properties for each room in our model.


We want to limit the number of items in the Model returned from the controller using a search box. Let’s add the search box to the view. As in standard HTML, we can use a textbox and a button in a form. With MVC, however, we are using the Razor view engine so the code is slightly different.

@using (Html.BeginForm())
Find by name: @Html.TextBox(“SearchString”)
<input type=”submit” value=”Search” />

The code above should make sense if you are familiar with HTML. The only thing that stands out are the lines starting with the @ symbol. These are variables/expressions in Razor. The above code will create our search box.


Rooms Controller

Now that we have the form, we need to create the code in the controller that will handle the request. The first ActionResult in the code is the Get Rooms function. This is the code that returns the Index.html file we see in the view. The code below will make our search box work.

public ActionResult Index(string searchString)
var rooms = from r in db.Rooms
select r;
if (!String.IsNullOrEmpty(searchString))
rooms = rooms.Where(r => r.Name.Contains(searchString));

return View(rooms.ToList());

when we browse to the Index.html page for the first time, we will see all the rooms in the database. This is because the string searchString is empty. So rooms is equal to the contents of the entire database.

var rooms = from r in db.Rooms
select r;

Once on the page and having entered a value and clicked search, we have requested the Index.html page again, but this time with a value for searchString. Now the controller populates the value of rooms with only those records where the string is in the name.

rooms = rooms.Where(r => r.Name.Contains(searchString));

The function always ends by returning the View (the one with the same name as the controller) and passes it the values of our rooms variable – either all of them or a filtered list.

the images below show rooms filter by the word Master and even by just two letters. The code above uses Name.Contains not Name.Equals. this allows us to get results without exact matches.



This little bit of code has greatly improved our application. The code can be modified to search on additional fields and added to other views – like the Walls Index.cshtml view.


Schedule Revit and Non-BIM Data in Excel – Live

4 Feb

There is a plugin called from Ideate called Sticky that looks like it could be useful, but after watching the video I have come to the conclusion that this is an awful idea. The plugin allows you to bring “non-BIM” data in to Revit. From what I can tell, it provides a live link to an Excel spreadsheet in your Revit Project. This means you can drop the schedule on to a sheet and it will look nice – not like taking a screenshot or other workarounds. The part of this that irks me is that there is no link between the non-BIM data and the data in the linked spreadsheet. And, how much space is this link taking up in my model? I do not need my models to be any larger. Why not create a schedule with a live link between non-BIM data and data you have in a Database? Then you can PDF it and add it to any sheet you want. In my last post, I showed you how to create a view that had Revit data and data joined from another database. In this post, I will show you how to schedule them in Excel.

Get the Data

Open Excel and select the Data tab. Then choose From Other Source. You will be prompted for the database connection. As we have been using SQL Express for the previous posts, that is what I will stick with.

Other Source

When you click next, you will be able to choose the view you created in the previous post.


Click through until completed and place it at cell a1. You will then have a table in Excel of your data.


I know have non-BIM data merged with BIM data straight from Revit. It is not as pretty as the forms on the Ideate video though is it?

How to Format the Sheet

The data comes in as a single table. The rick to using this data with your existing forms and formatted documents is to link it. When you import the data, do so on a different sheet in your workbook. For example, you probably created your perfectly formatted schedule component on sheet 1, so bring the data in to sheet 2. Then you can copy and paste a link from a cell in the table to the correct cell in your schedule. Below I copied a link from cell c5 to h5. Now the data is exactly where I want it.


Hey, You Said Live?

The data may not be live at the moment, so let’s check on it. Select the properties of your data connection and make sure you enable background refresh, set the refresh interval and why not refresh the data whenever you open the sheet.


You can also click the Refresh All button if you don’t feel like waiting for the refresh interval.

So I have a link in cell h5 just sitting there. I will change the data in the database and see what happens. If I change the 1/2 bathroom to “converted to full bathroom” the table and the link will both update within a minute – or I can refresh all when I made the change.


We did it. Now, whenever the Revit model is exported using DBLink, our spreadsheets will all update with the correct information. We can then use Adobe for printing and adding to our Revit sheets.


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.



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.


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.


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 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?