News & Guides

Looking for something in particular?
Search us! If you don't find it here, remember to share it with us when you do.

My ArcPython SQL Adventure

In ArcGIS Desktop, ESRI, GeoBase & Drafting, Microsoft SQL, Python | on June, 01, 2018 | by | 0 Comments

Hope you are having a wonderful Friday.  Just wanted to share some of my experience while working on a recent python script.  The story kind of goes like this….

There I was (deep in the coding jungle) trying to figure out how to extract data from a database without using something too crazy or off the wall.  I talked with one of the local developers initially to get some background as to how things were currently being done.  For some scripts the developers are using a library called pyDbUtils to parse their SQL queries in a close to native SQL.

–Example of pyDbUtils—

 

 

In this example, you will see the SQL command being setup as a variable sqlcmd with the full SQL statement that the script wants to execute.  {0} and {1} are variables that are filled by cadTblPrefix and user respectively found at the end of that line.  The result variable is defined after pyDbUtils.RunSQLStatement and its arguments are run.  Arguments are the variables and configuration (True).

It’s a great solution, but I didn’t want to have the script I was writing rely on anything other than what was provided out of the box from ESRI.  I opted to try something a bit different using only what ESRI had available within its ArcPy library.  After some research, this is what I ended up on the first attempt.

–Example of ArcPy 1’st Attempt—

In this example, I am using the arcpy.da.SearchCursor command execute my SQL query.  There is some argument that a cursor is not as efficient as native SQL.  This is entirely true.  They are also a huge resource hog.  Using SQL allows for the database to perform set based operations whereas cursors operate in a row based world.  In this example, I am telling python to with a cursor, access my data_source (Database Connections\\Some SDE.sde\\Schema_Name.Table_Name) to access the fields indicated in brackets, eg. Data_Owner, etc.  For each output, for each row, to take the output, row[], and low it to a corresponding variable.  The # within the row indicates what the column number is.  0 being the 1’st column.  In this case, the output from the Data_Owner field would be loaded to the target_owner variable.

What I discovered was that it was clunky and because its through Python it makes it 10 times harder to get the syntax just right, especially since I am not always working in Python.  I couldn’t pull it over to SQL and test it against anything without really re-working the code and…it was frustratingly inconsistent in results.

After a bit more research, I finally stumbled upon the hidden gem…a way to use ArcPy with native SQL without having to resort to an outside library for help.

–Example of ArcPy 2’nd Attempt—

 

As it turned out, ArcPy was hiding ArcSDESQLExecute from me the whole time.  That one command alone allows you to call your db_connection which in this case was something like this Database Connections\\Some SDE.sde.  After that, you specify execute and the argument (check_pending_updates_sql) you want it to run and viola it works every time.  The best part of all is, I can test it in SQL with very few changes, it pops right into Python.

–Example of ArcPy 2’nd Attempt w/ Query Built In—

This example has specific items its looking for in the database and only will kick out 1 result.  This query is looking to the database for what SDE connection it should use for a specific target database, database type (Production, Staging, or Test), and who the data owner or schema owner is.

Long story short, if you find yourself writing anything in Python for ArcGIS and need to run a SQL query through it, consider using ArcSDESQLExecute rather than pyDbUtils in the future.

Job Postings

In Career, News | on May, 24, 2018 | by | 0 Comments

Any EA’s getting out of active duty or already out and would like a GeoBase job…There are some openings:
Mountain Home – 1 slot
Tyndall – 2 slots
Minot – 1 slot
Wright Patterson – 1 slot

Posting can be found at www.woolpert.com/careers/careers-overview/

Then select Job Postings

Preparing For Civilian Life as a GIS [Fill In The Blank]

In ESRI, Gee Whiz, GeoBase & Drafting, Microsoft SQL, News, Spatial Databases | on April, 04, 2018 | by | 0 Comments
In the event anyone was curious, this is what a local government (city level) looks like on the back-end of their GIS program. I didn’t include the web servers in this diagram as that’s a whole other ball of wax that I am still working on.  Where I am working, there are 22 databases that I manage/maintain. These databases run on Microsoft SQL rather than Oracle. In some aspects it is a hybrid environment as we have feature classes and views that were created via ESRI, but we also have spatial views that were created via pure SQL.
 
So…as some of you consider what your future could be in the GIS world, remember that you need to CYA and make sure you get a broad exposure to all the technologies that make a GIS program what it is. Make yourself competitive for the positions you are applying for.

Hurricane Irma OpenStreetMap Urgent Mapping Needs

In Career, Gee Whiz, GeoBase & Drafting, News | on September, 10, 2017 | by | 0 Comments

If you are looking for a great opportunity to volunteer your mapping skills for a worthy cause, please check out OpenStreetMap’s Tasking Manager and lend your knowledge to those cities and counties who need your support.  OpenStreetMap is a huge online crowd sourced repository of spatial data you can use for your day-to-day life as well as your work life.  Data provided here helps countless mapping needs ranging from apps on your smart phone to government organizations.  The best part of all, its open data, ie. totally free so long as you credit the who provided the data, ie. OpenStreetMap and the contributor.

Help them out!

Hurricane Irma & Jose Mapping Support

In Gee Whiz, GeoBase & Drafting, News, Spatial Databases | on September, 08, 2017 | by | 0 Comments

For any of our current, retired or former 3E5X1’s who are stuck in EOC’s supporting Hurricane Irma and potentially Hurricane Jose (now category 4), who need a crowd sourced damage plotting system or even a failover for your own systems, please drop me a line at mapping@theexpeditionaryengineer.com.

We setup the Designated Tobacco Area map system for Joint Base Elmendorf-Richardson as a test of how such a system could work.  Through the test we determined that we could publish a system that would work on both computers and smartphones that could collect data from the field.  The proof of concept will allow us to build you in about 30 minutes a full-blown mapping system that can collect multiple inputs from thousands of users.

Should you need any other help we can give, please use the same e-mail above.

Mini-Training: Monthly Checks & Maintenance

In Career, Conventional, ESRI, GeoBase & Drafting, GPS, Spatial Databases, Surveying & Materials Testing | on August, 15, 2017 | by | 0 Comments

Let’s face it, we have all been there.  We walk into a brand new organization and have no idea how they have run things or why things have run like they do.  Sometimes it’s for the better, sometimes it’s for the worse and sometimes it really isn’t how you would like it to run.  That being said, here is a slideshow that has some pretty good rules to live by.  Some of these rules will need be adjusted to your working tempo, but for your general run of the mill base, these are rules that will probably cut some anxiety and give your shops some targets to keep up.  The areas of focus range from paper supplies to checking survey equipment (how to).

Monthly Checks and Maintenance

Mini-Training: How GeoBase Works

In ArcGIS Desktop, Career, ESRI, GeoBase & Drafting, Spatial Databases | on August, 15, 2017 | by | 0 Comments

A few weeks ago, it dawned on me that there wasn’t a really simple how everything works together picture for our Airmen.  With that I set out to build a map, pardon the pun, to help guide our Airmen through the layers that take what they see and know GeoBase in their lives all the way up to the HQ Air Force level.  As this presentation would be applicable to any MAJCOM who deploys servers at their installations, not much would need to be changed, however, for MAJCOMs like Air Combat Command (ACC), changes would be necessary as your architecture follows a different flow from last what I heard.  Keep in mind, this doesn’t make PACAF’s program better or more advanced than ACC’s or anything along those lines.  It is simply a different approach to a problem all MAJCOMs deal with.  While I have my particular preferences with how a MAJCOM program should look and operate, I do not control the mission and more importantly, the purse strings.

How GeoBase Works

The Great Smoke Pit Roundup — Continued

In ESRI, Gee Whiz, GeoBase & Drafting, News, Publications, Spatial Databases | on June, 09, 2017 | by | 0 Comments

I have completed all of the modifications to the map and have come up with 2 quick how to guides for facility managers and GeoBase techs alike.  It’s a very uncomplicated system to manage for the GeoBase office thankfully.  I will post the training guides once I have the example site up and running this weekend.  I have to make sure there is a distinction as it wouldn’t be a good thing to have anyone playing with a map that is currently being used for operational needs.

Keep in mind, this type of system could be used for anything really.  You could use it for emergency operations during a natural disaster and solicit information from the public to help identify where you need to direct work crews.  You could use it to show upcoming power outages.  You could use it as a C-2 like function for an Air Show staff.  Yes, security is paramount, but you can easily reach an acceptable risk scenario by working closely with your IA and INFOSEC folks to ensure you only have what is necessary out there and it’s in a reasonably secure environment.

Here are some screen grabs from the final changes that were made.  You’ll notice I changed the icons between the last post and now.  The only thing I have left to do is to add in an “approved” smoking zone.

I’ll be adding the smoking approved zones on to the map by using the following code:

var ctaLayer1 = new google.maps.KmlLayer({

url:’https://—-my url—-/smoking_zone.kmz?rev=1′,

suppressInfoWindows: false,

preserveViewport: true

});

ctaLayer1.setMap(google_map);

This is similar to what I did for the Arctic Thunder Open House map site, only this time I have a KML layer as my zones and XML points being rendered to generate the points and polygons.  I could have shoved everything into Firebase or Fusion tables, but that wouldn’t have worked for our GeoBase shop as they can’t access Google Drive resources from a DoD computer.  It’s a shame considering there are a number of other services that allow large file transfers such as Amazon S3, but I assume that’s even available as it’s not widely used outside of the COM world.

More to come when I publish the example site and the training guides.  Its funny how we publish training guides for even the most simple of tools that someone may use on a day to day basis.

The Great Smoke Pit Roundup

In Gee Whiz, GeoBase & Drafting, News, Spatial Databases | on June, 07, 2017 | by | 0 Comments

**If your installation needs the following product contact support@theexpeditionaryengineer.com**

I have found the most interesting side projects I have ever had the chance to be involved with usually came in association to a conversation I walked into.  On Monday this week I had been talking with our local GIS contractor about issues associated to gathering information from diverse groups of individuals.  The conversation was a result of him having had a meeting with leadership going over changes in the tobacco use policy.  On our installation alone, there are numerous agencies who do not have access to the same network where the GeoBase map server operates from.  Sadly, that left a huge segment of the population unable or incapable of accessing the simple map system he had established to collect and gather spatial data about smoke pits.  As he walked out of my office, he stated in a roundabout way that he wished there was something as simple as Google Maps that could be leveraged to get this data call done.  Something intuitive that everyone could access without an issue.  Something about that stuck with my mind, but didn’t quite click.  Recently I have been pretty distracted getting prepared for a huge life shift so it took a bit for lightning to strike, but when it did….Google Maps became reality.

For years, I have owned and operated my own web server(s) and websites.  Sometimes I do things that actually make some extra side money, other times it’s for things like this website and/or other interesting side projects.  Last year, I designed and built the Arctic Thunder Open House website which featured a Google Map that had GeoBase derived data overlayed on top of the map and even included some geolocation capabilities in it.

Here is quick link to the full size version of the map vs. the one framed onto the site:  ATOH Map.

Since the open house completed, I had been mulling over the idea of creating a pseudo C2 type map system that would support the organizers the following year.  A nice/decent way of tracking your personnel and identifying spatial where things needed to happen, ie. more ice, lost kid, tripping hazard, etc.  As I had done the research and spent the time trying to figure out how to do it, I knew there were a couple of ways to go.  I could have went the direction of using Google Firebase, Google Fusion Tables, or MySQL.  There were a couple of other options, but those were the ones I was entertaining of even exploring for the next open house.  There are a few problems though when wanting to work with Google products on a DoD network, 9 times out of 10, they don’t work if you are a regular guy like me.  If I worked in COM, I probably would have a long drawn out explanation as to why I could gain access to things like OneDrive (MS), Google Drive, and others and why the regular guys don’t, however I don’t work in COM so the mystery remains.  I have a feeling it’s a risk based decision more than anything else.  Nonetheless, most of the fun Google tools that make things easier don’t work within our environment so out of the available options, I was left with a MySQL choice.

Now MySQL comes in a couple of different flavors.  Due to some recent decisions on my end, I had a MySQL database that wasn’t capable of storing spatial data the way ESRI or any other GIS based software likes it to be.  I could have made a course correction and fixed it, but then came the other issue of the data connection through the installation’s firewall.  Essentially, too big of an unknown to deal with so there was no point in fixing the database to be spatially compliant.  To overcome the obvious issue, I decided to design the spatial data collection system to collect X,Y data via the Google Maps API.  Add in a simple submission form for facility number and you have yourself a very simple PNE (no Z or D) file that can be imported into ArcGIS if properly formatted.

So, I just realized that it would have been a great idea to have a simple sample map as part of this story or a link to one that is, but I blanked so screenshots will have to do for the time being.  However, if your base needs a similar tool for a short period of time, drop me a line at the referenced e-mail address at the top of the article and I’ll get something running for you in a day or so.

General overview map showing points on the map.

After right clicking, the pin drops onto the map and can be dragged around. When positioned, the end user can update the facility number and click save. Upon clicking save, the x,y and facility number are loaded into the database.

This is how another end user and/or administrator would see the dropped pin after a recent map refresh. If the pin is not required, the user and/or administrator can click delete and it will be removed from the database.

The only thing I am probably going to be adjusting the pins to something along the lines of a pin with a cigarette on it or something along those lines tonight, but all in all, its a great simple tool that gets the job done without a ton of engineering required to make it happen.