School Nurse Set Response in Motion - New York Times

Seeing Warning Signs of Outbreak, School Nurse Set Response in Motion - New York Times

It was a routine call last Thursday from a diligent high school nurse that put health detectives in New York City on the trail of a swine flu outbreak. Over the next few days, things unfolded much like a criminal investigation, with alert epidemiologists cast in the role of the police officer who remembers information on a wanted poster.

The newest thing on the things-to-be-scared-of list is swine flu, which has hit a number of countries around the world. In particular, Mexico, which led to cases in the U.S. So this because a Public Health emergency. And, since the coping through ignoring it and pretending it did not happen does not seem to be the U.S. policy, the first order of business is calming the public (because if you don't your doctors and emergency room will be flooded with the worried well/sympathetic illness types. The players here are the government and mass media. Because the function of mass media is to disseminate information to the population as a whole. (*really, this is what justifies their existance, otherwise news would only be subscription based to the few, a la Dow Jones corporation*)

So, how does this happen? The lazy way is news as copying press releases (e.g. Fox News and the Republican National Committee) But if you actually have a reporter who knows something about the subject and about her beat, you can do so much better. So the New York Times actually has a series of articles. One essentially communicating what the Federal Government (DHS, DHHS, CDC) are saying, articles about the city department of public health and the mayor. A couple articles of Q&A with expert doctors whose names are handy. Everything needed to let the public know that there is a plan and procedure, and noone is hiding, even with the unknowns. And throughout, the reporters know the questions to ask (not long ago, a very experienced New York Times reporter wrote an article looking back at how New York handled the 1918 Spanish Flu, no doubt giving all the young reporters there a master class on how these things are done.)

And this article is something even different. A close look at how the system works, by showing it in action. A school nurse spots the symptom (because school nurses, as well as other public health nurses, actually see entire populations first hand, not just reports that are sent in to them), the city department of public health recognizes a pattern that needs attention. Public health staff fan out and examine the affected. Centers for Disease Control are contacted and lab samples sent and tested. And a response is developed and enacted. All procedures that have been in place for years, tested on the ebbs and flows of daily life, and the occasional emergency. And refined by people who actually have to use them. Just the way you hope.

And it may be that swine flu is a little thing, a flash in the news cycle. But it is more. It is what scientists call a found experiment. Disaster response people have another name. A spontaneous exercise. Respond to something that uses all the command, control, and communications procedures that are in place for responding to something bigger. In this case, waiting for the day that a genetic drift mutates a strain of avian flu so that it is human-to-human transmittable. Just to make sure that everyone, President, cabinet secretaries, White House staff, agency leads, local officials, teachers, doctors, nurses, news media, and others, know what their role in a public health event will be.

First thoughts on teaching

I submitted my first set of grades for a class I'm teaching at Pitt. Part of my foray into academics. Which means I write my standard Lessons Observed (titled following the British tradition). I'm sure that those who have much more experience then I in teaching can have much to say.

Background:
I am teaching Database Design within the Department of Industrial Engineering at the University of Pittsburgh (Pitt). It is a service class, not part of the core curriculum of IE. But it is something that is very useful for engineers to know because of the large amounts of interaction with data. I have some background, because I use databases regularly, including some rather advanced uses for one of my research projects. The class is split between upperclassmen and graduate students, with most of the graduate students being professionals going for a master's degree. For most of them, this will be their only database course. There are two other places in a university where such a course would be offered. An information science (or possibly computer science) department would likely have a concentration in this field that examines data storage and retrieval. Business schools would sometimes have information management as a field, but more likely will have a course that teaches use of MS Access.

Goal:
The course objectives where to teach database design, with the understanding that the audience are engineers, i.e. people who would design applications that use a database, not database administrators or builders of database management systems. Similarly, I decided to make it a design class, not a course in driving a software package (MS Access), although using MS Access (or other database) would be assumed. I think that defining where the course fit in with other similar courses what the right idea, because it let me do the is-is not analysis when deciding what to include. I've done that with a course proposal I've submitted for fall, as well as the course I'm teaching in summer.

Software:
I did not specify software for the course. For the database, everyone ended up using one version of MS Access or another (three different versions were in use). While it probably would have been possible to do table design and queries in any database, teaching reports and user interfaces would have been too much of a stretch unless you use this. There were some problems because I don't use MS Access myself (I generally use PostGIS and BIRT for database and reporting) so I was probably light on the examples. For the next time, it would probably be safe to state use of MS Access. For diagramming, I gave them the choice of Visio or Dia. MS Visio was installed on the university computer labs (but not the department lab) and Dia could be freely downloaded. People ran into trouble navigating Visio (it was designed for programmers, not database designers) and most switched to Dia (databases is a base use case for Dia). While this is not software course, I probably should make a standard.

Projects:
For the project, not enough people had access to a real world project, so I had them create projects, and I had teams assigned to review each other (to include adding goals to the project). This was a heavier administrative burden then I realized it would be, and I was not able to keep track of all the communications. I should have had fewer mandated interactions, and let them do informal interaction. I was not sure about the quality of the peer-review, but most of the project reports specifically mentioned that having peer-review of the project at various stages gave the projects more clarity then they would have had otherwise.

Lectures:
This was a somewhat different subject then most in an engineering curriculum, since it is very soft. And not a subject I know deeply. I was walking the line between too much information to absorb on a weekly basis, but there was not enough content on a class session basis. Probably what it meant was I should have been doing more examples, especially working the software. Another thing I should do is organizational prep work. In particular, for the next course I will be preparing a lesson plan in addition to a lecture, to give each lecture more context.

One thing that did work was spend some time on motivating the day's topic at the beginning of every class. I suspect some of them thought I was only rambling, but others were able to make it work for them in putting things together.

Value at Risk Case Study:
I have in the back of my mind a capstone lectures that declares that mathematical models are a form of communication, and I expect every class I teach will have a way to talk about this. In this case there is a module on reporting, meaning how you present summary information from the database. And I used the Value at Risk statistic that factored into some of the riskier instruments used by various financial institutions. There was a clear division in how well this was understood. By the final, all the grad students made the connection between that and the related question on the final. Only a few of the undergrads did. Probably because the grad students were intimately familiar with the problems that financial institutions were having and it held their attention better.

Research and teaching:
I am now a firm believer that research helps teaching. One of the things that made this course interesting was the fact that I had actual database projects I worked with. While the actual projects could not be done in the course, I could extract parts. And I could use actual datasets and data collection tools as examples in the course. A couple of the students complained about the vagueness of the data specifications, which would be echoed by the people who actually deal with the data in question, so that was actually good.

Use of examples:
I had three datasets that I used for examples throughout. The US Department of Agriculture Standard Nutrient dataset (used in the preparation of nutrient labels on food), Pennsylvania Emergency Management Agency damage assessment collection forms, and the MS Northwind Traders example database. In particular, I used the USDA data from the very beginning of the course. It provided a rich set of understandable data so that it was clear what happened. I will probably do that again, using Pennsylvania as a dataset in my location class this summer.

Rep. Altmire (PA-4) goes to the Northland Public Library

My congressman has been going around his district this weekend having town hall meetings. His district covers the North Hills of Pittsburgh and parts of Beaver and Butler counties. All three of the regions mentioned are very conservative. Rep. Altmire is a Democrat in his second term. What follows are a summary of the meeting, then comments.

So, issues covered:

• Homeland security reports on extremists - The Department of Homeland Security has written (distributed? I think only in summary form) two reports detailing the dangers of left-wing and right-wing domestic extremism. While there does not seem to be any questions about the identification of eco-terrorists, al-Queda/Somali attempts to recruit Muslims, anarchist or progressive black bloc types (I'm paraphrasing) etc. in the report on left-wing extremists, there seems to be considerable reaction to the report on anti-abortion bombers, gun-rights activists reiterating a claim the feds are coming for their guns, and right-wing extremists recruiting among veterans. Rep. Altmire comments that as someone who is a veteran, owns guns, and is anti-abortion, he intends to find out just how this report is being used (since he is a member of the groups that are perceived to be most targeted by this report). This is of course met with loud and repeated cries of *yes or no* and repeated questions of what is he going to do. Of course, he is being asked for this even though noone has actually read the actual report (only a summary). He also noted that this is a regularly done report, which did not seem to get the same response during prior years (of course, it could be because only one of the two reports got routine attention).
• Fiscal spending - The well loved stimulus package. This was a nonsensical with a very vocal bloc seeming to be driven by a desire to scream out B***s***. (there was a point where Altmire stopped talking and called out one of the louder ones and called on him to yell it out a few more times so Altmire could actually discuss this.) What Altmire ended up doing was teaching what used to be called civics. Or, the Schoolhouse Rock "I'm just a bill." One of the "answer yes or no" folks was yelling this out as part of a question "Did you vote for the President's budget?" This is a non-sensical question because President's budgets do not come up for votes. Altmire pointed out that the President could propose, but budgets are debated and negotiated in Congress. In this case, starting in September 2008. There was also a lot of commentary about "voting on a 1000 page budget that they did not read." Altmire pointed out that everything was discussed and debated in the open, and there was nothing in that 1000+ page budget that every congressman/Senator had multiple opportunities to look at over the past 6 months (especially since one of the more recent reforms was blocking the ability to add to a spending measure 24 hours before votes). And all comments to that effect were, well, misleading (to put it charitably).
• Earmarks and PAC money. Many accusations of being pushed by corrupt money. Altmire pointed out that the current congress increased transparency by requiring congressmen to attach their name to any projects that were proposed, and that all of his campaign contributions were also public record. And the challenge was to find any major campaign contributors who were recipients of a funding request (earmark). The answer is none. The only gray example (which Altmire provided unprompted) was a major corporation who already had a contract with DoD who asked for assistance on that contract. (more rounds of B***S*** while he waited for people to amuse themselves before he could answer.)
  
• Big thing about attempts to block people from listening to any of the various right-wing talk shows. The Fairness doctrine was mentioned. It turns out, Altmire has for the second time, submitted a bill that prevent the Fairness doctrine from even coming up again. (and gives a reasoned argument against all such restrictions) This lead to further questions about his position on other un-proposed restrictions against right-wing talk shows.
• Accusations of not being interested in constituent views. Mind you, this question was being raised in the middle of a town hall meeting where the Rep. in question was fully aware that this was being loaded (he had a copy of the talking points that were being distributed to those in the town hall who were more vocally opposed to him). Altmire pointed out the irony of asking that question in a forum where he was physically present getting constituent views.

This was one of those meetings that is one of those "civics in action" moments. A representative in the legislative body of one of the most powerful nation on earth, exposing himself to open criticism, and openly addressing the comments and criticism. The value of transparancy, as various attacks tended to be responded with telling people the data that the question is about is openly available. And an elected representative fulfilling the role of civics educator, teaching constituents about what the American constitution and form of government. And defending the same.

Not one of those town halls that you would see on TV. It is characterized by the organized group that was their to listen to themselves yell out "B***S***" and "Yes or no" and that came with their sheets of talking points (Rep. Altmire's staff was able to get a copy of the talking points sheets being passed around so they gave him a copy) From the copy I got, it was majoring in minors. It asked about why he moved to Western PA after being on a congressman's staff (it was written to insinuate that he is an outsider that moved here soley to be a politician He was born in Western PA, then moved here in 1998 to work for the University of Pittsburgh Medical Center (recognized as a Pittsburgh 40 under 40 in 2003), then was elected to the House in 2006. And it also asked about his association with a lobbying firm under investigation (they had donated money to him. None of their clients were the focus of any funding requests/earmarks by him)

The other big question was on the concept of mandatory vs. discretionary spending. Most of the federal budget is due to social security, medicare and veterans (plus defense, which counts as discretionary), with 'mandatory' being a legal term. Many in the audience did not like that, claiming that this is not mandatory (presumably meaning they want to cut social security, medicare and veterans benefits).

And in the background of the whole thing is Article 1 of the United States Constitution, the one that states that all spending and taxes is under the perview of Congress, and that Congress deliberates and decides, with the President's proposal being no more then that. You don't think about it much, but Altmire speaks like he takes it seriously, and any congressman who does take his role seriously, especially with the transparancy rules that state that earmarks have to be accompanied by the member's name and that no changes are allowed to spending for 24 hours before approval, has no business complaining about a budget that he had 6 months to discuss and deliberate as being unknown to him.

The other undertone was about Obama's background. During the democratic primary, then Senator Clinton predicted that the Republican Party, should Obama be the Democratic nominee, would run a smear campaign. And this meeting showed this, with many of the attendees whispering about Obama being a Muslem, being really from Kenya, all things that are proved false multiple times (no muslem claims Obama is a muslem, the secretary of state for Hawaii has affirmed his native birth. Note that then Senators Obama and Clinton authorized the legal brief written during the presidental primaries that supported that McCain met the constitutional qualifications for president, dispite that he was not actually born in a state of the United States.)

Probably the most disturbing thing, the cries that the effects of Article 1 of the United States Constitution were "B***S***".

[Edit: The Pittsburgh Tribune-Review account of the meeting and the Republican National Committee robocalls and talking point is here:
At town-hall meeting, Altmire insists he's a 'centrist']

City gives final salute to slain officers

Pittsburgh Post-Gazette article

Joined by a dark blue sea of officers from around the region and around the country, Pittsburgh gave its final salute this afternoon to three policemen killed in the line of duty Saturday.

Today Pittsburgh said farewell to three of its own.  On Saturday morning, three police officers were killed in ambush when responding to a domestic disturbance call.  They lay in state from Thursday morning to Friday morning in the City-County building.  Friday, was the memorial service.  Some 30,000 police from around the country and Canada came to provide escort.  Because, as one of them said, 'that is what we do'

Pittsburgh is like many other big cities in the east.  Stable communities whose populations have made a stake in their city.  Including affirming those of their own who take on the badge and pledge to serve and protect.  

And so, along with many others, yesterday I was at the viewing along with other Red Crossers.  Like alot of other funerals, this is not about the dead, but about the living.  And their connections and relationships.  So, in addition to the viewing of the body, there was the meeting and greeting of those who have come.  And recognition of those joined by common bonds of relationships, of shared service, and a city we serve.  Along with the formalities, I was introduced to one of the police commissioners (?).  A former board member of the Red Cross.  Because we are two people sharing our duty to serve our city.

Beaver Falls Coffee & Tea: Kenya AA "Eagaads" Estate

Beaver Falls Coffee & Tea Blog
3219 4th Ave
Beaver Falls, PA 15010

We got a bag of the BFC&T Kenya AA "Eagaads" Estate - medium-dark roast this time.  Actually, since the last time I wrote about this roaster we did get a couple bags of coffee from other sources (meaning other coffee shops).  And one thing we've decided, we apparently have the taste buds that can appreciate the difference between a a good roaster and your standard coffee-shop coffee. 

[Digression]:  I regards Starbucks like I do lots of other things I use, the bottom end of good quality.  This tends to be a sweet spot on the quality/price curve.  A very good place to be until I decide that is a part of my life I want real good quality, and then it is a starting point.  No, the beans we got were not from Starbucks, but they were in the general neighborhood as far as quality is concerned. [/Digression]

We've brewed this a few times.  Mostly drip (we've been busy).  This morning I used the French press at 6 min.  Very happy with it.  It was richer in flavor then the beans we have gotten from other sources (presumably dark roasted, although it was not specified), and we like the medium-dark roast better then the BFC&T medium-light roast (enough subtleties for us, and the press time we need to get the flavor we like for medium-light is at the point when more bitterness comes out of the bean.)

Next, we are waiting for BFC&T to get their Guatamalan again.

Risk Mismanagement brought into the classroom

Joe Nocera, Risk Mismanagement, New York Times January 2, 2009


THERE AREN’T MANY widely told anecdotes about the current financial crisis, at least not yet, but there’s one that made the rounds in 2007, back when the big investment banks were first starting to write down billions of dollars in mortgage-backed derivatives and other so-called toxic securities. This was well before Bear Stearns collapsed, before Fannie Mae and Freddie Mac were taken over by the federal government, before Lehman fell and Merrill Lynch was sold and A.I.G. saved, before the $700 billion bailout bill was rushed into law. Before, that is, it became obvious that the risks taken by the largest banks and investment firms in the United States — and, indeed, in much of the Western world — were so excessive and foolhardy that they threatened to bring down the financial system itself. On the contrary: this was back when the major investment firms were still assuring investors that all was well, these little speed bumps notwithstanding — assurances based, in part, on their fantastically complex mathematical models for measuring the risk in their various portfolios.

I'm teaching a class in Database Design. Part of my scouting expedition into the worlds of academia. And tonight's class was on Reporting. Now, I think what everyone expects here is some tips on building reports using software packages (e.g. MS Access) and general design tips. I spent the session talking about why your doing reporting and focusing attention on the purpose of the report while doing the design.

And I've always had this fantasy in my head that I would try to bring in real world examples into teaching. For my midterm I put in an actual data collection form (which elicited a complaint about being vague and ambiguous. Complaints which are gladly voiced by the people who actually do the data collection using the form in question.)

So over the weekend, while thinking about what to do for this class (which was not looking very interesting) I thought about the financial crisis and all those little articles that I have been reading about the role of quants. And realizing that this was a reporting issue (i.e. the math was not the problem, it was in the communication, interpretation and use.)

The next question: How to do it. So I treated it as a case study. Operational environment. Problem description. Then led them through the thought process of the metric to be reported.

Next was the actual VaR metric. First explained the definition, then a discussion of where there was room for danger. Next, what the real definition was and a discussion of what people who VaR was reported to thought about it. Then a discussion of what happens when you measure something and use it as an evaluation metric (the metric was gamed.) And what happened when you did not pay attention (there was an asset which had characteristics that were hidden when viewed through the lens of VaR.)

Finally, the payoff. The wrong and right ways of using reports. Cribbed out of the New York Times article, which conveniently had both in one place.

While I won't say it was a free-wheeling discussion, there was some discussion. Not the risk taking opinionated you would get out of an MBA or policy course, but good enough. And it felt good to actually teach something.

Games I'm in

One of my many hobbies is playing table-top games. Of course, there is not that much time anymore, so these tend to be played only with imaginary friends (i.e. over the internet). And it does change its character over the internet (i.e. message boards and email). Because at that point it is more shared writing of fiction then a game. When I was deployed, a Marine in my unit enjoyed teasing me about this. After a point, when he commented that I seemed to feed this bit of entertainment, I replyed "if this is what you are ragging me on, I'm doing pretty good."

The premise of role-playing games is you play a character. And the character has a background which provides abilities, characteristics and motivation.

Like books and movies, there are many types of games, for different motivations for players. Some are more escapist, and the focus is on combating opponents in contests that follow set rules. These often involve lots of dice rolling. Dungeon & Dragons is the most well known of this type. Others are narrative, where the goal is to tell a good story and the characters are encouraged to take on weaknesses to advance a story line. FATE (Spirit of the Century) is like this. Others are focused on the characters, where the characters are written and played so they can be explored in depth. And in these later two, after the character is created, it almost disappears into the background, since the numbers do not matter as much as the description.

The idea is (unless one is escapist, or is playing a game that intentionally comedic) to see the world from another point of view, where you are faced with a different sets of abilities, skills, weaknesses, liabilities, strengths and knowledge. And you can make decisions that have consequences (in-game consequences that is).

I am in three games right now. In age:

Kanon: A non-commissioned officer in an army in a world that is mostly low-fantasy. Actually, because he is a very ordinary NCO, this is pre-industrial revolution. Other characters actually look more low-fantasy. There are actually two military branches in this scenario. One is focused on individual skill and its members have a upper class bearing to them. Kanon is part of the other one, whose focus is there professionalism. Kanon is sure that he is in the better branch. As he is also a racial minority (human) of a race that a large portion of which are in slavery, professional pride becomes a large part of how he engages the world. (http://www.kalyr.com, using Fudge (Wikipedia)

Adelson: a newly-minted university professor (biology) in 1920's United States. It turns out, finishing grad school and starting academia, brought him right back home. Which makes his mother very happy. The scenario (which he does not know anything about yet) is horror. What he knows is that there is an environmentalist vs. logging company fight not far from his university that he has been asked to study. (Call of Cthulhu, Wikipedia) )

Jason: a spaceship engineer/pilot who has just finished his enlistment in a space navy. And now needs work so he is signed up on a merchant ship. Actually, I don't know much about him yet. Yes, there is a character sheet and backstory, but the other players don't engage in much conversation, so there has not been much of a chance for him to have a personality yet. (Traveller (Wikipedia), using Fudge)

There is room for players in these games (well, not Cthulhu) if it is of interest.

In Memoriam: Keith Herber Post-Interview Chat Video

Yog RadioVision - In Memoriam: Keith Herber Post-Interview Chat Video

Posted using ShareThis

In memoriuam at Yog-Sothoth.com

Keith Herber was an author of stories and game scenarios based on the setting of stories by H. P. Lovecraft, commonly referred to as the Cthulhu Mythos. He passed away suddenly last Friday. By all accounts, people remember him for his guidance in working through the collaborative process of writing as well as being an editor deeply concerned with the quality of the works that passed through him to print.

The craft of writing can be difficult. There is a need to write not just to tell a story, but to make a setting that is complete enough that the story has a place to live and flow. To create a game setting is even harder. You have to make the setting deep and rich enought that the reader can imagine a place in the world, and a group playing has room to roam. And you have to create the mood, such that those playing

I'm involved in a game based on one of Keith Herber's last works. We've just started, and as we settle into the personalities we've created, anticipating the path ahead, and wondering what Keith Herber has created for us. And remembering him.

PSO: Music by the sea

One of my hobbies is backpacking.  I enjoy the opportunities to be in the natural world, to accept it on its own terms (including storms), and to be able to listen.  And when you think of such things in music, the first piece that comes to mind is Beethoven Symphony No. 6, and the Fantasia rendering of green pastors, interupted by storms, and return to green pastures.  Except, the real, living world is not like that.  Even in its most peaceful and idyllic, everything is just much more, . . . , random.  And anyplace that is alive has sounds (whether or not you are listening properly is another question) and the representation of the natural world by sweeping melodies, evokes an emotion or a picture rather then represents its true nature.  Last night's Dharma at Big Sur by John Adams was different.

Dharma provided a different sort of view of the outdoors.  And listening to it brings you through a full day.  There is the beginning of the day, and you can almost hear the low murmurs of surf and nature entering your subconscious as you slowly wake up in the morning.  As the sun rises, more and more of the world awakes, with waking birds adding clarity above (but not replacing) the ambient sounds that continue. And finally, you wake up yourself, looking forward to the day and adventures ahead of you with sights and sounds both familiar and not (the gongs provide the different sort of sound that keeps things interesting.).

But my friends also know that water and I go together when I go outdoors.  And so Dharma has this too.  It also has the raw power of nature crashing.  But even here, its not just that you can hear the waves crashing against the rocks on the shore, or the thunder and lighting, but you hear the constant rain that is in the backdrop, and the wind's whistle bends as it goes by.

It is a different part of life.  I mentioned how John Adams Short Ride in a Fast Machine

seemed to reflect the greatness in even the doing of everyday tasks in a very man-made environment.  But this seemed almost organic and a living and growing thing.  And like a good backpacking trip, I almost expect it to be different the next time I hear it.

PSO: Wow, Fast fingers

I remember hearing about this concert a few weeks ago. I had been sent links to YouTube videos with Yuja Wang, and hints that she was a pianist with amazing technique, a virtuoso. But those are words I've heard before, and did not pay much attention.

For all the talk of technique and raw talent, I believe that there is more to being proficient then ability, it is the ability to apply that talent to a context interacting with others that provides value above and beyond the application of talent. I had a discussion about this in regard to a book I did not like all that much. It was a claim at a person could grow in skill to a level way beyond any of those around her. My claim has always been is that ability past a certain point requires a person be interacting with others who can complement her gifts and talents, and point the way towards greater heights.

Where I lose interest in virtuosi is when they seem to only shine alone. And the concerti where a soloist and the orchestra take turns playing are not as interesting as when the orchestra and soloist play off each other.

From the point of view of looking for virtuoso, Ms. Wang did not disappoint. I was amused during the intermission of one man who said to her "Wow." But, for all the accolades that Yuja Wang has received over skill, there was more she could show during the Prokofiev. From the opening, where the orchestra seemed like it was sneaking in to the piano's opening, to numerous transitions throughout, the changes in focus from piano to orchestra seemed smooth, with the level of intensity and energy matching at the point of transition, both the piano and orchestra parts fitting together. Supporting each other as in conversation, not merely dropping out in an exchange of soliloqueys. And sharing the stage does not make the soloist lessor, but made Ms. Wang's talent shine.

And this impresses me more than the ample demonstration of her skills, even more the arrangement of Flight of the Bumblebee encore that she played. That her and the orchestra playing together make each other look better. And people like that, not only do they look good. You are glad they do and hope to see them again.

Juno: Movie review

(Wikipedia)

Juno is the story of a teenager who is pregnant. In 2007 when it came out, there was much controversy, as everyone on the pro-life/pro-choice/teenage pregnancy/values issues had a go at it. But I never saw it until now.

I was all expecting it to be one of those movies with the spunky teenage girl and clueless adults around her. But it was not. It is a teenage girl, dealing with the world through her mistakes and with the help of people around her.

Juno is a high school girl, who has gotten pregnant. The other major characters are the boy (surprise!), her best friend, and her parents. While initially she was going to have an abortion (apparently, her best friend had helped a few other girls in their school through the process) Juno changes her mind at the clinic and tries to give up her baby through adoption. The couple that is planning on adopting the baby become the other main characters in the movie.

Instead of the sassy teenager, Juno is introspective and comes to terms with what she recognizes as a mistake. And her focus is on dealing with it. There are others around her like the school nurse, some of the other classmates who look down on her. But those closest react differently. Her parents first reaction is disbelief, mostly blaming themselves for being bad parents. But they too, after first beating themselves up, switch into dealing with the situation, getting Juno through her pregnancy and the adoption.

What I liked about it was how they handle adversity. Yes, Juno and her parents (and the father) are hard on themselves for messing up. But the focus is on what has to happen next. The parents making sure that lessons are learned. The daughter learning that her parents are paying attention and care about her. And they guide her, with what wisdom they have. And in the end they have her respect.

None of the principles thinks they have it all together. Just people who have made mistakes, and need to figure out life. And in a movie, that is good.

Beaver Falls Coffee and Tea: Guatamalan Huehuetenango

Beaver Falls Coffee & Tea Blog

Beaver Falls Coffee & Tea is a coffee shop nearby Geneva College in Beaver Falls, PA. It was started by a couple of alumni who just didn't quite leave, basically the stereotypical college coffee shop. But last year they have started the next step, roasting the beans. We occasionally get beans from them, and this time we got some Guatemalan Huehuetenango (which were Direct Trade).

Why is this something of note? It turns out, like most food, coffee is time sensitive. The green beans are shipped from there origins, but all the steps after that effect the taste, starting from when they are roasted. It matters how long from roasting they are brewed, it also matters how long between grinding and brewing,and of course, how long they are exposed to direct heat (i.e. either take the coffee off the brewer and pour it in a thermos within 1/2 hour or dump it.)

So a year ago, Beaver Falls Coffee and Tea started roasting. The other roaster in the Pittsburgh area is La Prima, but they have no outlets in the North Hills (I know, I've asked their marketing director and after much discussion among the staff, came up blanks.) So we made a point of using them, even asking them for different style of roasts to try the range of roasts (light to dark).

We got the Guatemalan this week. It is a medium-light roast. Based on prior experience, we French-pressed it at 7 minutes (for most coffees, we French Press for 5 minutes). At this point, it was still a little light, but we were at the point where the bitterness in the bean was coming out, so I think we have the brew time just right. But the beans were probably too lightly roasted for my tastes.

The dark roasts are probably easier to quality control and these have gone well (which is why if you get coffee at places that ship their roasted beans from somewhere else, you should get dark roasts). The medium and lighter roasts have more nuances, but our experiences with Beaver Falls Coffee & Tea is that they are inconsistent, and their mediums and lights are often too light (not as much taste)

UPDATE: Batch 2 was this afternoon. This time I ground the beans to medium grind (rather then coarse like I usually do for French Press) and had a bit more beans in there. Better. I get more of the taste. Again, at 7 min, I can taste just a hint of the bitterness that tells me that I better not let the beans brew any longer. Of course, now I have to use a second filter to filter out the grounds, but this mostly works. But we'll probably make a point to get medium-dark roasted in the future.

Of Cowards and Conversation at the New York Times

Of Cowards and Conversation, as summary by Eric Etheridge

I'm in a hotel room, very tired, listening to CNN No Bull with with Campbell Brown. And I'm writing, very dangerous to write in public while tired, especially if race relations is within spitting distance of the topic.

The setting is two-fold. The New York Post has an editorial cartoon satarizing the shooting of a chimpanzee who mauled a child in Connecticut, depicting the chimpanzee as the economic stimulus bill. Al Sharpton and others are railing against it claiming the chimpanzee is actually representing a black man, namely President Obama. The other event was Attorney General Eric Holder gave a talk where he claimed that the U.S. “in things racial we have always been and continue to be, in too many ways, essentially a nation of cowards.”

My feeling, in short, is the fuss over the cartoon is a farce in itself, and that Holder is right. Because it is incredibly difficult to have an honest conversation about race relations. I'd say I've only been part of one, at an alumni board meeting at my graduate department. Like real conversations there were opinions, even disagreements,. And like good conversations there was exploring, depth and the exchange and understanding of points of view. And an example of the dielectic. When this particular conversation was over, one of the more vocal people there openly commented that what we just did was shocking because it was an honest conversation about race relations. Left unsaid was the fact we probably could not expect to experience another one anytime soon. It is very easy for conversations to become a self-justification session, which tends to ruin the whole thing. (most conversations about people trying to announce their own sanctification are rather boring)

My wife and I have accepted the fact that if we have kids, they may not follow our paths. And our hope (well, certainly mine) is that in whatever direction our kids go, we will have older people in our circle who can be with them, so they may have a mature example of whatever it is they do. But in my own life, I've been noticing that my circle is less and less broad in scope. It's not married life, I've just don't have the energy to explore that I used to. And as big as my world is, the only way I will add to it is if it is indeed complementary to what is there.

These, like many choices, is a decision that is made. In this case, openly and honestly. It has the advantage there are some points that have a very big point of entry. But it is a reality that I don't have as broad a circle as I used to. And the world that I can share in life is smaller then it used to be. And that is a bit sad.

QOTD: We did not mean to go to the opera on Valentine's Day

(The following is a very loose paraphrase)

K: What did you do yesterday
L: Well, we went to the opera. You see, when S asked me about Saturday or Sunday, I just picked Saturday night without looking at the calendar.
S: I kept on telling you "Are you sure?"
L: You can't just hint, you have to spell it out!
S: So, we went to the opera on Valentine's Day.
L: We've been so good about never doing anything on Valentine's day

K: That is so you

Fresh Air talks about Afghanistan - February 4, 2009

Fresh Air is one of my favorite radio programs. It is a show of interviews. And Terry Groves is a good interviewer, who has the ability to ask the questions at the heart of the matter, and uses the interview to draw out the story the interviewee has to tell.
This episode revolves around the current campaign in Afghanistan and centers around two interviews. The first interview is with Sarah Chayes (Taliban Terrorizing Afghanistan), a former NPR reporter who was assigned to Afghanistan in 2001, and has stayed there to run an NGO (Non-Governmental Organization) that is establishing an industry in Afghanistan. The second is with Ahmed Rashid (Taliban Activity Up In Pakistan), a Pakistani journalist. Terry Groves asks them about the conditions they see in Central Asia, what they think about the U.S. troop increase promised by President Barack Obama, and what they think about the future.

Some highlights:

• Both of them are highly supportive of the idea to add 30,000 additional American troops into Afghanistan.  The biggest issue they both see is the lawlessness and lack of security, and 30,000 additional American troops will go a long way.
• Both of them are almost fawning in admiration for U.S. General Petraeus, the new head of the U.S. Central Command, for his leadership in Iraq as the head of U.S. forces in Iraq, and for Petraeus' understanding of counter-insurgency.  Listening to them, it reminded me of a Sargeant who unshamingly praised General Patraeus as a scholar-warrior, and this was when Petraeus was just starting in his post in Iraq.
• Cheyas sees as the most difficult problem in Afghanistan is the corruption in the Afghanistan government. And she sees President Karzai as part of the problem.  It is not just the fact that there is corruption.  It is the severity (not extent) of the corruption that is wearing out the people.  And they remember that it was the corruption among the rulers that made people welcome the Taliban many years ago in the first place.
• Both of them blame the previous American administration for not pressing the Afghanistan or Pakistan governments to improve in governance all this time.  That one reason for the lack of progress was because no progress was required, the Americans would support the Afghan and Pakistani governments regardless of their progress.

I especially find it interesting that both welcome the additional U.S. military forces in the region.  They both see the U.S. military as the most capable and beneficial force in the area.  And one that has learned much over the past few years.  But they are few in number, and they need other expertise to help the real issue, improving the Afghanistan ability to govern themselves.  Which is not something that is reasonably expected to be a job that goes to the U.S. military.  (of course, they get that job like all other jobs.)

Book Review: Desktop GIS: Mapping the Planet with Open Source by Gary Sherman

Desktop GIS: Mapping the Planet with Open Source by Gary Sherman

My review

rating: 4 of 5 stars
Desktop GIS covers Open Source software for use as a Geographic Information Systems (GIS). In particular, it covers the following programs and libraries:

- Quantum GIS
- uDig
- GRASS(and its Java front end JGrass http://www.jgrass.org)
- PROJ.4
- GDAL/OGR
- PostGIS
- FWTools
- GMT

At the beginning of the book, the author outlines three classes of users. A casual user who only needs to look at data found from elsewhere, an intermediate user who visualizes but also creates or converts data, and an advanced user who has the need to do spatial analysis. To cover all of these is an ambitious goal, which is further diluted by the authors felt need to cover the entirety of open source mapping in one book.

What the author does well is to identify tools, and explains what can do what with enough to get you started. So Quantum GIS and uDig are the viewers, able to read almost any GIS format (in particular the readily-available ESRI Shapefiles as well as PostGIS). GDAL and OGR that can convert anything to anything (including delimited text. These are often distributed as FWTools). PROJ.4 that converts projections from one to another (and is embedded in everything). PostGIS which is the spatial database that enables spatial analysis. And GRASS, which is the full-fledged can-do-everything-but-is-hard-to-learn tool. And then some random programs that either do something completely different (e.g. OSSIM, which is an imagery analysis tool) or can make a picture of a map with lots of options (e.g. GMT).

What he provides are the basics for the casual or intermediate user. The advanced GIS analyst would only have a taste of what GRASS can do, but would not know what can be done with it. Similarly, while the intermediate user will get a sense of what PostGIS can do, the lack of space to cover spatial extensions to SQL supported by PostGIS loses its value to the advanced user. What could have made this book better was more focus. If the author was compelled to have a survey of all open source mapping, it may have gone into an appendix with a few paragraphs for each of the miscellaneous tools. But for the book, one good focus would have been the GIS stack comprising of data storage, data analysis and data viewing. Basically the open source counterpart to the ArcGIS/Oracle with Spatial Extensions. And everything that does not play a role in the stack, gets pushed into the appendix.

What could have been done? The book tended to be organized by tool. But once past the casual user, almost all tasks required multiple tools. I would have gone:

1. Viewing data (raster, vector, introduction to QGIS, uDig, GRASS)
2. Converting data/data formats (GDAL/OGR, Maybe PROJ.4)
3. Creating/Editing data (digitizing, importing)
4. Spatial databases (PostGIS)
5. Geoprocessing/spatial analysis - GRASS, PostGIS, R-spatstats
6. Tools integration (QGIS/uDig with GRASS/PostGIS)
7. Scripting
8. Customization

and everything that did not fall into this gets a page in an appendix.

Knowing where to start is a big help. Most of the websites either focus on one product, or try to teach everything as being equally important. Gary Sherman at least identifies the main building blocks. (QGIS or uDig, GDAL/OGR/PROJ.4, PostGIS, GRASS) and gives enough to get started. And this can be very helpful, so at least the starting analyst knows where to start.

What would be next? For the person still working within the GIS stack (as opposed to a completely different topic, like imagery analysis which is OSSIM's territory) there are a few obvious topics.

1. PostGIS - Spatial databases with SQL. Maybe even connections with ArcGIS. Even a short (10 pg) appendix would have done wonders here.
2. GRASS - The author devotes an additional appendix to this. But to do this right, you probably need to refer to Open Source GIS: A GRASS GIS Approach
3. R spatial statistics packages. Applied Spatial Data Analysis with R would cover this.

Mostly, a good book to get started in GIS using Open Source tools. Casual users would be well served. Intermediate users would get started and can find the rest using the internet. Advanced users are going to miss alot (to the point they don't even realize that these tools were a worthy alternative).



View all my reviews at Goodreads.

Loading lat/long data into PostGIS

Base scenario. I have a datatable with longitude and latitude data that I want to look at using spatial tools. And I have shape files for the metropolitan area that I want to use them with. So, in other words I have the following.

1. ESRI shapefiles
2. Data table with latitude and longitude

The plan is to load the data table into PostGIS, then I can pull them into a GIS system like uDig or Quantum GIS or various ESRI products. Because PostGIS can do transforms, but ESRI shapefiles are what they are, the procedure is as follows:

1. Identify SRID (spatial reference ID) for the ESRI shapefiles. This should include units (feet, meters, or degrees)
2. Identify a corresponding SRID that used lat long degrees
3. Load data table into PostGIS
4. Add geometry (points) data to datatable, including transformation from degrees to feet.
5. Optional: export PostGIS to ESRI Shapefile if needed.

1. Identify SRID (spatial reference ID) for the ESRI shapefiles. This should include units (feet, meters, or degrees)

Shapefiles usually come as a set of files inside a directory. When they come from an official source, one of these should be a *.prj file. This is an example of one

PROJCS["NAD_1983_StatePlane_Pennsylvania_South_FIPS_3702_Feet",
GEOGCS["GCS_North_American_1983",
DATUM["D_North_American_1983", SPHEROID["GRS_1980",6378137.0,298.257222101]],
PRIMEM["Greenwich",0.0],
UNIT["Degree",0.0174532925199433]], PROJECTION["Lambert_Conformal_Conic"], PARAMETER["False_Easting",1968500.0],
PARAMETER["False_Northing",0.0],
PARAMETER["Central_Meridian",-77.75],
PARAMETER["Standard_Parallel_1",39.93333333333333],
PARAMETER["Standard_Parallel_2",40.96666666666667],
PARAMETER["Latitude_Of_Origin",39.33333333333334],
UNIT["Foot_US",0.3048006096012192]]

The first line identifies the projection, using something that should approach a WKT ("Well Known Text") name of a projection. In PostGIS, when the PostgreSQL database was geographically enabled, a table "spatial_ref_sys" was created that has a list of all SRID with names. Searching on this table should reveal a projection that has a name very similar to this one. That will define the target SRID for the whole project. The quick way to do this is to write a query:

SELECT srid, auth_name, auth_srid, srtext, proj4text
FROM spatial_ref_sys
WHERE srtext LIKE '%Pennsylvania%'
ORDER BY srtext;

In this case, we find:

2272; "PROJCS["NAD83 / Pennsylvania South (ftUS)", GEOGCS["NAD83", DATUM["North_American_Datum_1983",SPHEROID["GRS 1980", 6378137,298.257222101, AUTHORITY["EPSG","7019"]],

Note that in this case the key parts were NAD83, Pennsylvania South, and ft.

2. Identify a corresponding SRID that used long lat degrees
Another search through the "spatial_ref_sys" table finds only projections that are associated with feet or meters. So a broader search should be used to get


SELECT srid, auth_name, auth_srid, srtext, proj4text
FROM spatial_ref_sys
WHERE srtext LIKE '%NAD83%'
ORDER BY srtext;

This eventually reveals one projection that

4269;"GEOGCS["NAD83",DATUM["North_American_Datum_1983",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]], AUTHORITY["EPSG","6269"]], PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4269"]]"

The key here is that the 'UNIT' is "degree" as opposed to "Survey feet" or "Meters".

3. Load data table into PostGIS

First, by this point the PostgreSQL database was created using a template that has PostGIS enabled. I created an SQL file with all the Create Table commands ahead of time. They look like this:

CREATE TABLE table2004
(
case_no character(7) NOT NULL,
week integer,
"month" character(3),
eventdate date,
bg_lat numeric(12,6),
bg_long numeric(12,6),
serial2004 serial NOT NULL,
CONSTRAINT table2004_pk PRIMARY KEY (serial2004)
)
WITH (OIDS=TRUE);
ALTER TABLE table2004 OWNER TO userid;

Key point is that I need something an integer field that is a primary key, or at some points indexing becomes a problem. So I made a serial field to be an index (because my case_no field is a text field so was unsuitable). Another issue is the geographic indexing also likes an OID field, so I set OIDS = TRUE. Most current PostgreSQL documentation mentions that this is deprecated, but PostGIS databases would be an exception.

Next, a geographically (geometry) enabled field is added. PostGIS has a PL/pgsql function named "AddGeometryColumn" that does this. Note that earlier, I established that the target SRID is 2272. Next, because there will be spatial searches, I will create an index on the geometry column.

SELECT AddGeometryColumn('public', 'table2004', 'longlat', 2272, 'POINT', 2);

CREATE INDEX idx_table2004_longlat ON table2004
USING GIST (longlat);

Next, the data is loaded. I had exported the data table to a csv file, then used VIM to surround each line with an INSERT INTO statement so it looked like this:

INSERT INTO table2004 (week, month, eventdate, case_no, bg_lat, bg_long) VALUES( 1,'JUL','07/01/2004', 6666097,40.382649,-79.803297);

This loads the data into PostgreSQL. The next step is to actually create the geographic data.

4. Add geometry (points) data to datatable, including transformation from degrees to feet.

Now that there is a geometry column, and the lat/long information in the database as a source, the point needs to be added as a point. This is done through an UPDATE statement, using the PL/pgsql function "transform" "PointFromText". Note that the SRID that corresponded to a latlong representation was 4269. Also note that POINT is expressed in x, y. So longitude comes before latitude.

UPDATE table2004 SET longlat = transform(PointFromText('POINT(' || bg_long || ' ' || bg_lat || ')', 4269), 2272);

Now, this is done, and the table can be viewed using a GIS viewer that can connect to a spatial database.

Some of the data is messy, in particular, I was not able to get lat/long for all the addresses. So I created a View that removed these from the result (or I get a bunch of points at 0, 0

CREATE OR REPLACE VIEW table2004work AS
SELECT table2004.week,table2004.month,table2004.eventdate, table2004.case_no,table2004.bg_lat,table2004.bg_long, table2004.serial2004, table2004.longlat
FROM table2004
WHERE table2004.bg_lat > 1 AND table2004.bg_long < (-1);

ALTER TABLE table2004work OWNER TO userid;
COMMENT ON VIEW table2004work IS 'Remove 0,0 data from map';

So this provides a cleaner dataset, and it is also accessible from a GIS viewer.

5. Optional: export PostGIS to ESRI Shapefile if needed.

To export from the PostGIS database to an ESRI Shapefile (if it needed to be given to someone who only need to look at it), the PostgreSQL database should be running. Then pgsql2shp can be run. This should be done after creating a data directory for the shape file to exist. From the terminal, cd into the target directory and run

pgsql2shp -f table2004working.shp datatable table2004work

This creates a shapefile with .shp, .dbg and .shx files. Note that you may need to create a .prj file as well, but this can be done from within a GIS viewer.


Note: Much of this was found through the great documentation available at Refractions PostGIS site and the Boston GIS website, which has multiple tutorials on PostGIS and PostgreSQL.

[Edit]  In the 'srtext' field, the PROJCS identifies the SRID as belonging to a 'projection' which is a curved surface put on a flat plane.  When there is no projection, it is a pure GEOGCS (geographic coordinate system) and it is in degrees lat/long

Represent (The Open Blog at the New York Times)

Represent (from Open: All the news that's fit to printf() )

More than eight million people live in the five boroughs of New York City. They have more than 150 elected legislative representatives, from the local level to Congress. Keeping tabs on the people who represent you is a difficult task.
Represent

That’s the idea behind Represent, an interactive feature we launched in beta last week. Using your address as a starting point, Represent figures out which political districts you live in and who represents you at different levels of government. It draws maps that show how where you live fits into the political geography of the city. And using information collected from around the Web, it presents a customized activity stream that tracks what the people who represent you are doing.

Hat tip to Paul Ramsey (The Clever Elephant) for pointing to this.

I've been teach myself the use of Geographic Information Systems (GIS). Specifically, the use of spatially enabled database. The general idea is take a dataset that includes locations (such as addresses) and display and analyze the spatially. The display part is actually pretty easy. From a list of addresses there are many tools to get a longitude and latitude, then create an ESRI shapefile. I've been using uDig to view shapefiles.

But the real goal is to do analysis. You could link a GIS viewer with a database to view items based on some parameter. uDig, QGIS, and ArcView all have this capability. But the real way to do this is to connect this to a spatial database so you can use all of your standard reporting tools. And you could produce reports based on any set of criteria (such as buffers or overlays) Commercially, the provider of GIS tools is ESRI with their ArcGIS family of systems. And if you want to do more then just look at the data, there are a very few database management systems that could handle spatial data. Oracle, Informix, DB2, SQL Server (I think the very latest version has spatial capability), and PostGIS. For the commercial systems, they don't come fully spatially enabled out of the box, you have to buy the more expensive versions. PostGIS is the exception. It is freely distributable. Which is important if you are just exploring and don't have money. Or you are looking at applications that are for someone who does not have money.

Anyway, it turns out the technology team at the New York Times has been doing a variety of projects with the website, using technology to provide many different visualizations of the information (not just stories) in the New York Times. Some of the good ones are the Faces of the Dead project, which provides stories about individual servicemen and women killed in Iraq, and the Election Results Map, which graphically displays the election results in a variety of different ways, which tells a story much differently then you can using only words.



The Represent project is another project. It takes an address, then determines what districts you live in (local, state federal) and who represents you. Then it goes and searches the New York Times, and a variety of data sources to let you know what they have been up to, how they have voted recently, and what is coming up (in their public schedule. And the tools they use was driven by the fact they needed to use an open source (freely available) spatial database, which means PostGIS. (They use GeoDjango web framework to build the website, which includes extensions to Django web framework that allow the Object Relational Mapper (ORM) to work with spatial data fields.

Why is this cool? Well, it is what some call a mash-up, it is a way to re-purpose and reuse data, in this case news articles. And in the case of the New York Times, create something that cannot exist anywhere else, to deliver timely, local information. Just what a newspaper is supposed to do.

PSO: American composers and a new day

(also can be found at http://pittsburghsymphony.blogs.com/outside/)

This weekend's concert featured Gabriela Montero, fresh off a little Washington DC performance of Air and Simple Gifts in a small venue of 2 million people and a worldwide TV audience. It promised to be something interesting.

Bookended by Berber and Mendelson, Ms. Montero played Rhapsody in Blue by Gershwin and the live performance premier of Air and Simple Gifts by John Williams.  Rhapsody in Blue is probably best known for being played in commercials. Air and Simple Gifts, for its place in the recent 2009 U.S. Presidential Inauguration. For both, Ms. Montero made hearing these pieces live a treat. If your concept of Rhapsody in Blue is the driving theme of a TV commercial, the PSO performance presented many sides to the theme. From the hard driving we're used to hearing, to a light dancing on piano, to strings and horns playing off each other, and winds taking the theme exposed on their own. There is even room for a banjo. And listening to a piano improvise her parts is a treat, especially when someone like Ms. Montero, whose a skilled improvisationist, is the one on stage.

Air and Simple Gifts is also transformed in a live performance. While on TV watching the inauguration it sounded like a straight copy of the Copland Appalachian Spring, live shows many more layers that seem like mere rumbling on the televised version. You can hear the counterpoint in the strings as clarinet and piano take on the main Simple Gifts theme. The piano part becomes an integral weave throughout the piece instead of a murmur under the strings and wind.

I was amused at the program's description of George Gershwin in relation to his contemporary classical composers. It describes Gershwin as a jazz composer dabbling in classical composition. Certainly he is better known for his jazz and folk-style music, along with his many musicals. But this description also fits another composer, who is better known for writing music that is played in a popular culture setting, whose tunes also embed themselves in popular consciousness, setting the theme for a work, or even an event. John Williams, composer for film, and Presidential inaugurals. The comparison is greater when one looks at the criticisms of the piece in various newspapers (not about the playing, but the piece itself). Similar to Gershwin, John Williams is best known for writing music for a popular setting, in his case movie music. His music sticks and conjures up strong images
in your mind. What images do I here in Air and Simple Gifts? There is a quiet beginning of a new day, then waking up for the work to do with the Appalachian Spring theme with the clarinet. And the rest of the piece giving that sense that there is much work to be done by all, good work, but lots of it. And for this piece, at this time, that sounds about right.

A concert with modern American pieces just fits this week, with an inauguration just behind us. And it seemed to show the contrast between a brasher, more cocky America of the not-too-distant past, and one that has many challenges ahead in a new day. It made for a worthy performance. Of course, there was the also timely improvisational encore at the end of the first half, when Ms. Montero took requests for a theme to play off of. And what followed was many spirited variations on 'Here we go Steelers, here we go!' Very timely indeed.

Massaging text with Vim

I'm teaching a database design class this semester. And while I could do most of it using a database I already know (and I have been doing the samples so far using SQLite. I'm sure just about all the students are using MS Access), I'm also taking the opportunity to learn PostgreSQL. (I'm using PostgreSQL because I have a project that will require a spatial database at some point. And the only real options are Oracle and PostGIS, an extension of PostgreSQL. Since funding is at a premium, PostGIS it is.) PostgreSQL is a bit of a step for me, because it is a server-based database, and requires administration. So stuff to learn.

The other thing I need to learn is how to get data into it. MS Access, SQLite and Derby all have mechanisms for pulling in your average CSV file. But the general way that databases are populated (and backups made) is via SQL files. But data does not come in SQL files, they come in delimited text. So the way to do this is take a text file, then build SQL around the data.

The procedure is straight forward. After the tables are created in the database (using CREATE TABLE statements) you just read in a text file (usually with a .sql extension just so you can recognize it when you see it again). And the text file is not hard either. It starts out as
INSERT INTO tablename(fieldname1, fieldname2) VALUES ('value1', 'value2');

Now, it is not too hard to convert the delimited text into the 'value1', 'value2' format using a bunch of search and replace operations (and accounting for things like escaped "'" and the like). The trick is getting the rest of the text around it. It could be as simple as a cut and paste. Multiple pastes. But some of these files had 100000s of lines. That is a lot of [CTRL]-[v]

Like a lot of people whose fortune is it to program, I've used a number of text editors to write code. Microsoft Visual Studio, Eclipse, JEdit are my current set. But one editor that I've always kept around is Vim. And it is for things like this I do so.

So, I have something I need to do for a few 100000 times. One way to do this would be to write a script. Not too hard, read a line of text, use it to build a new line of text with all the SQL in the proper place. Write a file. But it is a bit of overhead with having the interpreter running and managing the file handles, etc.

But another way to do this is just write a macro or script inside the editor. But this would not be fun in Visual Basic. Vim, like MS Word, has a macro recorder. Basically, it records a bunch of key strokes and allows you to replay them however many times you need to. So I start by doing one line. Then I put the INSERT INTO tablename(fieldname1, fieldname2) VALUES ( on its own line at the beginning of thie file (before the first line of data). "ayy puts this into a memory register 'a' within Vim (not the system clipboard. I tried this, but it is much slower.) Now the magic starts.

q1 (starts recording in register '1')
J (joins the following line with the current line)
[CTRL]-[a] (moves cursor to the end of the line and switches to insert text mode)
); (finishes off the line of SQL)
[ESC] (ends insert text mode)
j (moves down one line)
^ (goes to beginning of current line)
"aP" (inserts contents of register 'a')
q (stops recording the macro)

Now, to use this, I test this a couple of times to make sure it does what I want.


@1 (replay macro '1')

Looks good.

100@1 (macro '1' 100 times)

Or

500000@1 (macro '1' 500000 times)

and go to bed.