Moving To PostgreSQL (Part 3)

Welcome back!  Last time in Part 2 we configured PostgresSQL on a Linux server.  Now it is finally time to create an Enterprise Geodatabase in PostgreSQL.

Keycodes File

You now need to find your keycodes file.  This file was created when ArcGIS Server was installed on one of your servers.  This file is written to \Program Files\ESRI\License<release#>\sysgen folder on Windows servers and /arcgis/server/framework/runtime/.wine/drive_c/Program Files/ESRI/License<release#>/sysgen on Linux. Copy the keycodes file to a computer that you run ArcGIS Desktop on.  You will need access to it when creating the Enterprise Geodatabase.  Continue reading

Moving To PostgreSQL (Part 2)

Welcome back!  Last time in Part 1 we installed PostgresSQL on a Linux server.  Now we need to do a few things to get it ready so we can create an Enterprise Geodatabase in it.

Postgres User

When PostgreSQL was installed, a postgres user was created.  The postgres user is the default “superuser” to the PostgreSQL database.  Right now the postgres user password is unknown to you.  You must change it in Linux and in the PostgreSQL database.

Log back in to the Linux server and at the Linux prompt, use the passwd command to change the postgres user password.  You might need to use the sudo command with it for it to work.  Continue reading

Moving To PostgreSQL (Part 1)

Goodbye Oracle, hello PostgreSQL!  I’ve decided to get out of the Oracle business and move our Enterprise Geodatabase to PostgreSQL.  I’m tired of giving Oracle lots of money each year.  PostgreSQL is open source and it is very mature.  Though we do not have a dedicated DBA here that knows PostgreSQL, they can learn!  And so can I.  Besides, ESRI supports it and if something goes wrong, I can get them on the red hotline phone!

Over the past few years, I have been testing PostgreSQL on Windows by installing it with our ArcGIS Server installations and using it to store GIS data used in our map and feature services.  I have had only one issue and it was a speed problem when selecting over 10,000 polygons in ArcMap.  ESRI confirmed it was a bug.  I believe that problem has gone away, so now is a good time to move to PostgreSQL.  But just to be sure, we will be running both Oracle and PostgreSQL in parallel for a few months.

NOTE: To be able to install an Enterprise Geodatabase in PostgreSQL, you must be running ArcGIS Server (enterprise addition) somewhere.  You need the keycodes file that was created with it to authorize the geodatabase.  You will also need the st_geometry.so file that was created when you installed ArcGIS Desktop 10.6.  More on that later.  Continue reading

Error: Failed to connect to database

Have you tried to open a Microsoft Excel file in ArcMap and get the following error?

Error: Failed to connect to database. An underlying database error occurred. Class not registered.

I used to be able to open Excel files and now it stopped working.  What happened?  Well … our computers were recently upgraded to Microsoft Office 365 and when that happened it removed an important driver that ArcMap uses to open Excel files.

What to do?  Head on over to this ESRI Technical Support article and install the Microsoft Office system driver.  Don’t worry if you were using a version of Office other than 2007, the driver will work for you.  It fixed my problem!

Road and Aviation Noise

While helping out someone that was looking for fatal accident data, I ran across a cool map service hosted by the US DOT Bureau of Transportation Statistics.  Looking at the REST endpoint for their map services, I found the National Transportation Noise Map.  The service description reads:

The National Transportation Noise Map is developed using a 24-hr equivalent sound level (LEQ, denoted by LAeq) noise metric. The results are A-weighted noise levels that represent the approximate average noise energy due to transportation noise sources over the 24 hour period at the defined receptors. This map includes simplified noise modeling and is intended for the tracking of trends, it should not be used to evaluate noise levels in individual locations and/or at specific times. https://maps.bts.dot.gov/noise/

If you click on the link in the description, it will take you to a document that outlines how the noise levels were collected and processed.  A few points about the data:

  • It is a simplified noise model to track trends
  • Flight operations are averaged into a single average annual day
  • Military operations were not included unless they were at a joint-use or commercial airport
  • Helicopter operations were not included
  • Flight track info was available for some airports for the modeling, and for the ones that did not have any flight track info, a straight-in and out procedure was modeled
  • For the road noise model, average annual daily traffic values were used in conjunction with vehicle types and speed, distributed evenly across 24 hours
  • The noise level cutoff of 35 db(A) was used

Adding the image service to ArcMap displayed this in the LA basin area:

noise1

I then changed the color ramp so high noise levels were red and low noise levels were blue.

noise2

Pretty cool … unless you live near LAX!

Extracting MORE Features from Map Services

Back in August 2015 I posted information about how you can extract features from a map service.  Since then, I have had many contact me about modifying the code so it can extract features beyond the record limit set in the map service.  So today I decided to work on one that does!

To test the script, I headed over to the map services provided by the State of California GIS.  Specifically, the one for wildfires:

http://services.gis.ca.gov/arcgis/rest/services/Environment/Wildfires/MapServer

When you scroll down a quick look will reveal that there is a maximum record count of 1000:

extractpt2-1

Continue reading