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

Caching (GWC) Imagery

Issues I've run into while caching imagery on Geoserver/GWC

• Out of Memory Errors (java.lang.OutOfMemoryError: PermGen space): Solved this (as suggested here) by adding -Xms48m -Xmx256M -XX:MaxPermSize=128m to Geoserver's startup.bat/startup.sh on the line that starts the JVM (for me it was line 120)
• also found a post suggesting adding UseCompressedOops fixed some issues with caching imagery ... from

"%RUN_JAVA%" -DGEOSERVER_DATA_DIR="%GEOSERVER_DATA_DIR%" -Djava.awt.headless=true -DSTOP.PORT=8079 -DSTOP.KEY=geoserver -jar start.jar

to

"%RUN_JAVA%" -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -XX:+CMSClassUnloadingEnabled -Xms48m -Xmx512M -XX:MaxPermSize=256m -XX:+UseCompressedOops -DGEOSERVER_DATA_DIR="%GEOSERVER_DATA_DIR%" -Djava.awt.headless=true -DSTOP.PORT=8079 -DSTOP.KEY=geoserver -jar start.jar

• ERROR [seed.MTSeeder] - Problem communicating with GeoServer : was getting this error because a DB parameter had recently changed (DB name)
• Error ... Could not list layers for this store, an error occurred retrieving them ... then something about null argument ... this was also fixed with these memory options
• I also found it necessary to scale down my imagery using the outsize parameter  with gdal translate

gdal_translate -of GTiff -co "TILED=YES" -outsize 50% 50% input-file.tif output-file.tif

• I think I may also have been having trouble not adding a world file (possibly prj) which I then created with ArcGIS (not sure how to do that with GDAL, since that saves the geographic info directly to the TIFF)

JQuery Each Iterator

Is your each iterator not working on an Array? Try constructing the array as an object instead. Jquery v1.3.2

Setting LM_LICENSE_FILE Environment Variable for ENVI/IDL 4.8 on Pre-Lion MacOS

To set an environment variable for ENVI/IDL on a pre-Lion MacOS System, I would recommend using the environment.plist file. This persists through whatever shells you have installed as well as in X Windows (and therefore when you double-click the ENVI executable from the MacOS GUI). I did not find any correct documentation for doing this from ITT or NASA.

1. Install the software
3. Define the environment variable in the file ~/.MacOSX/environment.plist
----- Open the Terminal
----- You will probably have to create the directory ... mkdir ~/.MacOSX)
----- ... and the file vi ~/.MacOSX/environment.plist

Paste the following into vi in Insert mode (press i) and save (:x) ... if you have more than one environment variable, make sure to add another key/string pair ... make sure you subsistute PORT and HOST below

<!--?xml version="1.0" encoding="UTF-8"?--><!--?xml version="1.0" encoding="UTF-8"?-->
<plist version="1.0">
<dict>
   <key>LM_LICENSE_FILE</key>
   <string>PORT@HOST</string>
</dict>
</plist><plist version="1.0"><dict></dict>
</plist>

4. Close terminal

You should now be able to use ENVI/IDL.

Openlayers Geoserver WMS Caching with GWC

Now that Geowebcache is integrated with Geoserver and WMS (among other OGC service types), map tile-caching is supposed to be really simple. I found this not to be the case. Among other challenges, document is lacking or blatantly wrong. Here are a couple key concepts that I learned the hard way:

Use the demo from the Geowebcache section (http://localhost:8080/geoserver/gwc/demo) NOT the regular OL "Layer Preview" which does not use the correct gridset parameters (nice demo, eh?)


Issues I was having with labeling and tiles are mostly solved by creating a database view from the centroids of polygons to be labeled and label these instead, and include in a layer group.

Don't use the "Recycle Tiles" option ... especially if you don't have native JAI. This causes a memory leak and subsequently causes the server to crawl/crash after X requests. Also you probably shouldn't tweak max render time/memory unless you have to (despite documentation saying that this helps performance ... would this be helpful at another time? I don't know).

Despite documentation saying to use the wms endpoint and that no special endpoint is needed ... a special endpoint is needed ... it's of this format http://host:8080/geoserver/gwc/service/wms

The gridset scheme is static and opaque, unless a custom config file is created. The documentation gives the impression that the integration of WMS/GWC creates arbitrary gridsets given wms grid request characteristics. One could could create their own geowebcache.xml (saved to GEOSERVER/webapps/WEB-INF/geowebcache.xml ... though there is some confusion about that). See more about geowebcache, the gridset schema, and geowebcache.xml here

Don't pass anything on the wms request that you don't have to! Forums and documentation say that you need to pass things like tilesOrigin, which will cause the request to fail most of the time.

GWC saves tiles to your data directory under the gwc folder. You should check here to make sure tiling is working.

Lookbehind/Lookahead Assertion with Npp Regex

Today I was looking for a way to get the last character of a pattern so that I could replace it. In this example, I wanted to find the last pipe in a record like 8|34346|. Normally with regex, one could do this using a lookbehind assertion. To do this with notepad++ you must utilize the implicit search buffer, by using a \1 in the replace field (for example, to access the first set in the buffer).

So by searching on: 1|10007|someemail@someplace.com|lastname,first name|X

"Find What:" ([0-9]*[|][0-9]*)[|]
"Replace With:" \1%

I could get: 1|10007%someemail@someplace.com|lastname,first name|X

BIG THANKS TO DEAN NAIRN!

Lay Interpretation of Cancer Incidence

Cancer is a big deal, and the potential environmental factors make mapping incidence very appealing. This has made maps a handy device for newspapers and other "lay" interpretations, which typically don't employee rigorous statistical tests for significance. Victor Perez, a researcher at UD wants to investigate the ways cancer incidence is discussed in these lay publications. As a discursive preliminary step in this study, Dr. Perez asked me to help him prepare a map that would show what a random incidence of cancer cases might look like. To do this I used the "generate random points" tool using data on census tracts (Total Population from Census 2000) and given average Delaware cancer incidence.

SDE Errors

"ERROR: ESRI: error getting spatial references for srid = "

... uses public.sde_spatial_references instead of sde.sde_coordinate_systems, so cannot use standard srid ... only have a selection of srid's of loaded features ... therefore use srid defined in public.sde_spatial_references


"DBMS table not found xxx State ID = 9"

... was getting this error after the SDE had become corrupted b/c client was using PG Admin instead of ArcGIS Desktop to do schema changing operations (deleting tables). Clearly these actions should be done with Desktop in the future (Catalog). To fix this I deleted the SDE and reconfigured with the SDE Post-Installer. This may also be fixable using

sdetable -o unregister

or some switch combo on sdegdbrepair

Error in event viewer:
FATAL: bogus data in lock file "postmaster.pid": ""
resolve by deleting the postmaster.pid file

Postgres Error Codes: http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html

Deciphering Errors within ArcSDE
http://blogs.esri.com/esri/supportcenter/2011/09/22/deciphering-errors-within-arcsde/

ArcSDE/Postgres Error 000210, Could not Copy Features

It seems like there is an out of the box error in ArcSDE which causes geodatabase operations like Import Feature to fail out of the box (giving a 000210 error, Could not Copy Feature). In my case, it was because my db user name "postgres", which is usually a default, was not included as a schema in the deafault database. This might be solved by calling the user "sde", but in my case I solved it by simply creating a new schema in the default database beside the existing sde schema.

postgres max integer in an alphanumeric column

select max(to_number(substring(COLUMNNAME from '([0-9].*)'),'99999')) from TABLENAME

removes letters from a mixed column ... can be used for generating a new id

Intersect ArcGIS Bug

There is a bug in ArcGIS with the intersect tool (I noticed it when intesecting lines as polylines) that causes the result to be an empty table. To fix this I exported all tables being intersected to a new location (and the output to point there as well). This resulted in the expected output.

Flipped Polar Coordinate System

Problem: NIC/NSIDC Antarctica data was appearing flipped 180 dg, without coordinate system or projection defined. When other polar data, thought to be in the same coordinate system/projection was placed with this data, the other data appears to be flipped 180 in the right orientation (though the data in question is still flipped).

Solution: Modify the projection of the data in question to have a central meridian of 180 instead of 0 (with "define projection" using a custom projection based the other data). THE DATA VIEW PROJECTION MUST ALSO BE SET TO THE PROJECTION OF THE "GOOD" DATA (e.g. 0 Meridian, Polar Stereographic)

On Simple Structure and Factor Analysis

"I had become increasingly unhappy with L. L. Thurstone's principle of so-called simple structure and his widely accepted idea that variables are best explained if based on a minimum number of "factors", on only one "factor" if possible. As far as I can see, varieties in nature are nowhere put on single poles, phenomena generally arise from relations of underlying building blocks, from interaction of components and complex combinations. On all levels of inanimate and animate organization wholes emerge from interweaving parts. I could not understand why factor analysts were doggedly attempting to construe a world of utterly independent "dimensions" and associated constructs in splendid isolation." (David M. Glover)

notepad ++ special characters as replace string

While copy over a newline special character will show up in the "find" field of the "find & replace" dialog if one of these is present in a highlight, these cannot be copied/pasted into the "replace" field notepad ++ allows an extended set of special characters to be used both in the find AND replace fields ... so for example if you replace ";" with ";\r\n", with the "extended" option checked on, you will get a new line after every ";"

ArcGIS Editor for OpenStreetMap

I recently had a chance to test out ArcGIS Editor for OpenStreetMap (1.1 Beta3), an open source toolbox for interacting with OpenStreetMap (OSM). OSM, a popular open-source data repository, gained extra traction in the US after the Haitian Earthquake. This growth came from NGOs, and from some ArcGIS shops with open source leanings, who advocated on behalf of an extension to OSM from ArcGIS. I've been interacting heavily with OSM in building and maintaining the University of Delaware Map (UD Map). The data schema for UD Map is based on the OpenStreetMap schema, and interfaces with OpenStreetMap for data updates and publishing. As the project demanded it, and as a test of the new tools, I used the toolkit to publish some pending campus base layers. Alas, this trial ended disappointingly, leaving only manual editing of the vector layer. Append, which could have been used to join the vector input to the OSM polygon layer, did not work as expected: The OSM changeset was not updated, and therefore could not be uploaded to OSM for processing. The tools still look very useful for doing manual editing of OSM data and adding OSM data as fully editable vector layers in a map ... and of course, as code to be expanded upon ... this story is to be continued :-)

jquery accordion breaks out of containers on ie7 (ie6?)

fixed this issue by giving css position:relative on parent container further up the tree (in this case was the tabs parent)

Google Spreadsheet Data API

I gave a presentation to the U. of Delaware WebDev group and IT-CS&S (RDMS) on the topic of using data from Google Docs Spreadsheets for consumption in a scripted workflow. I'm using this in the UD Map for automatically incorporate rendering decisions from OCM.

The gist is that you open an https stream with headers for
authorizing, capture an authorization token on the response, open
another stream returning an xml of the table, and parse that. In
addition, you could form the url for the second stream with parameters
that query the column of interest, for example.

Here is the presentation:

https://docs.google.com/present/view?id=dx7rb3j_104dxjkq8c6

Here is some sample code (excuse references to my database/tables):

function do_post_request($url, $data, $optional_headers = null)
{
 echo "Begin do_post_request \n";
 $params = array('http' => array(
    'method' => 'POST',
    'content' => $data
  ));
 if ($optional_headers !== null) {
$params['http']['header'] = $optional_headers;
 }
 $ctx = stream_context_create($params);
 $fp = @fopen($url, 'rb', false, $ctx);
 if (!$fp) {
throw new Exception("Problem with $url, $php_errormsg");
 }
 $response = @stream_get_contents($fp);
 if ($response === false) {
throw new Exception("Problem reading data from $url, $php_errormsg");
 }
 return $response;
}

function doFromGoogle(){
echo "Begin doFromGoogle \n";
define("GURL", "https://www.google.com/accounts/ClientLogin");
define("SPREADSHEETREFKEY", "***YOURKEYHERE***");

//creates post to google auth, creates $auth string from google
$dataArr = array();
$dataArr['accountType'] = 'GOOGLE';
$dataArr['Email'] = '***YOUREMAILHERE***';
$dataArr['Passwd'] = '***YOURPASSWORDHERE***';
$dataArr['service'] = 'wise'; //name for spreadsheet data service
$dataArr['source'] = '***YOURSORUCENAMEHERE***'; //this may be optional

$dataStr = http_build_query($dataArr);

$opt_headers = 'application/x-www-form-urlencoded';

$responseStr = do_post_request(GURL, $dataStr, $opt_headers);
$responseArr = explode("\n",$responseStr); //breaking apart request to get auth token
$auth = explode("=", $responseArr[2]);
$auth = $auth[1];

$authStr = 'Authorization: GoogleLogin auth=' . $auth;
$url = 'https://spreadsheets.google.com/feeds/list/' . SPREADSHEETREFKEY . '/1/private/full';


$opts = array(
 'http'=>array(
'method'=>"GET",
'header'=>"$authStr"
 )
);

$context = stream_context_create($opts);

// ini_set('Authorization', "$authStr");

$handle = fopen($url, "r", false, $context);
$contents = stream_get_contents($handle);
fclose($handle);

$xml = new SimpleXMLElement($contents);

$fullquery = null;


$count = 0;
foreach ($xml->entry as $entry){
$count = $count + 1;
$id = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->osmid;
$render = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->dorender;
$label = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->dolabel;
$labelname = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->labelname;
$maxzoom = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->maxzoom;
$minzoom = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->minzoom;
$labelname = addslashes ($labelname);

//$color = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->color;

//print "$id, $count \n $render \n $label \n $labelname";
print "$id  $maxzoom $minzoom\n";

$query = null;

if($render <> ''){
 $query .= "UPDATE master SET dorender = '" . $render . "' where osm_id = $id and dorender <> '" . $render ."';\n";
}
if($label <> ''){
 $query .= "UPDATE master SET dolabel = '" . $label . "'where osm_id = $id and dolabel <> '" . $label ."';\n";
}
if($labelname <> ''){
 $query .= "UPDATE master SET labelname = '" . $labelname . "'where osm_id = $id and labelname <> '" . $labelname ."';\n\n";
}
if($maxzoom <> ''){
 $query .= "UPDATE master SET maxzoom = '" . $maxzoom . "'where osm_id = $id;\n\n";
}
if($minzoom <> ''){
 $query .= "UPDATE master SET minzoom = '" . $minzoom . "'where osm_id = $id;\n\n";
}

$fullquery .= $query;
}

//print($query);
$result = pg_query($fullquery) or die('Query failed: ' . pg_last_error());
}

Replacing Tomcat .jsp pages with Apache httpd .php pages

It turned out that this task was much easier than I'd expected. Here are the steps I took:

1. Edit the httpd.conf file (default path: C:\Apache2.2\conf\httpd.conf). Remove tomcat references to the services (JkMount). Add Redirect 301 lines to .php proxy
2. Create php proxy which is referenced by Redirect 301. Arguments are passed by the redirect, which can be handled by this proxy. Here is the code I used for a single argument, but could easily be extended to other arguments. This also saves arguments that are not handled to a log file:

   $out = array();
   $log = '';
   
   foreach($_GET as $key => $value){
      if (strpos($key, 'max')){};
      switch ($key) {
          case bldgcode:
              $out['id'] = $value;
              break;
          default:
              $log .= 'key is ' . $key . ' and value is ' . $value . "\n";
              break;
      }
   
   }
   
   $fp = fopen('log', 'a');
   fwrite($fp, $log);
   fclose($fp);
   
   $url = 'http://maps.rdms.udel.edu/map/index.php?' . http_build_query($out);
   
   header("Location: $url");