Postgres/Postgis Tips

Tip 1: Importing DBF Files

shp2pgsql command is mainly to import shapefiles. However, it does come with an optional parameter -n that allows to import dbf files. You might need to install postgis in order to have shp2pgsql command.


> shp2pgsql -n filename > outfile.sql
> pgsql -h hostname -U username -d database -f outfile.sql


The above two commands can be further shortened into a single command

> shp2pgsql -n filename tableName dbName | psql -d dbName


Tip 2: Calculating Area
Use transform function (st_transform) to project geometry onto some spatial reference system. I would recommend using a projection system that preserves area. Then use the st_area function. The unit depends on the unit used by the projected spatial reference system; most likely it will be in meters (or square meters for area).


> select st_area(st_transform(the_geom, ) from table


Tip 3: Counting number of words
Unlike char_length, which returns number of characters in a string, there is no function to count number of words. However, you can nest two function in order to number of words.


> select array_upper(regexp_split_to_array('this is trial. it should return 7', E'\\s'), 1);

Enjoy

Automating Interactive Shell Script (Mac)

Automation

Shell scripting is a powerful way to automate things. However, writing a shell script to automate interactive shell commands can be tricky. For instance, I weekly download log files from the server to my local machine. For this, I use scp command. While I had written shell script to automate this process, it wasn’t really an automation. The scp command requires password, which I cannot pass as a parameter but has to be entered interactively.  Thus, my script wasn’t really an automation. To overcome this specific issue (entering password for using scp command), you can use RSA key as suggested here. However, this would only solve the password problem. Also for a newbie like me who is entered the Mac world recently, this seemed to be much more involving. I was looking for an approach that was much more generic, simple and less geeky.
Luckily, today I came across another solution that satisfy all of my above constraints.  However, the simplicity of this second approach approach comes with the price of being less secure. As explained here, the second solution is to use the “expect” command. For instance below a sample code that shows how to use expect command to write an interactive shell script.


#!/usr/bin/expect -f
spawn scp user@hostname.com:/path_to_file/filename.tar /tmp/
expect {
-re ".*sword.*" {
exp_send "password\r"
exp_continue
}
}
exit


In order to get the above code, however, make sure you have following things correctly done:

1. Make sure the first line is correct: The first line should be address leading to the location where the compiler/interpreter (not sure) can find expect command. Usually it is at /usr/bin/. If you not sure, open a terminal and type “which expect”. This will tell the location where the compiler can find the expect command. Also include trailing -f
2. Spawn: Include “spawn” before all your commands
3. exp_continue: Also include exp_continue after sending the password
4. Running the file using ./: Normally we run a shell script using the “/bin/sh file_to_execute.sh”. If you try running the code using this method, it will generate errors. Make sure you run the code using “./file_to_execute.sh“

Expect command, as used above, is not limited to sending password but can be used to send any other parameter. See the original post for an example.This make expect command much more generic than using RSA key. However, as you might have notice that you have explicitly type the password in the file. Anyone with system administrator privileges can easily open you shell script and look at the password. Hence, as I said before, the simplicity of the “expect” command tradeoffs security. Nevertheless, I am quite thrilled to find this and having fun with my new or real automation script.
Enjoy

Calculating Geographic Distance

Any geographical analysis almost invariably involves calculating distance between two points on the surface of the Earth (or two geographic coordinates). Below are four different options/formulas to calculate geographic distance. Each formula makes different assumption about the Earth’s shape and thereby has different accuracy and computational complexity.

Formula 1: Pythagorus’ distance formula

distance = ( (lat2 – lat1) ** 2 + (lng2 – lng1) ** 2) ** 0.5

where
lat1,lat2, lng1, lng2 – lat/lng in decimal degrees

Assumption: Earth is flat.
Notes about error: Although, the Earth is not flat, over a very small distance it can be reasonably assumed to be flat. Thus, Pythagorus formula can be used to calculate geographic distance between two geographic coordinates when two geographic coordinates are close to each other. However, the definition of ‘close’ remains ambiguous. I haven’t found any numerical value to define ‘close’ and identifying that value is an aspect of my next post.

Formula 2: Modified Pythagorus’ distance formula

distance = (
[69.1 * (lat2 - lat1)] ** 2 +
[53.0 * (lng2 - lng1)] ** 2
)** 0.5

Notes: This formula is same as Pythagorus’ formula but includes correction for the spherical surface of the Earth. Again, this is not very accurate formula and should be used only for small distances

Formula 3: Great Circle Distance Formula

distance = R * arccos( [sin(lat1) * sin(lat2)] + [cos(lat1) * cos(lat2) * cos(lon2-lon1)] )

where
R = radius of the Earth (equatorial radius)
lat1, lat2, lon1, lon2 – latitude/longitude in radians.

Assumption: Earth is spherical
Notes about error: One of the problem with Great Circle formula is that cos(x) value tends to be unreliable. For example, see below (Ref)

cos (5 degrees) = 0.996194698
cos (1 degree) = 0.999847695
cos (1 minute) = 0.9999999577
cos (1 second) = 0.9999999999882
cos (0.05 sec) = 0.999999999999971

As can be seen from above values, the difference between cos(1 minute) and cos (1 sec) starts to appear only after 8 decimal places. Thus, make sure to use datatype that has sufficient long to capture these minute differences. In Java, always use “double” (and not float) to perform these calculations.

Formula 4: Haversine Formula

d = R * 2 arcsin ( sqrt[ sin_square(dlat/2) + cos(lat1)*cos(lat2)*sin_square(dlon/2)] )

where
R = radius of the Earth
dlat = lat2 – lat1
dlon = lon2 – lon1
lat1, lat1, lon1, lon2 are in radians

Assumption: Earth is spherical, but includes some correction for flattening around poles
Notes about error: Haversine formula is also based on spherical model of the Earth but is considered more accurate than the Great Circle Formula.

Formula 5: Vincety’s Algorithm

Reference to Vincety’s algorithm

Assumption: Earth is ellipsoidal.
Notes about error: Vincety’s algorithm is the most accurate formula to calculate distance between two geographic coordinates. Based on empirical evidences, it has been shown to be accurate within 0.5 mm. However, Vincety’s algorithm is an iterative distance calculation algorithm and thereby, as compared to any of the above formula, it takes longer time.

References:
1. Vincenty’s Algorithm – http://www.movable-type.co.uk/scripts/latlong-vincenty.html
2. Modified Pythagorus Formula – http://www.meridianworlddata.com/Distance-Calculation.asp

Knee Surgery (ACL Reconstruction) Journal

17th June 2009

In an unfortunate event of mugging and assault, I severely damaged my left knee.There are four ligaments in and around a knee: ACL, PCL, MCL, and FCL. ACL and PCL corss each other and are at the center of the knee. They help prevent excessive forward and backward movement of the knee. MCL and FCL are around the knee and prevent sideways movement of the knee. Lastly, there is a meniscus that acts as a shock absorber and prevents rubbing of thigh and shine bone and, thereby prevents arthritics.

In my case, I completely ruptured ACL and MCL and the lateral meniscus got displaced.

19th June – 25th June

I saw four orthopaedic specialists, each having a different opinion, suggestion and a rational. Below is a table comparing their suggestions and arguments.

Specialist	Saw on	ACL Reconstruction	MCL Reconstruction	Fixing Meinscus	Surgery Time	No. of Surgeries
1st	19th June	Yes	No – grows naturally	Yes	After swelling goes down – difficult to operate	1
2nd	23th June	Yes	Yes	Yes	After swelling goes down – difficult to analyze and operate and let the knee heel by itself for sometime	1
3rd	24th June	Yes	Yes	Yes	After swelling goes down	2: ACL surgery should be followed by leg extension physiothearphy. Whereas MCL surgery should be followed by giving a knee rest for two-three weeks. Thus both cannot be operated at the same time
4th	25th June	Yes	No:  grows naturally	Yes	As soon as possible. The major concern for him is lateral meinscus. If not treated soon, lateral meniscus might get more damaged increasing chances of arthritics	1

Jun 26th 2009

I decided to go with the fourth specialist. However, If one goes with the above table, the fourth specialist has a very unconventional beliefs. In contrast to all other specialist, the fourth specialist has suggested for a surgery as soon as possible. Nevertheless, my decision to stick with the fourth specialist is based on the fact that he is affiliated with big sports team and specializes in ACL reconstruction. Another big motivation is that I wan’t to get rid of this knee pain as soon as possible and getting surgery quickly done will help with alleviate this problem.

June 30th 2009
Follow up meeting with the fourth specialist. To reduce swelling, the doctor took out some blood from the knee. This helped both in reducing the pain and swelling

July 1st 2009
Surgery started at 1:15. Doctors gave me general anesthesia and bored three holes in my knee. A cadaver was used to reconstruct ACL. Doctors were able to salvage lateral meniscus.

July 2rd 2009
Follow-up meeting with the specialist. The specialist saw the knee and asked his assistant to take out some more blood from my left knee to reduce swelling. Gave me some instructions and asked to visit him again after a month

July 3rd 2009
Started using CPM. The other machine, ice-therapy machine, was not so useful. I found using regular ice pack to be more convenient.

July 10th 2009
Started physio-therapy. Most of the first session was spent on massaging knee and getting my left kneecap mobile.

Related articles by Zemanta

• Knowing the needs of knees (nationalpost.com)

30 Day Workout Challenge

Challenge: Go for 30 days workout streak.
Motivation: They say it takes 30 days to make a habit.
Starting Date: 17th May 2009
Rules:

1. Keep track of thoughts. Especially note down all the positive and negative thoughts.
2. No breaks. Although, on some days I can go for easy workout such as walking only. However, break days should be at-least 3 days apart.

Contestants

Meet the two contestant of this challenge.

The Procrastinator	The Achiever

The Procrastinator: He is one powerful evil and the master of rationality and logic. But the problem is he uses rational and logical argument to procrastinate things. He is very active only in building logical arguments to procrastinate important things in life. The very existence of this challenge is because of this powerful procrastinator.

The Achiever: He is a weak humble soul who wants to achieve lot of things in life. He believes in hard work but often losses to the procrastinator.

Ever since I existed, the two have struggled to gain control over my mind. Generally, its the procrastinator that wins.  Let see who wins this challenge.

Score board

Scoring mechanism: if I go to the gym, the achiever gets a point. Otherwise, the procrastinator gets a point

The Achiever:   6
The Procrastinator: 1

The Debate

Day 6 – Friday May 22nd

• bummer….so finally procrastinator got a point. Yesterday I thought I will go to gym after in the evening. However, in the evening got busy in party and came back home at 10 PM. I could have still gone to gym as the gym is open 24 hrs and is just one block from my apartment. But, influenced by the procrastinator, I started watching a movie.

Day 5 – Thursday May 21st

• I guessed today the procrastinator worked silently. Although I went to the gym I was not able to enjoy it.I skipped cardio, but did following exercise
  • 3 sets of compound exercise ( lift, balance, curl and shoulder press)
  • 3 sets of cross-cycle
  • 3 sets of shoulder press (sitting on a ball)
  • 3 sets of triceps extension
  • 3 sets of tricep press.

• Most likely it was procrastinator working silently because of which I was not able to enjoy today’s workout. But it might be also possible that I need to allow my muscle relax. Hence, tomorrow I am planning to do only cardio and stretching exercise. Actually there is a free yoga class in the gym. I should try that.

Day 4 – Wednesday May 20th

• 11:56 AM – you are so close to completing your thesis and you are running out of time. Skip today’s gym and focus on your thesis. Right now, your thesis is the most important thing. Work on it. You have already worked all major muscle group, take a break for a day and work on your thesis. Don’t listen to him. He just want to procrastinate. Going to gym will make you active and will increase your productivity.
• 9:40 PM: I must confess that today it was one of the most intense battle that I have ever seen between procrastinator and achiever. The procrastinator even went to the extent of suggesting giving a point to the Achiever without going to the gym. But don’t worry, The achiever is not here to win only this challenge but the whole life long challenge. Finally, the achiever convienced me to go to gym
  
• 10 PM: went gm and did
  
  • Running – 25 min / 2.25 miles
  • Elliptical – 15 min / 1.25 miles
  • Sit-up 2 sets of 12 rep.
    
• 
  

Day 3 – Tuesday  May 19th

• 5:50 AM: Man, you have just gained 3.5 lbs in just one day because of exercise. You should stop going to gym.  You lost 3 lbs last week by not going to the gym.  See exercise is not meant for you. Weight gain can be due to many reasons. May be because of water retention and you just read that 3-5 lbs fluctuation is normal. Just keep on doing. Let complete this challenge and see. But by then, you might gain lot of weight. Exercise cannot cause weight gain as long as you are controlling food, which you are doing I know. Just keep on doing. Do it three more days and on friday take a yoga class. That will stretch your muscles and will allow lactic acid to go. Keep on doing and we will analyze the results after 28 days.
  
• 3:15 PM – went gym.
  
  • 15 min running ( 3 min walk + 6 min @ 6 mph/1 inclination + 6 min @ 7 mph / 0 inclination)
  • Inclined chest press – 3 sets of 35 lbs, 12 repetitions – next time reduce to 30 lbs
  • Push-up – 3 sets, 12 repetitions
  • Chest Extension – 20 lbs, 12 repetitions, 3 sets
    
  • Chest Press using ball – 20 lbs, 3 sets
  • Rowing – 1000 m in 4 min 30 seconds
  • leg raises
    

Day 2 – Monday May 18th

• 10:30 AM – lets go to gym after weekly meeting (which is at 1:30 PM)
• 3:01 PM - lets go to the gym around 4:30 and use this time to work on some new ideas
• 5:22 PM – went gym
  • 10 min elliptical
  • leg press (4 sets of lbs, 12 repetition)
  • calf press (4 sets of 55 lbs, 12 repetitions)
  • hack machine (4 sets of 45 lbs, 10 repetitions)
  • cross cycle (for core – 3 sets of 25)
  • 10 min cycle
  • Missed – Lunches with rotation & Squats
    

Day 1 – Sunday May 17th
Mind at work:

• 7:02 AM -  Will go Gym in the afternoon. Morning time is good for studies and I can effectively use it for writing my thesis.
• 7:04 AM – man 30 days is too long. Probably I should start with one week or two week. My two cents go for two weeks. Also, doctors don’t suggest working out for 30 days. Take one day at a time and forget about 30 days
  
• 10:41 AM - oh man, today is so hot. Just saw the weather and according to the report today afternoon temperate will be around 90 F. Do I really want to go gym in this hot weather and that to in the afternoon. Let’s go in the evening. Talking as if you will be working outside. The gym has controlled environment and you will be never exposed to the sun. Go and get some workout. You will feel good after workout. Don’t your remember that exercise regulates stress hormones and promotes the release of endorphins that make you feel good
• 7:30 PM – So what happened about the challenge. I got some new ideas about my thesis. Let me just put them in writing otherwise I will forget it. Will go to gym around 9 PM ( ) . Achiever mind  ( )
• 9:oo PM  – Common body, get up and move. Anyway you are not able to write any new sentence (for thesis) and just moving statements from here to there. Go to gym. Oh!!. its already 9 PM. If you will go now, you will get late for bed and tomorrow you will be late for office. Lets forget about this challenge and we will start it again sometime later.
  
• 9:15 PM: Ahh, finally I got a chance to win.
  
  • 5 min walk (3.5 mph) + 7 min running (6 mph / 1 inclination) + 3 min walk (3.5 mph / 0.5 inclination)
  • Lower T-Row (4 sets of 90 lbs and 10 repetition)
    
  • Back Extension (4 sets of 10 repetition)
    
  • Upper Back T-Row (3 sets of 80 lbs, 12 repetition + 1 set of 95 lbs, 10 repetition)
  • Lift & Swing (Squat Raises ) – (3 sets with 30 lbs and 10 repetition)
  • Sit-ups (3 sets at 2 inclination, 10 repetitions)
  • 5 minutes elliptical
    
  • Missed Chin-ups, 10 minutes of elliptical, 1 set of situp and 1 set of lift & swing