Upcoming Sessions at Autodesk University- Part 2


 
Autodesk University is getting close and we are looking forward to it! While we are there, Stephen Brockwell will be presenting two sessions this year and Brockwell will also be in the Exhibit Hall. In the weeks leading up to AU 2017, we will be providing some information about the presentations Stephen is giving and some of the other classes and exhibits we are looking forward to seeing.
 
On the third day of the conference, our session is in the morning and titled “Data Aggregation with InfraWorks and ArcGIS for Visualization, Analysis, and Planning.” In this session, we will be discussing some of our latest work on integrations, specifically focusing on the integration with ArcGIS.
 
In many industries, we talk about “Silos of data” or data that tends to get locked up in a separate database or set of data files that are not part of an organization’s enterprise-wide data administration. You would think that with the invent of “the cloud” or the increase in database capacity that silos of data should be history. But integrating geographic information system (GIS) with 3D model-based design can be a real challenge, especially for organizations that are large and where enterprise databases take significant effort to update and maintain.
 

Figure 1


However, the ability to aggregate data and visualize multiple sources of information is crucial for decision making, especially within municipal organizations who have several layers of data to consider. But let us present you with an advantage – with ArcGIS Online and InfraWorks software, you will have the edge: the cloud-based ability to easily aggregate personal, organizational, and public data previously locked in GIS files or servers. For urban development, environmental planning, or capital projects, this capacity can enhance the value of your GIS data to create rich 3D design models for critical infrastructure projects.
 

Figure 2


In this session, we will demonstrate the use of InfraWorks software to aggregate ArcGIS Online. You will learn how to configure local GIS servers for ArcGIS Online access. We’ll also show you how to configure ArcGIS orthophoto and terrain services for use in InfraWorks. Finally, we’ll demonstrate the process using a specific road-design example from a large North American city.
• Learn how to aggregate public, personal, and organizational GIS data from ArcGIS Online within InfraWorks
• Learn how to save mappings for ArcGIS attributes to your InfraWorks model
• Learn how to link ArcGIS services you have published to ArcGIS Online and access them from InfraWorks
• See a comprehensive workflow for visualizing a transportation plan for a major North American city


Upcoming Sessions at Autodesk University- Part 1


 
Our own Stephen Brockwell will be presenting at Autodesk University again this year and Brockwell will also be in the Exhibit Hall. In the weeks leading up to AU 2017, we will be providing some information about the presentations we will be giving and some of the other classes and exhibits we are looking forward to seeing.
 
On the second day of the conference, Stephen Brockwell will be presenting his first class titled “Citywide Reality Capture for Infrastructure Design Using InfraWorks and AutoCAD Civil 3D”. In this session, we are working with our partner CycloMedia US to show you how street-level imagery can help you visualize real-world data without leaving the comfort of your own office. It sounds like a wonderful proposition – being able to view the exact location of street furniture, take inventory of all the pavement markings and get detailed, high-definition photos of each, as well as take measurements to begin the pre-planning process. And all of this being done from the home office with out having to incur the expense of sending crews into the field to measure and collect information about assets which can be time consuming and costly.
 

 
When you have street-level spherical imagery at your finger tips and colorized LIDAR (light detection and ranging) data at hand, you have the freedom to quickly, accurately, and cost-effectively create photorealistic 3D models for a variety of purposes. Planning for construction become much easier, designing new intersections takes much less time and emergency support and disaster management teams have access to recent pictures and can assess the best way to handle recovery efforts. When you have access to the street-level imagery, regulatory compliance and asset management are easily managed with less costs.
 

 
During this session, we’ll explore the collection process and business implications for street-level reality capture and examine 2 alternatives, such as Google Street View and CycloMedia. We’ll also discuss practical workflows for municipal governments using this data, including design and visualization, with a focus on water asset management and pavement surface markings. Finally, we’ll describe how to effectively integrate these data sources with AutoCAD Civil 3D software and InfraWorks software.
 
Our learning objectives for this session include:
• Understand the business process and benefits of street-level, mobile reality capture at the scale of the municipality
• Understand how photorealistic street-level data and colorized LIDAR can work together to provide a low-cost source of design measurement and visualization data
• Learn the principles of working with AutoCAD Civil 3D to integrate these tools into the design process, including tips on application development
• Learn how InfraWorks can access this data for even stronger visualization and native 3D design
 
We hope you can join us at Autodesk University 2017 in Las Vegas. For more information on this session or attending AU, see www.au.autodesk.com.


Applying New Technology to Assist in Disaster Recovery

Applying New Technology to Assist in Disaster Recovery

 

by: Jessica McCall

In light of this year’s events, we want to highlight some recent technology that helps cities or counties to recover faster in the face of a natural disaster. Brockwell has developed SynCumulus, a web and mapping solution for field inspection of infrastructure damage or outages. SynCumulus is a hosted app and allows the user to connect live over Wi-Fi or cellular network, or use offline in case of an emergency where internet service is unavailable.
 
SynCumulus gives you access to your GIS in a mobile environment, allowing you to assess damage in the field and transmit that information back to the main GIS via built-in forms. Base maps can be provided by Google, and street views and directions are integrated to show visual and test descriptions. Navigation within the map is made easy with the simple icons located on the menus, and with the integration of Google Maps, users can see their location and get directions to the next inspection site or a particular feature.
 
During a disaster recovery period, field crews can use SynCumulus to retrieve images of the infrastructure prior to damage, make notes, and create inspections records based on the field crew’s assessment of the current damage.
 

 

 
Brockwell is also working closely with one of our partners, CycloMedia on an integration of AutoCAD and CycloMedia’s Street Smart™. Street Smart™ is the new interactive web viewer for GeoCycloramas™, and includes overlays and measurement functionality—Street Smart™ and all available data are directly accessed from the CycloMedia servers, so you can easily access the imagery without any changes to your existing IT infrastructure.
 
With street-level imagery, CycloMedia provides control rooms and staff remote visual support for review of infrastructure and measurements prior to natural disaster events. This can be helpful in times of recovery, when it is essential to gain a thorough understanding of the location and damage level so that recovery crews are equipped with all the information they need to proceed with repairs.
 
In an emergency, prior knowledge of the nature of buildings—for example, the number of floors and roofing materials—helps the fire department or recovery crews rapidly deploy optimal equipment. Emergency services can be quickly informed of any potential dangers or obstacles in their pathway to rescue. At Brockwell, we know that access to that information can be invaluable in some circumstances, and we work to make the process easy and efficient when you need it most.
 


Practical Applications of Very Cool Oracle Database Custom Aggregate SQL Functions

Practical Applications of Very Cool Oracle Database Custom Aggregate SQL Functions

 

by: Stephen Brockwell

 
The Brockwell Telecom Module has an elegant, simple model for telecom, fiber and copper equipment locations. In general terms, we have Structures (poles, towers, sites) at which Enclosures (splice cans, comm cabinets, vaults) contain Equipment (muxes, radios, and so on). Cables run between Structures and terminate at Equipment; this organization tends to make information easy to find. From a GIS perspective, the only geographic data is the Structure—the locations and their visualization is obtained on-the-fly from the Equipment located at a site or on a pole. These clean, simple maps are easy to navigate at various scales—and easy to label with customer-specific information from this Structure >
Enclosure > Equipment hierarchy.

 

So, essentially, all the important Equipment is always located at a Structure—a pole, vault, tower, site, or building. In previous versions, we made buildings Enclosures to “keep-it-simple”, and for many customers, this was natural—the sites were the primary means of locating equipment for trouble call or asset management purposes, and most buildings were located at these sites.

 

But we’ve recently embarked on a major project with a large North American utility to integrate their telecom assets with a leading asset management system, and this has required a substantial re-organization of the data. Buildings must be independent locations, fully addressable—and, for this customer, many sites have multiple buildings, sometimes with different addresses (for instance, the operations center and the primary equipment hub may be a few hundred feet apart).

 

So we’ve decided to change the model to treat buildings as Structures. Because the Equipment itself is hierarchical (sites have buildings, buildings have rooms, rooms have comm-cabinets and so on) we must maintain very careful bookkeeping of those relationships.

The Geometric Problem

 
But the more interesting challenge that I want to share with you here is the geometric one. Here are the possible situations we’ve encountered, in general terms:

  1. All buildings must have new locations based on the site or structure that contains them.
  2. Multiple site locations must be consolidated and new building locations must be created for the previous sites.
  3. All sites that currently have no buildings must have new buildings created (this is a consistency requirement for asset management).
  4. A site and a building cannot share the exact same location.

Sites with multiple buildings must appear to be near the “center” of the building locations

 

 

From an Oracle perspective, we need to:

  1. Offset the geometry of any site that has only one building.
  2. Average the geometry for sites that have more than one building.

 

Less or More?

 
Over the years, I’ve learned an important lesson about the Oracle database: Work with it, not against it. Too many application developers write 3GL programs to circumvent the way SQL works by nature; rather than make things simple, they make things more complex and less maintainable. Oracle is one of the most extensible databases on the planet. There is almost always a way to do something more simply with far less code—often using a single SQL statement. This is much better than a 3GL loop that is often more difficult to debug and maintain.
 
This is not to say that a thousand-line query with nested views, multiple hierarchical subqueries and so on is always a better solution. It is essential to find the right balance between maximizing performance, simplicity, and testability, and minimizing cost, complexity, and maintainability.
 
Let’s look at an approach that achieves these goals.
 

Grouping Data

 
The SQL group by clause allows you to consolidate records into summaries. Often, a business requires only simple groups. For multiple telecom sites with the same asset management Location ID, for example, you might want the total number of equipment:
 

 

Sample output might look something like this:

 

Nested Enclosures and Hierarchical Queries

 
I have a few observations for anyone learning or trying to master SQL. Because enclosures are hierarchical, it’s important to recursively visit all of them. Most terminating Equipment is in a vault, in a rack, in a shelf, and, depending on your model, in a chassis. This is the purpose of the CONNECT BY clause, which is unique to Oracle’s flavor of SQL.
 
Recursive queries are crucial for reporting on hierarchies of information: material lists, infrastructure network traces, and, of course, fiber circuit reporting. Writing code to process these hierarchies in JavaScript or Python misses the opportunity to use the inherent capabilities of SQL; the better idea is to let the database handle it and bind Angular or Microsoft WPF controls to the query.
 
For observant followers of standard SQL, almost every CONNECT BY query can be replaced with a nested query using recursive CTE (common table expressions). The following query is somewhat simplified, but it gives you an idea of the fundamentals:
 

 

With the CTE approach, you split the query into two parts: the anchor members (the root of the tree), and the recursive members (the leaves) which result from querying the results of the view. If recursive queries are necessary for your database work—in either SQL Server, Oracle or any other database—get in touch with us.
 

Aggregating String Data

 
In case you didn’t know, the LISTAGG function will concatenate a list of text with a separator—so if you want to preserve multiple location details, remarks, or notes for the aggregated site, you can do that as indicated below in a slightly simplified version of the query we used to consolidate the sites:
 

 

The “WITHIN” keyword resembles the “OVER” keyword familiar to users of analytical functions. This is used to order the textual list.
 
For remarks, for example, you might see a result like this, ‘Site A (Main Site), Site B (radio tower), Survey date 7/08/1998’—the consolidated results for three different locations that now fall within a single site.
 

Aggregating Geometry

 
You may be aware that Oracle has some geometric aggregation functions (have a look at this Oracle link). Some of these would come very close to solving the problem of the average location for our consolidated sites.
 
For example, we could use the SDO_AGGR_MBR function to get the minimum bounding rectangle for all the buildings and previous sites that are to be merged. The center of that rectangle would be very close to the average location. The SDO_AGGR_CENTROID function would be even better—it creates a single point, and that point would be the geometric average. Unfortunately, it doesn’t adjust the offset of the site if that site has only one location associated to it.
 
For this specific case, I decided to use Oracle’s ability to have user-defined aggregate (group by) functions.
 
Implementing a custom aggregate function involves the implementation of a database object type that supports specific interfaces necessary to support aggregation.

These are

  1. ODCIAggregateInitialize()– used to set the initial state for each group (for example, reset a counter to zero).
  2. ODCIAggregateIterate() – used to increment the group result for each row within the group.
  3. ODCIAggregateMerge() – used when parallelizing to merge the results of two or more parallel group operations.
  4. ODCIAggregateTerminate()– used to return the final result for the group.

I won’t provide all the details here, but suffice to say,

  1. ODCIAggregateInitialize() creates a new empty geometry for the group.
  2. ODCIAggregateIterate() keeps track of the average location for the group.
  3. ODCIAggregateMerge() averages the results from any parallel sets.
  4. ODCIAggregateTerminate()returns the result—and here’s where we use a nice trick.

 

What is happening here is simple, but effective—and it shows the power of these functions. All this code does is return the average if there was more than one point. However, if there was only one point for a site, it nudges it north 50’. Any rule here could apply; in fact, our final customer rule is somewhat more complicated. Still, it’s amazing how a programmatic exercise in a loop can be transformed into something as simple as this:

 


 
where the averagepoint custom aggregate function returns the optimized location of the site. A lot of benefit for less than 20 lines of code across the four interfaces.
 

Performance

 
I didn’t talk about performance here, but if you have database performance issues, we can help with that as well. We’ve tuned electric databases with millions of customers and hundreds of millions of features. We’ve tuned telecom databases with thousands of wire centers. We’ve tuned graphic display performance, query performance, and long-transaction performance, whether it was with ArcGIS SDE versioning, Oracle Workspace Manager, or Autodesk Industry Model Jobs. Performance tuning is essential to the implementation of the above processes to be most effective.
 

Conclusion

 
Frank Misurec and I have been working with database GIS since 1990. Brandon Amick is now a five-year veteran of some of our largest and most complex database migrations. The Brockwell team has tuned, partitioned, migrated, and customized Oracle and SQL Server databases in almost every continent for some of the largest utility and public-sector organizations on the planet. If you’re migrating to Oracle 12c, or from SQL Server to Oracle, or from files or proprietary formats to an ESRI geodatabase, or even if you just want to know how to get more value from the incredible reporting and analytical capabilities of your asset databases, reach out to us at support@brockwellit.com.

 


Making More of Map 3D: Integrate to Save Time

Making More of Map 3D: Integrate to Save Time

by: Brandon Amick

Recently, we have been working on projects that involve integrating AutoCAD Map 3D with other products to allow for AutoCAD Map 3D to operate as a “One-Stop Shop” for not only GIS but also for asset management.  With the right customization, data can travel back and forth between AutoCAD Map 3D and asset management software or other software without having to switch between interfaces to input data a second time (…or third time).
 

 
One such project involved integrating AutoCAD Map 3D with Infor Hansen to create and update assets in Hansen as they were being created and updated in AutoCAD Map 3D.  There are a variety of ways to accomplish this and our project involved using the AutoCAD Map 3D API and the Hansen web services.  Since this customer was using an Oracle database backend, we collected data from AutoCAD Map 3D as each job transitioned through various job states to pass along into Hansen.  This allowed the customer to utilize the job states within Map 3D to help them create a stable internal workflow process.  Our work resulted in allowing assets to be created in Map 3D and Hansen simultaneously without any additional effort for a seamless workflow and time saved.
 
Another customer called on us to aid in bringing transformer data from NorthStar Harris into the Map 3D environment for the users to view it.  We worked with the SQL Server database for this customer to set up database links between the Map 3D database and the database used for Harris asset management software. This would allow the user to bring in asset management data about their electrical infrastructure using master detail records created in Infrastructure Administrator.

Additionally, we created links to other software called Savage to load the corresponding Savage URL in the user’s web browser and view the data about a transformer or meter. This was completed using the data available from the Map 3D API to construct the proper URLs.  When everything was finished, the user simply had to click a button on the Map 3D form and their web browser would automatically navigate to the correct Savage URL for that transformer or meter.
 
As you can see there are many ways that Map 3D can be used as an all-encompassing software to handle design, GIS, and asset management data all at once.  It can be as simple as adding appropriate fields to your data model in order to track the data that you need to as complex as full plugins that work within Map 3D to synchronize your data from platform to platform.


News and Events

Autodesk Authorized Consulting Partner

Brockwell IT Consulting is an Autodesk authorized consulting partner with significant expertise in geospatial asset management and IT architecture for utilities. We have a variety of customizable services to fit your organization and industry. Visit our Accelerator page to learn more.

Looking for Training?

Brockwell IT Consulting can offer general application training or customized training for Autodesk and other utility-industry focused applications. Contact us for more information.

Want to join the team?

Check out our Careers @ Brockwell IT.

Contact Us

  • This field is for validation purposes and should be left unchanged.