A #TechGivesBack Story: Hurricane Florence NCDOT Road Closure, GasBuddy Navigation App

Whether you’re a fan of Muhammad Ali as one of the Greatest Sports Figures of the 20th Century or as a Symbol of the Civil Rights Movement, he did once say that “Service to others is the rent you pay for your room here on Earth.” Mother Teresa, the Catholic fellow Albanian Agnes Gonxha Bojaxhiu, and Nobel Peace Prize Winner, on the other hand was of the opinion that “It’s not about how much you do, but how much love you put into what you do that counts.”


The Why!

ODTUG‘s main offices are located in Wilmington, NC which means that the families and communities of more than 11 of the extraordinary people we count on to support our amazing user community and who go above and beyond to organize our Kscope conference were going to be impacted by Hurricane Florence in the wake of a pretty normal Sept 14 weekend for the rest of us, but far from normal for them!

I contacted some of the ladies to check how things were looking right before the hurricane while 1.7 million people were required to move in evacuation zones. Two days after the hurricane, I checked in with Haleigh on what the situation looked like. This is what she had to say: “Wilmington is a mess, no power or gas and no way to get out or in. So so glad that we evacuated, I can’t even imagine being there with a little one! We are in Greensboro and safe, minimal damage to our home. Unfortunately not the same for everyone else at YCC/ODTUG.  We are a strong team and community and will help everyone get through this.”

You now have the chance to give a monetary contribution in the restoration efforts of the houses of 2 members of the YCC/ODTUG staff impacted by Hurricane Florence!

Last year Monty Latiolais, former ODTUG President, shared his efforts in using his technical skills to help during Hurricane Harvey. I was impressed! I always wondered how we could engage even a few of our thousands of members to participate in such worthy non-profit causes where their technical, big data skill set is really needed. So I reached out to our 4 communities to see if anyone would be interested to help out the families and communities of the individuals who support ODTUG with their constant dedication. This was a new initiative that I had no idea how it was going to evolve. All I knew is that I had to try because it was a need that ODTUG experts had the ability to help out with.

Related image

Flooding caused by Hurricane Harvey in Southeast Texas. Credit: Air National Guard photo by Staff Sgt. Daniel J. Martinez.

Right at the time that Hurricane Florence impacted North Carolina, I was following the updates from the Call For Code initiative. Their goal is to engage developers to come up with innovative solutions using technologies like AI, Blockchain, Cloud, Open Source etc, to help in natural disaster preparedness and post disaster efforts. It is a multi-year global initiative serving as a rallying cry to developers to use their skills to drive positive and long-lasting change across the world with their code. It was fascinating to see how many developers around the world responded to the Call for Code! I tried to engage some of our members, but since this was a new idea that was not formalized through an existing ODTUG initiative it was difficult to get others on board without giving them clear directions or a familiar use case they could relate with.

And then Hurricane Florence happened, right at ODTUG’s home base in Wilmington, NC. In cases of natural disasters, there’s not much time to lose, there’s not much time to think about if your idea is going to fail or be welcomed. You have to act quickly. We had the information from the ODTUG ladies, we knew the needs and we came up with the precise steps on what we needed to implement to be of assistance. See below tweets for the details.


The Who! 

Now we needed to get the right people on board to transform the vision into a useful reality: tech experts with the right skills, who had the time at that precise moment, who were willing to help and spread the word. It’s easier said than done, especially on a Monday morning when everyone has no space in their mind to even think about such out of the blue, unfamiliar, uncertain asks. I was organizing a 2 full day Mini Kscope event at GE that week where I had to present 2 sessions and coordinate all other presenters!

The East Coast Oracle code conference was going on in North Carolina that same week, so I was hoping that at least some of them would be willing to help out.

And then Jayson Hanes joined in! Call it a pure stroke of luck! He had the experience, the skill set and the TIME to help out at that exact moment! How did that happen??? While, on a Monday morning!! I was contacting most people that I thought could be of help, I thought of Jayson just because he seemed like a nice guy when I first met him at Kscope18 at a Fine Liquor Tasting party organized by Opal Alapat, Kscope18 Conference Chair. Given the case that I have a very low alcohol tolerance, I’m not sure how I ended up in that event, but looking back now the reason seems pretty obvious. I had the pleasure of briefly chatting with Jayson and his wife Andrea, both Oracle APEX community members and somehow we ended up following each other on social media.

You can see Jayson and his wife Andrea in the right corner. Opal had once shared an article highlighting him as a UAV Aeronaut since he has a passion to fly drones!

Meanwhile the North Carolina Department of Transportation was warning against GPS usage because GPS systems didn’t display real time NCDOT road closure data. From what you can see below the situation was pretty critical.

Just at the time Jayson Hanes came on board, Adrian Png joined us as well. Adrian was awarded the ODTUG Volunteer award last year. I was humbled to receive the same award at Kscope18 so I had a feeling that he would be as helpful as I would be in the same situation. He was beyond helpful! Adrian created the Florence #TechGivesBack Gitter group to collaborate and coordinate among all volunteers. He came up with our hashtag as well! Along with Monty, he also helped in getting us an APEX Cloud instance provided by the INSUM Solutions team!

On Day 2 Alexis Galdamez joined our efforts! He wasn’t an ODTUG member (he will be now), but he answered our call for code anyway! He played a very important role in creating the alternative route directions and visualization. If you’d like to see Jayson and Alexis in action you should join the Gitter group and see what a great team we formed without having worked together before, without knowing each other’s styles before, all because we were committed to giving back to the best of our abilities.

There were so many other people that landed a hand during the whole initiative either by giving us feedback, spreading the word or encouraging us. Kudos to some special people that didn’t let me give up on this initiative when initially almost no one was answering the Call for Code! Everything mattered and we thank you!


The How!

I’m giving the storytelling torch to Jayson Hanes in this section. This is how we did it!

There was a vision to create an application that could pull together road closures and open gas stations on a map. The data was “out there”, but some of it was only available on spreadsheets. We needed a platform to develop this all on, and components that we could bring together to make this all work.

Oracle APEX 18.1 was chosen as the platform – but we needed a work space on an Oracle Database instance to both develop on and host the production application, that could also be publically accessible on the internet. Somewhere in there Adrian volunteered to create our Gitter app, INSUM provided us an Oracle APEX Cloud instance, Neviana created a Google Sheets doc to keep track of requirements and the status for the initiative!

When Neviana approached me, I was ready to jump in. Although I have never worked on a project with remote data sources for a map, I had used the Google Map Platform and API a few times during my many years of development. APEX 18.1 was recently released and it supports super-easy REST endpoints as websources for reports. This would come together quick! However, as things progressed and the puzzle pieces were figured out and arranged, we encountered several hurdles:

1) I was developing on my own personal instance of APEX 18.1 that is running on a DigitalOcean droplet “in the cloud”, with Oracle Database 11gXE (an OXAR build). This meant that we didn’t have access to the new features of Oracle Database 18c, and that we couldn’t actually configure a REST endpoint using websources over https (due to a known SSL wallet bug in 11gXE).

2) We wanted to pull this data from NCDOT.gov and GASBUDDY.com REST endpoints (urls), and without having to do a lot of code. I was REALLY bent on wanting to use all the new APEX 18.1 features! To resolve this need, Adrian Png turned me on to a node.js application that he created called SUEZ which behaves as a website-proxy, which I could then use to at least manage a working prototype with the websources.

3) NCDOT.gov only has around 1,000 incident records, which isn’t really a lot of data, but we decided to pull the data down on a schedule to a local table to improve performance. Our first few revisions of the application were doing live websource queries, and caching was enabled, but it didn’t seem like it was caching and was slow. The local table method just worked better.

4) The GASBUDDY.com data couldn’t be “all pulled down” to a local table. Their REST endpoints were very narrow in scope for each query, allowing requests such as “open stations within X miles of location A, B”. I ended up implementing the GASBUDDY data exactly this way, once we figured out how to do it of course.

5) I thought it would save a lot of time and effort to use an APEX plugin that Jeff Kemp made for a Google Map report — it seemed to have all the features we were after! It worked extremely well for a normal SQL query — but the main query is actually a UNION ALL against the 2 datasets. One was from a local table, the other was JSON data from a REST endpoint. Ultimately, neither websource could be used in real-time with this plugin directly. I had to seek out other methods of creating functions that could pipeline the data from the JSON data, which took time.

6) The whole user experience wasn’t really clearly understood. The application had to evolve — the first few iterations had select lists to select city, county, station status, type of event, etc. Too much for the user to juggle. It had to be even more simple!

7) We wanted to be able to geolocate the user’s location and allow them to set a destination. I opted to simply put them on a map as a “from” and enable them to click the map anywhere which defined the “to” — easy! — but, it had to be more useful. The need became the ability to type in an address for BOTH. This created a new challenge. Again, I chose to find a plugin and not have to re-invent the wheel, which lead me to INSUM’s google address lookup plugin for APEX. Unfortunately, it took time to realize that it wouldn’t work well on the same page as the map plugin (code clashing somewhere). I took a little time to see if I could make them work together, but it wasn’t an effective use of time. So, I opted to use modal dialogs!.. which in and of themselves presented other challenges within the sphere of how the google maps events work, and how the maps plugin events on the page interacted with variables and so forth. It really wasn’t easy to coordinate!

8) The concept that we were after of “show all map markers within X miles of a route, along length of the route” just wasn’t practical. The software would have to iterate over each point on the route (each turn or every x miles) and query each dataset, performing math on the latitude and longitude the whole way — a very expensive and time-consuming process! We decided on start point, end point — get the distance, and draw a circle around the center point of the two. It isn’t perfect, but it seems to work well enough to help include most alternate route options.

9) The complexity of the app grew, and we discovered that these “timings” or events were really not working very well as implemented. There might have been a better architecture to approach this solution with, but what I arrived at was what we had — we were running out of time for it to even be useful — and it just didn’t work well as I felt it needed to. I decided to stop using many of the map plugin features and opted to manually control some of the events. It seems to be working much better.

10) During the whole process, we tested extensively. Android, iOS, PC, Mac, Chrome, Firefox, Safari, etc. It all worked well — the google platform is just really solid. HOWEVER, on the go-live day we realized that this latest decoupling of the maps plugin, with the new manual events, was actually NOT reliable on some iOS. We don’t know why. It’s a work in progress to resolve, but for now, it only seems to reliably work with Private Browsing. Firefox would be the best browser.

11) From a usability POV, if you set a from, and a to address, and THEN decide to click on the map to set a new TO location, the address remains that you specified before. This can be cleaned up, so it’s more of a to-do than a bug.

Neil Fernandez and Jeffrey Kemp, the developers behind the Google Map APEX plugins that we relied heavily on have now volunteered to have a retrospective review on how we can standardize this solution for instances of other natural disasters in other parts of the world.

Back to Neviana to wrap up the story!


The App!

What did we deliver in the end? In this section, Oracle APEX & Oracle DV join forces. Along with the app I wanted to visualize the NCDOT dataset that we were using. There were 365 roads across North Carolina that were closed during a ~1 week period. And if the flooded road images at the beginning of the post weren’t enough to conceptualize the vast impact Florence had on North Carolina, these visualizations of NCDOT data can give you another perspective on this weather event.

Florence Hurricaine Story Board
Viz. 1: How many roads per county were impacted. 365 total roads were closed due to flooding across all NC Counties at some point between Sept 14 – 23.
Florence Hurricaine Story Board1
Viz. 2: 365 roads closed in North Carolina at some point between Sept 14 – 23, no. of incidents per each road highlighted in blue.
Florence Hurricane Story Board4
Viz 3: Counties that were impacted the most by road closures at some point between Sept 14 – 23
Florence Hurricaine Story Board3
Viz. 4: Cities that were impacted the most by road closures at some point between Sept 14 – 23

 

The Hurricane Florence NCDOT Road Closure, Gas Station Navigation App is a 3 in 1 solution that uses the below verified data sources/technologies to provide NC residents the latest and most accurate information on road closures, open gas stations and alternate routes to guide navigation around flooded areas.

Data Sources Used

Technologies Used

This app was built by Jayson Hanes, Alexis Galdamez, Adrian Png, Neviana Zhgaba and supported by many other members of the Oracle Developers Tool User Group – ODTUG, as part of the #TechGivesBack Initiative: Experts volunteering their technical skills for non-profit worthy causes for social good.

Known Issues/Limitations:

  • This app does not attempt to show routes that are free from incidents – Google Maps is used to display routes between 2 locations with the latest NCDOT Road Closure Incident markers and GasBuddy Open Gas Station Data visible. The user will need to review all routes and make an informed decision on the best route to use that doesn’t display Road Closure markers.
  • There is a limit of 500 combined data points for closed roads and gas stations.
  • On some iOS devices, the actual map route only seems to be visible and/or render reliably with Private Browsing enabled.
  • On desktop PC’s, we have heard that the Firefox browser works best.

Navigation App

The NCDOT Mobility Program Manager & the main Traffic Operations Engineer of the Transportation Mobility and Safety Division of the North Carolina Dept. of Transportation reviewed the app and gave us the kudos for thinking of such a great idea! It would have been great to have this app ready right after the hurricane so that more people could have gotten the help they needed to navigate the flooded NC areas.


The What Next?

This is only the first step towards the #TechGivesBack initiative for ODTUG. Hopefully we can build a more structured program with clearly laid out steps so that more people across all ODTUG communities can find ways to participate with whatever availability & skills they have in a more relaxed & non time constrictive timeline. And we already have volunteers willing to help out!

I was recently asked to describe ODTUG in 10 words or less. This was my take on it: Fun Crowd of ORACLE DISTINGUISHED TECHNICAL UNDENIABLE GEEKS – That is ODTUG to Me!

What’s next for our ORACLE DISTINGUISHED TECHNICAL UNDENIABLE GEEKS? Well, if you’d like to volunteer your technical skills for a worthy cause, here is one immediate opportunity initiated by some #orclAPEX community enthusiasts. You can donate whatever time & skills you have!

ODTUG staff is grateful for 1 week worth of effort that some of us had the time, energy and skills to give back. However I’m sure that all the thousands of ODTUG members are even more grateful for this fantastic organization that is committed to constantly add value to all its technical communities!

And don’t forget to register to attend Kscope19! It will be even better than Kscope18!

By

Neviana Zhgaba, Product Manager, Oracle EPM at General Electric

Jayson Hanes, Solutions Architect, Oracle DBA & APEX Developer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s