Sunday, April 10, 2016

Connecting to Amazon Redshift (or generic PostgreSQL) with Oracle SQL Developer

I guess old habits die hard; I've never used TOAD in my life but having a GUI-based SQL client is often convenient. I've used Oracle SQL Developer on and off for years (after all, it is free as in beer) and it works well enough for me.

I've recently had the issue of connecting to Amazon Redshift using SQL Developer, and after some poking around managed to do it, so I'm documenting it here.

1. Get yourself SQL Developer from Oracle's web site

2. Download the PostgreSQL JDBC drivers (the latest ones work fine)

3. Install the PostgreSQL JDBC drivers into SQL Developer as per Oracle's documentation (also see Gokhan's blog post)

The wrinkle is that SQL Developer wants the PostgreSQL database name to match the username, which is not the case for Redshift - Redshift has a separate username/password and database name.

Basically, you need to modify the Hostname field in SQL Developer. Instead of putting in just the hostname of your Redshift cluster, you need to put in hostname:port/databasename?



Note that there is a Port field but this gets ignored if you suffix databasename? to the hostname.

Everything should work as expected after that.

Enjoy columnar database goodness!

Tuesday, October 20, 2015

Getting Oracle Linux on Amazon EC2

If you feel the need to roll your own Oracle Linux install on Amazon EC2 (since Oracle no longer provides an officially-supported AMI, and you may not be too keen on using one of the community AMI's):

(1) launch an EC2 image using the CentOS 6.5 AMI from the Marketplace (which is free..) then log in as root (not ec2-user)

(2) import the Oracle GPG key
1
2
cd /etc/pki/rpm-gpg/
curl -O https://oss.oracle.com/ol6/RPM-GPG-KEY-oracle

(3) use the Oracle specific rules to convert the CentOS 6 to OL from this documentSpecifically, run the following commands as root:
1
2
curl -O https://linux.oracle.com/switch/centos2ol.sh
sh centos2ol.sh

(4) Synchronize the yum repository
1
yum -y upgrade

Once the command completes, the end-user should have a fully-patched Oracle Linux 6.7.

If you intend to install Oracle Database 11gR2, you can apply all the necessary packages and kernel parameters with this command:
1
yum install oracle-rdbms-server-11gR2-preinstall -y

and if you will install Oracle Database 12c R1, use the following:
1
yum install oracle-rdbms-server-12cR1-preinstall -y

Tuesday, October 06, 2015

Collecting Haze Data with Arduino, Raspberry Pi, and Amazon DynamoDB

I finally decided to put those Sharp airborne dust sensors which I bought during the last haze epidemic (in 2013) to good use, along with the small mountain of parts I have at home.

I used this very simple Arduino circuit and code snippet to read the Sharp sensor.  I ended up using a 100uF capacitor and 220-ohm resistor because those are what I had on hand; performance seems unimpaired. I used an Arduino Uno, because the dust sensor needs 5V. The sensor is very noisy and jumpy, so I used an interquartile mean and 200 measurements (discarding the bottom 25% and top 25%) to get a more robust reading. The raw value still jumps around by 1 LSB.


I also modified the slope function so that a full-range reading is 250 ug/m^3 (the original formula is here). Here is the modified Arduino code:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
// program to read a Sharp GP2Y1010AU0F dust sensor
// this sensor produces a dust value with a maximum value of 250 ug/m3

#define NUMSAMPLES 200
#define dustPin 0
#define ledPower 2
#define delayTime 280
#define delayTime2 40
#define offTime 9680

void setup() {
  Serial.begin(9600);
  pinMode(ledPower, OUTPUT);
  pinMode(4, OUTPUT);
}

// swap sort algorithm
void swapsort(int *sorted, int num) {
  boolean done = false;    // flag to know when we're done sorting              
  int j = 0;
  int temp = 0;

  while(!done) {           // simple swap sort, sorts numbers from lowest to highest
    done = true;
    for (j = 0; j < (num - 1); j++) {
      if (sorted[j] > sorted[j + 1]){     // numbers are out of order - swap
        temp = sorted[j + 1];
        sorted [j+1] =  sorted[j] ;
        sorted [j] = temp;
        done = false;
      }
    }
  }
}


// read the dust sensor, implementing an interquartile mean
// thanks to STMicro Application Note 3964 "How to design a simple temperature measurement application using the STM32L-DISCOVERY"
int readRawDustValue() {
  int i = 0;
  int rawVal[NUMSAMPLES];

  for (i = 0; i < NUMSAMPLES; i++) {
    // ledPower is any digital pin on the arduino connected to Pin 3 on the sensor
    digitalWrite(ledPower, LOW); // power on the LED
    delayMicroseconds(delayTime);

    rawVal[i] = analogRead(dustPin); // read the dust value via pin 5 on the sensor
    delayMicroseconds(delayTime2);

    digitalWrite(ledPower, HIGH); // turn the LED off
    delayMicroseconds(offTime);
  }

  // now we have the raw values, sort them
  swapsort(rawVal, NUMSAMPLES);
  
  // drop the lowest 25% and highest 25% of the readings
  long dustVal = 0;
  for (i = NUMSAMPLES / 4; i < (NUMSAMPLES * 3 / 4); i++) {
    dustVal += rawVal[i];
  }
  dustVal /= (NUMSAMPLES / 2);
  return (dustVal);  
}


// convert the raw count to a dust value
// the full-range signal is 771 counts = 3.76V
// based on http://www.howmuchsnow.com/arduino/airquality/
//
// dust density (mg/m3) = 0.172 * V - 0.1

float calcDustDensity (int rawVal) {
  float calcVoltage = rawVal * (5.0 / 1024);
  
  // I use a different figure so that 771 counts = 248 ug/m3
  float dustDensity = ((calcVoltage * 0.688) - 0.1) * 100;
  
  if (dustDensity < 0) dustDensity = 0;
  return (dustDensity);
}


void loop() {
  int dustValue = readRawDustValue();
  float dustDensity = calcDustDensity(dustValue);
  
  Serial.print("Raw Dust Value = ");
  Serial.println(dustValue);
  Serial.print("Dust Density = ");
  Serial.println(dustDensity);
}

Unfortunately the Arduino Uno only has 2K of RAM and mine did not have an Ethernet or Wi-Fi shield, so I decided to use a Raspberry Pi (Model B, the old single-core one) to read the Arduino Uno and talk to DynamoDB.

Basically, the Arduino appears as a serial port to the RasPi and I read the values using pySerial and uploaded them using boto.

Surprisingly the RasPi behaves exactly like a "real" Linux box, installing the AWS CLI and Python SDK (boto3 and boto) is exactly the same as on a large machine. There were no hiccups during the installation (albeit the installation took a long time).


The Python script is as follows (my first Python program!) note the hard-coded AWS credentials which is really a terrible practice.


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
#!/usr/bin/python

import boto
from boto import dynamodb2
from boto.dynamodb2.table import Table

import datetime
import time
import serial
import re
import sys

# monkey hacking to work around "inexact numeric" issue in boto
import decimal
from boto.dynamodb.types import DYNAMODB_CONTEXT
# Inhibit Inexact Exceptions
DYNAMODB_CONTEXT.traps[decimal.Inexact] = 0
# Inhibit Rounded Exceptions
DYNAMODB_CONTEXT.traps[decimal.Rounded] = 0


conn = dynamodb2.connect_to_region(
 'ap-southeast-1',
 aws_access_key_id='AKIAxxxx',
 aws_secret_access_key='R0KIxxxx',
)
table = Table(
 'dustValues',
 connection=conn
)

# open the serial port (we need to be setuid root for this)
serialport = serial.Serial("/dev/ttyACM0", 9600, timeout=0.5)

rawDustValue = 0
dustDensity = 0

oldDustValue = 0
oldDustDensity = 0

# to put a blank line..
print "\n"

while True:
 command = serialport.readline()
 matchObj = re.match( '^(.*) = (.*)$', command, re.M | re.I)
 if (matchObj):
  # hash is unixTimestamp
  unixTimestamp = int(time.time())
  timestamp = time.strftime("%Y%m%d%H%M%S")

  # Var =  Raw Dust Value
  # Val =  137
  # Var =  Dust Density
  # Val =  81.51
  if (matchObj.group(1) == 'Raw Dust Value'):
   rawDustValue = int(matchObj.group(2))
   # print "raw dust value = ", rawDustValue

  if (matchObj.group(1) == 'Dust Density'):
   dustDensity = int(float(matchObj.group(2)))
   # print "dust density = ", dustDensity

 if ((rawDustValue != oldDustValue) and (dustDensity != oldDustDensity)):
  oldDustValue = rawDustValue
  oldDustDensity = dustDensity

  # print "hash = ", str(unixTimestamp)

  # calculate the PSI (this is a very approximate value)
  # based on dustDensity (in ug/m3) and this
  # http://www.haze.gov.sg/docs/default-source/faq/computation-of-the-pollutant-standards-index-(psi).pdf
  # we only use the 24-hour PM2.5
  # note that the Sharp sensor can't distinguish particle size
  # so PM10 particles are also falsely measured

  psi = 0
  if (dustDensity <= 12):
   psi = dustDensity * 4.17
  elif (dustDensity > 12 and dustDensity <= 55):
   psi = 51 + ((dustDensity - 13) * 1.17)
  elif (dustDensity > 55 and dustDensity <= 150):
   psi = 101 + ((dustDensity - 56) * 1.05)
  elif (dustDensity > 150):
   psi = 201 + (dustDensity - 105)

  psi = int(psi)

  # shorten timestamp so it fits on the tiny PiTFT screen
  ts = int(unixTimestamp) - 1444116000

  print ts, ":",
  print "raw=", rawDustValue,
  print " ug/m3=", dustDensity,
  print " PSI=", psi, "   \r",
  sys.stdout.flush()

  # write to the table
  try:
   table.put_item(data={
    'unixTimestamp': int(unixTimestamp),
    'timestamp': timestamp,
    'rawDustValue' : int(rawDustValue),
    'dustDensity' : int(dustDensity),
    'psi' : int(psi)
   })
  except:
   # do nothing
   pass

I then placed the Python script in /etc/rc.local (making sure to append an ampersand so that booting would complete).

And voila:

Monday, June 15, 2015

Canon 10-22mm EF-S and 16-35mm L IS Compared

The results of this comparison are pretty much fore-ordained, but I was curious as to how obvious the differences were to an amateur and non-critical photography user such as myself.

I've used the 10-22mm f/3.5-4.5 EF-S lens for about a year, through a couple of extended holidays, and it's a very satisfying lens on a crop sensor body. The 16mm equivalent focal length is quite striking and gets pretty addictive, and the ring-type USM, full-time manual, focusing window, and well-damped mechanicals are a pleasure to use (not far off from L build quality, to be honest).

However, we got a Canon 6D body (a full three years after its introduction - we've been using an ancient Canon 40D on its second shutter for the past 6+ years) and the 10-22mm won't mount on a full frame body.  After some analysis paralysis, I decided that I wanted to retain the 16mm wide end and so went for the 16-35mm f/4L IS (instead of the somewhat cheaper 24-70mm f/4L IS).

I also have a Canon 550D which I was planning to IR-mod for astrophotography (the elderly 40D has been through so many holidays and memories and has a brand-new shutter, so I didn't want to butcher it). The 550D has almost the same pixel count as the 6D, so I figured it would be interesting to compare the 550D and the 10-22mm, with the 6D and the 16-35mm.

Here's the sample image that I used, which is 1/500 second, f/4, ISO 100, "natural" picture style, straight JPEG from the camera, tripod-mounted.  f/4 is wide-open for the 16-35mm, and stopped down a smidgen for the 10-22mm.  The 10-22mm had lens correction enabled, but the 16-35mm did not (the 6D's firmware didn't have the profile for the 16-35mm loaded).

The full image from the 6D and 16-35mm (it got nudged a bit and is a bit narrower than 16mm here). Note the vignetting in the corners, due to the lack of a lens correction profile.

and from the 550D and 10-22mm at 10mm. Note that there's no vignetting in the corners! the 550D has a less-sophisticated lens correction system than the 6D (only correcting for vignetting, not for CA) but it shows its magic here:

This is the center at 100% with the 6D and 16-35mm:

with the 550D and 10-22mm:

I also used a $150 Tokina 20-35mm f/3.5-4.5 on the 6D. The Tokina is a bit narrower so I scaled down the image by 20% (to "convert" the 20mm to 16mm equivalent) and here is the center:

This is the far left edge with the 6D and 16-35mm:

with the 550D and 10-22mm:

and with the 6D and Tokina 20-35mm:

I then tilted the cameras upward to put the house at the far left edge (in the immediate images above) at the bottom-left corner. Here's the bottom corner with the 6D and 16-35mm:

with the 550D and 10-22mm:

and with the 6D and Tokina 20-35mm:

The results should be self-evident, but some observations:

The Tokina is not bad at all in absolute terms, and is even more amazing considering the low, low price of $150 on eBay. In the sample images above, I would guess that the Tokina might almost be giving the 16-35mm f/4L IS a run for its money. Of course the correct comparison of the Tokina would be against the 10-22mm at 12.5mm and the 16-35mm at 20mm. In that case I suspect the Tokina would fare worst, but this post isn't about the Tokina, I just threw it in since I had it.

I hadn't realized how mediocre the 10-22mm was in the corners until I did this little test. Which only goes to show - I carried the 10-22mm through two countries, had a great deal of fun, and came back with a lot of memorable images. Never once did I wonder if I was getting critically sharp images. Do I feel that those holidays were compromised by the not-so-great 10-22mm lens? not at all. As an engineer, I certainly feel better if I take technically better images, and there are worse vices than accumulating L glass. But absolute image quality has very little to do with the personal satisfaction and memories captured with any set of camera gear.

The 16-35mm is obviously the best, as it should be at over double the price of the 10-22mm and ten times the price of the Tokina 20-35mm. In addition to its high price, the 16-35mm is also heavy, and large, about an inch longer than the 10-22mm.

Of course if you want a moderately-priced ultra-wide zoom on a full-frame Canon body, the 16-35mm f/4L IS is probably the best game in town right now, as it's sharper than the f/2.8 version, is lighter, cheaper, and has IS. In my opinion, if you want a practical ultra-wide zoom for a full frame Canon, this lens is the no-brainer choice. It's even a pretty good deal for an L lens, considering it is Canon's latest and greatest not-so-expensive ultra-wide.

Friday, April 10, 2015

Astro-Tech 8" Imaging Newtonian, Part VIII

Final refinement on the Astro-Tech Imaging Newtonian: rather than paint, powder-coat, or anodize the tube, I simply wrapped it with fake carbon fiber wrapper (used to make regular cars look more ricey).

A 1m x 1.7m cut cost me S$25 (about US $20) and I only ended up using half of it.


A useful tip: putting the holes in the wrap for the screw holes is much easier with a hot soldering iron!

The large hole for the focuser must be cut out with an X-Acto knife, box cutter, or similar implement.


The finished telescope actually looks pretty good, and no one's the wiser that it isn't real carbon fiber.
One caveat though, the wrap is rather soft. I dinged the telescope tube against an aluminum door frame, which cut into the wrap and exposed the metal underneath. I was able to push back the wrap over the exposed metal, but the finish is no longer pristine.  I would expect that for larger dings, it might be necessary to overcoat the damaged portion with clear varnish or similar to prevent the wrap from peeling.