Operationalizing SDE Subsetting/Layer Registration

Q: How to get max latitude for each given longitude in a time period subset of points as a GIS layer?

The particular dataset we were working with already had X and Y coordinate fields and was in shapefile format.  After attempting to use ArcGIS tools, including those that exposed the limited SQL that ArcGIS offers without SDE, we found that SDE was necessary.  We had already set up a SDE Postgis/Postgres (PG) database, so we could easily import the shapefile. 

First we wanted to create a subset of max latitudes in PG, through the preferred PG interface (ours was pgAdmin III).  Where max is the max subset and data is the original data:

SELECT * INTO sde.max FROM sde.data Where y in (SELECT max(y) FROM sde.data GROUP BY x); ALTER TABLE sde.max OWNER TO sde; GRANT ALL ON TABLE sde.max TO sde;

.  Notice the use of the 'sde.' schema prefix and the permissions queries, which we found necessary (see stumbling blocks below).  We operationalized this by semi-colon delimiting and changing table names where necessary.  This could be made much more efficient by getting a list of tables with the name format given to the tables that we wished to subset, but we were more interested in getting a result than more completely operationalizing at this time.

Next we needed to register these new tables as SDE layers, so that ArcGIS could "see" them.  After this step, the tables behave as normal ArcGIS feature classes.  Before this set, we could not even get properties of the tables, let alone interact with them (an error message would be displayed).  We ran the following command to do this through Windows cmd (SDE bin must be on the Path):

sdelayer -o register -l max,shape -e npc -C objectid,sde -i sde:postgresql:localhost -D sde -s localhost -u sde -p sde -t st_geometry

  .  We operationalized this by repeating this in a .bat file and changing tables names.  Again, this could be made more efficient by using an sde command to get a table list and looping through all tables that had names which matched the max subset table name format.

Stumbling Blocks

1. The most difficult problem we ran into was the "DBMS table not found (-37)" error, which apparently is quite common for a variety of reasons with SDE.  This ultimate came down to needing to make sure the table was stored under the sde database, in the sde schema, and that the table was owned by sde.  I had assumed that making the owner of the database sde would cause new tables to be sde owned by default, but this was not the case.
2. You may need to update your SDE/ArcGIS Desktop to 10 SP3.  This was mentioned in some forums with the above error and with other errors around not being able to see tables in an SDE DB.  To do the update, you need to download/install SP3 on desktop/sde and then run "update database" from the database properties under ArcCatalog.  However, you must create a direct connection to do the update ... update does not work under the normal ArcCatalog Spatial DB connection.  
3. General SDE recommendations:  My rule of thumb is"anything you can do in arcgis/catalog, do there ... all else do in pgadmin".  Also remember to refresh after all steps if there's some result you're trying to see.  pgAdmin and ArcCatalog both need refreshes before showing updates.

ArcGIS on a High Performance cluster: Part 1, Linux

Now that we have our new community cluster running at UD  it's time to learn how I can optimize GIS software for that environment.

The first hurdle is operating system

ArcInfo Workstation used to be a great way to run Esri/ArcGIS geoprocessing tasks on *nix boxes.  However, it seems the last version of ArcInfo Workstation that ran on Linux was 9.1.  I'd contacted Esri about obtaining a copy of 9.1, but apparently it is out of production and they do not have any copies of the software that they'd be able to send to me.

So basically the workstation/desktop route is out not available at this time.  But there is more than one way to skin a cat: ArcGIS Server 10 (AGS) runs on Linux.

I've recently seen documentation which suggests geoprocessing models can be leveraged by publishing them as geoprocessing services through ArcGIS Server.  I have heard at the Esri conference, and in some documentation that success has even been reported in distributed tasks, such as building caches, through the SOM/SOC architecture that is available out of the box in AGS.  Could this architecture be extended to distribute geoprocessing tasks?

Taking a different tack, AGS exposes the geoprocessing object through a Python wrappers.  That means that we should be able to programatically run our software on Linux through Python.  Python also has wrappers or libraries for multithreading and MPI (distributed), so the implications for taking advantage of our cluster are especially exciting. 

Note: There are some differences to be expected, such as with file path conventions and name lengths. 

Next: Multithreading

http://support.esri.com/en/knowledgebase/techarticles/detail/31903

The Atlas of Visual Complexity

Very cool dynamic cartograms for visualizing economic complexity: atlas.media.mit.edu

Surface Climate Data

I am consulting on a research project involving bridge decay. For this project we needed to locate a number of surface atmosphere chemical data (some sulfure oxides and chloride) as well as data on precipitation, snowfall, and min/max temperature.

Here are some sources we found:

• Cl, SO4, precipitation etc.: National Atmospheric Deposition Program, http://nadp.sws.uiuc.edu/ ... the National Trends Network (NTN) data was the best
• SO2: http://www.epa.gov/airtrends/sulfur.html ... a KML is even available from this link
• Snowfall, Min/max tempurature (precip is also available): Daily by state, http://cdiac.ornl.gov/ftp/ushcn_daily/, Monthly, http://cdiac.ornl.gov/ftp/ushcn_v2_monthly/

NetCDF

NetCDF is a format well suited for environmental research given the inherent multi-dimensionality of the environment and environmental research topics. 

I have not worked much with NetCDF data, and recently ran into some trouble importing gridded bathymetric data sets (GEBCO) into ArcGIS.

When importing into ArcGIS I attempted to use Multidimension Tools > Make NetCDF Raster Layer.  This tutorial from Esri describes this procedure.  Unlike in the tutorial, default values were not populated for tool parameters, and as I was unable to ascertain these values, I could not run the tool.

My next option was to learn more about the data set from GDAL's gdalinfo command.  After recompiling with the netcdf libraries, I was able to run gdalinfo on the data set, but it returned a rather sparse result that did not illuminate any of the parameters that I needed.  This contrasts with the result show in the GDAL documentation for netcdf.

I then attempted to load NetCDF directly into ENVI and IMAGINE -- common geospatial image proecessing programs -- I could not load it successfully into either of these.

I was somewhat more successful with IDL.  IDL is able to read NetCDF and report on its structure. However, the commands for interacting with the results objects are unwieldy at best.  This is an option that requires more investigation.

The GMT use NetCDF as the native package format, and GEBCO and others seem to base their NetCDF format off of GMT's specifications.  The grdinfo command under GMT gives some information about the data set, but still nothing that can be used in ArcGIS.  This also requires more investigation, as there are many commands for working with NetCDF.

Finally the data set was able to be opened by GEBCO's specialized display software (which is produced by the data provider).

I will be adding more to this post as I learn more about NetCDF and perhaps the peculiarities of this particular data set ... why use open standards if you need a data provider created tool to open the data??

Importing Data Dump into Postgres

I needed to take four steps to import an Oracle binary dump into PostgreSQL

1. Open Oracle dump with something like notepad++ or grap and do a find/replace for all the binary characters (these can be highlighted in notepad++) and replace them with something like a pipe character ("|")
2. If you know the special characters that are not compatible with UTF-8, then do find/replace on these too now ... otherwise you can do this after running the copy command (step 5), since that will give an error for each non UTF-8-able line/character
3. Open the piped file in Excel and replace with tabs.  Tabs are the standard delimiter in Postgres and by saving out of Excel, you get a uniform number of columns for each record, thereby avoiding "missing data for column" when executing the "copy" command
4. Create a table in Postgres with the number of columns seen in Excel, using generic data type such as text
5. execute the copy command ... no delimiters param need be given, since it's tabs

   COPY data FROM 'C:/temp/data.txt'

ArcGIS Weighted Overlay Error #999999

Client was getting error 999999 (generic) error when running weighted overlay analysis.  Fixed this by changing Windows environment variable for temporary folders to c:\Windows\Temp from the users temporary directory, which had a complicated file name