Italy from space
Avishkar Autar · Jan 12 2011 · Random
A nighttime picture of Italy taken from the Cupola observation deck of the International Space Station:
(I modified brightness + contrast, and sharpened a bit)
NYC Data Mine, restaurant inspection data
Avishkar Autar · Jan 11 2011 · Databases
I’ve just finished importing the current restaurant inspection data from the NYC Data Mine into a PostgreSQL database. It wasn’t the most difficult migration, but more difficult than it should be as the raw data from the data mine is messy and not well-formed; a typical problem with many of the data sets present in NYC Data Mine. I came across a great post by Steven Romalewski (director of the CUNY Mapping Service) about the poor data quality and poor metadata based on his experiences.
From looking at the restaurant inspection data and skimming a few other sets, I get the sense that structured and relational data simply isn’t understood or handled well. To be fair, there’s a very real lack of tools in the market, at least at the consumer/data-entry level, for handling such data, so it’s not surprising that everything gets jerryrigged into an Excel worksheet. This is very clear when looking at the restaurant inspection data, you notice right away that restaurant ids and names are repeated across multiple rows.
In any case, the restaurant inspection data is better than most of the sets, but there’s a few issues to take note of:
- In multiple cases the same row, with the exact same data, is repeated.
- There are 2 columns for the inspection date: INSPDATE and GRADEDATE; GRADEDATE = INSPDATE if there’s a letter grade for the restaurant, otherwise it’s blank/null.
- Most glaring, there are invalid timestamps in the GRADEDATE column for 2 restaurants (but, of course, it’s across multiple rows as the restaurants has multiple entries), CAPRI RESTAURANT and MAMA LUCIA:
For my purposes, I only wanted the most recent inspection result (i.e. the row the latest INSPDATE timestamp). To do this, I added an additional column for a serial/auto_increment id number. Then, once imported, I deleted the unneeded rows with the following query:
/* table is restaurant
id = CAMIS
inspection_score_date = INSPDATE
internal_id = serial/auto_increment id number
*/
DELETE FROM restaurant WHERE internal_id NOT IN
(SELECT MAX(restaurant.internal_id) AS max_iid FROM restaurant,
(SELECT id, dba, MAX(inspection_score_date) AS last_inspt FROM restaurant GROUP BY id, dba) AS sub
WHERE restaurant.id=sub.id AND restaurant.inspection_score_date=sub.last_inspt GROUP BY restaurant.id)
The innermost subquery pulls the rows with the most recent inspection date, the outer takes care of duplicate rows with the same inspection date by simple taking the row with the max internal id number. What results is a column of internal id numbers – each representing a row with a unique restaurant inspection for the most-recent inspection.
I’m not sure if this is the best or most efficient way to do this, but it works and took about 14s to delete the unneeded rows for 398,878 rows on a low-end VPS.
PostgreSQL + PHP installation on Windows 2003 x64
Avishkar Autar · Jan 10 2011 · Databases
Well the PostgreSQL installation itself is easy enough, getting it to work with PHP is the challenging part. Here’s what I did:
- Install PostgreSQL
- Edit php.ini, uncomment “extension=php_pgsql.dll”
- Edit environment variables, add PostgreSQL /bin and /lib directories to Path. This solves the issue of php_pgsql.dll not loading due to it not being able to resolve dependencies.
- Download http://files.dll-vista.com/dllvista-php_pgsql.dll.zip, replace php_pgsql.dll with the one in the zip file. This solves the issue where you get a message about php_pgsql.dll not being a valid win32 application if you run php.exe;
- Done. PHP should now be able to communicate with PostgreSQL.
webOS file upload
Avishkar Autar · Jan 9 2011 · webOS
This is something pretty basic, but the example in the API documentation leaves a lot to be desired and, if I recall correctly, doesn’t even work.
So here’s how to do a file upload on webOS, sending the file along with additional data in the POST call.
One critically important point to take note of is that session information is not passed from your app to the com.palm.downloadmanager service so, if necessary, you must verify and authenticate the user again. As I was using PHP for my server-side stuff, I looked into getting and passing the PHPSESSID cookie, but I couldn’t figure out how the get the value, webOS does not seem to store it with other cookies.
// additional POST data
// spot_id, userName, password declared elsewhere
var post_params = [
{ 'key': 'id', 'data': spot_id, 'contentType': 'text/plain' },
{ 'key': 'username', 'data': userName, 'contentType': 'text/plain' },
{ 'key': 'password', 'data': password, 'contentType': 'text/plain' }
];
ctrlr.serviceRequest('palm://com.palm.downloadmanager/', {
method: 'upload',
// myfile = fileName of file to upload
// url_to_web_service = URL to server-side script that will handle file upload
// Note, contentType is mime type of file (according to docs), but 'img' used for images?!
parameters: {
'fileName': myfile,
'fileLabel': 'my_cool_file',
'url': url_to_web_service,
'contentType': 'img',
'subscribe': true,
'postParameters': post_params
},
onSuccess: function (resp)
{
if (resp.responseString) {
// file has been uploaded
}
else {
// file partially uploaded, resp contains progress info
}
},
onFailure: function (e)
{
// something bad happened!
}
});
On the server-side, the file is identified by the value of ‘fileLabel’ (in this case ‘my_cool_file’), and the additional POST data is identified by the value of the key field.
dotspott photo markers
Avishkar Autar · Jan 8 2011 · Web Technologies
The dotspott web client has been updated yet again, this time I’ve added support for photo markers.
Mojo.Widget.ImageView
Avishkar Autar · Jan 7 2011 · webOS
Using the ImageView widget is somewhat perplexing. The documentation states:
This widget is designed to view an image full screen with support for zooming and panning…
You would assume that you could simply create the ImageView widget and it would be full screen and handle orientation changes (something very essential and common when viewing photos) out of the box. Unfortunately, this is not the case and some additional code is necessary to get it to function as such.
I’ll present some code here, but much of what I did was based on the code at webOS 101.
First, a few additions to the assistant’s activate function:
- We setup full-screen mode
- Set “free” orientation (allows the system to rotate the display)
- Manually size the ImageView widget (id=photoView)
ViewPhotoAssistant.prototype.activate = function(event) {
this.controller.enableFullScreenMode(true);
this.controller.stageController.setWindowOrientation('free');
$('photoView').mojo.manualSize(Mojo.Environment.DeviceInfo.screenWidth, Mojo.Environment.DeviceInfo.screenHeight);
/* set images here */
};
To actually respond to orientation changes we need to setup an event listener for the “resize” event. This is dealt with in the assistant’s setup function:
ViewPhotoAssistant.prototype.setup = function() {
var photoViewerAttributes = { noExtractFS: true };
var photoViewerModel = {};
this.controller.setupWidget('photoView', photoViewerAttributes, photoViewerModel);
// setup handler for resize event ...
this.handleWindowResizeHandler = this.handleWindowResize.bindAsEventListener(this);
this.controller.listen(this.controller.window, 'resize', this.handleWindowResizeHandler);
/* other setup code... */
};
Within the resize event handler, we manually resize the ImageView widget based on the new dimensions of the window:
ViewPhotoAssistant.prototype.handleWindowResize = function (event){
if ($('photoView') && $('photoView').mojo) {
$('photoView').mojo.manualSize(this.controller.window.innerWidth, this.controller.window.innerHeight);
}
}
Finally, in the assistant’s cleanup function, we stop listening on the event:
ViewPhotoAssistant.prototype.cleanup = function(event) {
this.controller.stopListening(this.controller.window, 'resize', this.handleWindowResizeHandler);
};
The haversine formula
Avishkar Autar · Jan 6 2011 · Random
I had to do a little research into calculating distances between 2 points on the Earth, as I wanted the latest version of the dotspott webOS app to calculate the distance from your current position to your spotts.
The page at Movable Type on the subject proved to be an amazing resource.
I ended up doing an straightforward implementation of the haversine formula:
R = mean radius of earth = 6,371km
Δlat = lat2 - lat1
Δlong = long2 - long1
a = sin²(Δlat/2) + cos(lat1) · cos(lat2) · sin²(Δlong/2)
c = 2 · atan2( sqrt(a), sqrt(1-a) )
d = R · c
The Javascript code (HSD.CurrentGlobalPosition.LatLong is a string with the device’s current position as a latitude,longitude pair):
HSD.Math.toRad = function(deg)
{
return deg * (Math.PI/180);
}
HSD.CurrentGlobalPosition.calcDistance = function(_targetLat, _targetLon)
{
var latlongParts = HSD.CurrentGlobalPosition.LatLong.split(',');
var lat1 = parseFloat(latlongParts[0]);
var lon1 = parseFloat(latlongParts[1]);
var lat2 = parseFloat(_targetLat);
var lon2 = parseFloat(_targetLon);
var R = 6371; // km
var dLat = HSD.Math.toRad(lat2-lat1);
var dLon = HSD.Math.toRad(lon2-lon1);
var a = Math.sin(dLat/2) * Math.sin(dLat/2) + Math.cos(HSD.Math.toRad(lat1)) * Math.cos(HSD.Math.toRad(lat2)) * Math.sin(dLon/2) * Math.sin(dLon/2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var d = R * c;
return d;
}
The result is the distance to the target in kilometers.
Netbeans RTF Copy plugin
Avishkar Autar · Jan 1 2011 · Netbeans
I made a little plugin for Netbeans to allow copying text from the editor as RTF; mainly, preserving the foreground colors from its syntax highlighting. I wanted something akin to what’s possible with Visual Studio.
Taking the copied RTF text and running it thru Rtf2Html, I can then post nicely colored code:
header('Content-type: application/xml; charset=utf-8');
echo "<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n";
The NBSpecialCopyPaste by Casper Bang was incredibly helpful as a starting point for this plugin.
There’s an issue I wasn’t able to resolve before releasing this; fields (by default, highlighted green by the editor) aren’t copied properly in some cases. The API seems to give no indication that these tokens are colored differently. I know this is true for Java and PHP code, but not true for XML. The code to get the necessary AttributeSet is exactly what’s in NBSpecialCopyPaste:
private static AttributeSet findFontAndColors(Token tkn, FontColorSettings fcs)
{
AttributeSet as = fcs.getTokenFontColors( tkn.id().name() );
if (as == null)
{
// ...try to get from its category
as = fcs.getTokenFontColors(tkn.id().primaryCategory());
}
return as;
}
I’ll dig deeper and try to resolve this in a future version.
Reflex Feedback widget
Avishkar Autar · Dec 27 2010 · Web Technologies
I worked on a small AJAX widget for user feedback built atop jQuery UI: Reflex Feedback. It’s inspired by the widgets you see from services like Get Satisfaction and UserVoice, but much simpler and it’s a frontend-only widget, how you handle the feedback info on the backend is up to you.
Here’s what it looks like.
And here’s what the tag that opens the dialog looks like:
To use it, download or clone the ReflexFeedback repo from bitbucket
Place the .js file wherever you’d like but the /reflex.content folder should a subdirectory in the same folder as the page loading the .js file. Load reflex.js as you would any other javascript file:
<script type="text/javascript" src="js/reflex.js"></script>
Call Reflex.init() to add the widget to the page. The first argument is the DOM element to attach the additional HTML/CSS code to. The seconds argument is the server-side script to call when the user clicks Send Feedback.
Reflex.init($('body'), 'controller/post_feedback.php');
That’s it for the frontend. You should see the tag show up in the right-hand corner and when clicked the dialog open.
For the backend, the AJAX call to send the feedback info will send a POST request with 2 fields: feedback_type, feedback_txt.
Reflex expects an XML reply from the server:
<reflex>
<result>ok</result>
</reflex>
ok indicates a successful result, any other reply is considered an error.
A successful result will close the dialog and show another with a thank you message.
For an error, a message is shown below the Send Feedback button, informing the user that an error has occurred and to try again.
As for what to actually do with the feedback, that’s up to you, but what I’m doing is sending myself an email with the feedback info. I’ve posted my PHP script below; feel free to use it, modify it, etc. If you do use this code, be sure to fill in your mail server credentials and a from address; you’ll also need PEAR’s Mail package installed.
<?php
require_once "Mail.php";
require_once "Mail/mime.php";
header('Content-type: application/xml; charset=utf-8');
echo "<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n";
if(!isset($_POST['feedback_type']) || !isset($_POST['feedback_txt']))
{
echo "<reflex><result>error:missing-arguments</result></reflex>";
}
else
{
$from = "...";
$to = "...";
$subject = "Feedback from user...";
$feedback_type = $_POST['feedback_type'];
$feedback_txt = $_POST['feedback_txt'];
$bodyHtml = "<html><body>";
$bodyHtml .= "<p>Type: {$feedback_type}</p>";
$bodyHtml .= "<p>Feedback: {$feedback_txt}</p>";
$bodyHtml .= "</body></html>";
$body = $bodyHtml;
$host = "...";
$port = "...";
$username = "...";
$password = "...";
$headers = array('MIME-Version' => '1.0rn',
'Content-type' => 'text/html; charset=utf-8',
'From' => $from, 'To' => $to, 'Subject' => $subject);
$smtp = Mail::factory('smtp',
array ('host' => $host,
'port' => $port,
'auth' => true,
'username' => $username,
'password' => $password));
$mail = $smtp->send($to, $headers, $body);
if (PEAR::isError($mail))
{
$err_details = $mail->getMessage();
echo "<reflex><result>error:send-failure</result><details>{$err_details}</details></reflex>";
}
else
{
echo "<reflex><result>ok</result></reflex>";
}
}
?>
That’s all for now. I’ll work on more features and options for customization in the future. You can see the widget in action over at dotspott.com
Spott map
Avishkar Autar · Dec 19 2010 · Databases
Something pretty cool in MSSQL Server Management Studio: for columns with the geography data type, Management Studio will plot the points on a map. Here’s a map of all locations tagged from all dotspott users.
