postgres tips: enable plpgsql for implementing stored procedure

This entry is just a memo for me 8^)
If we want to implement stored procedure to any database, it is necessary to type below command.

createlang -d dbname plpgsql

postgres tips: calculate degree with stored procedure

-- User-Defined-Function for calculate degree
-- ------------------------------------------

-- This function calculates cos from vector(0,1)

-- .. code-block:: sql
--    :linenos:

CREATE OR REPLACE FUNCTION GET_COS
    (v2_x FLOAT, v2_y FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    v1_x FLOAT;
    v1_y FLOAT;
    v1_v2 FLOAT;
    length_v1 FLOAT;
    length_v2 FLOAT;
    cos FLOAT;
BEGIN
    v1_x := 0.;
    v1_y := 1.;
    v1_v2 := (v1_x * v2_x) + (v1_y * v2_y);
    length_v1 := sqrt(power(v1_x,2) + power(v1_y,2));
    length_v2 := sqrt(power(v2_x,2) + power(v2_y,2));
    cos := v1_v2 / (length_v1 * length_v2);
    RETURN cos;
END;
$$ language plpgsql;

-- This function calculates sin from vector(0,1)

-- .. code-block:: sql
--    :linenos:

CREATE OR REPLACE FUNCTION GET_SIN
    (v2_x FLOAT, v2_y FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    v1_x FLOAT;
    v1_y FLOAT;
    det FLOAT;
    length_v1 FLOAT;
    length_v2 FLOAT;
    sin FLOAT;
BEGIN
    v1_x := 0.;
    v1_y := 1.;
    det := (v1_x * v2_y) - (v1_y * v2_x);
    length_v1 := sqrt(power(v1_x,2) + power(v1_y,2));
    length_v2 := sqrt(power(v2_x,2) + power(v2_y,2));
    sin := det / (length_v1 * length_v2);
    RETURN sin;
END;
$$ language plpgsql;

-- This function calculates degree from vector(0,1)

-- .. code-block:: sql
--    :linenos:

CREATE OR REPLACE FUNCTION GET_DEGREE
    (x FLOAT, y FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    sin FLOAT;
    cos FLOAT;
    degree FLOAT;
BEGIN
    sin := GET_SIN(x, y);
    cos := GET_COS(x, y);
    IF sin >= 0 AND cos >= 0 THEN
        degree := degrees(acos(cos));
    ELSEIF sin >= 0 AND cos < 0 THEN
        degree := degrees(acos(cos));
    ELSEIF sin < 0 AND cos < 0 THEN
        degree := 180. - degrees(asin(sin));
    ELSEIF sin < 0 AND cos >= 0 THEN
        degree := 360. + degrees(asin(sin));
    END IF;
    RETURN degree;
END;
$$ language plpgsql;

sphinx tips: how to easily write a document for several scripts?

Today, I explain how to write a sphinx document for several scripts in an easy way. Assume that you are now writing postgres scripts and need to write document for them. Now, you have two postgres scripts named sample{1,2}.sql and these scripts are written like below.

-- Sample1.sql
-- -----------

-- This SQL does something!!
-- A definition of sample_table shown below.

-- =============== =========== ============
-- Column Name     Type        Description
-- =============== =========== ============
-- foo             TEXT        foo?
-- bar             TEXT        bar?
-- baz             FLOAT       baz?
-- =============== =========== ============

-- :NOTE: just return 10 records
-- :TODO: just return 11 records

-- .. code-block:: sql
--    :linenos:

SELECT
    *
FROM
    sample_table
LIMIT 10;

-- Sample2.sql
-- -----------

-- This SQL extract data from columns named foo, bar...

-- :NOTE: just return 10 records
-- :TODO: just return 11 records

-- .. code-block:: sql
--    :linenos:

SELECT
    foo,
    bar
FROM
    sample_table
LIMIT 11;

As you can see above, there are rst formatted comments in sql scripts. If you write this kind of comments in sql scripts, you can easily convert them to html/latex-styled pdf! Below shell scripts can convert above sql scripts into one rst document.

#!/bin/sh
# convert_sql2rst.sh

output_rst="sample.rst"

if [ -e ${output_rst} ]; then
    rm ${output_rst}
fi

echo "=======================" >> ${output_rst}
echo "This is sample chapter!" >> ${output_rst}
echo "=======================" >> ${output_rst}
echo "" >> ${output_rst}

for sql_file in `ls *.sql`
do
    echo "converting ${sql_file} to rst-formatted document..."
    sed ${sql_file} -e 's/^/    /g' | \
    sed -e 's/^    -- //g' >> ${output_rst}
    echo "" >> ${output_rst}
    echo "" >> ${output_rst}
done

Now, put all of above scripts into same sphinx-root directory and edit your index.rst for including ${output_rst}. Next, do this:

yaboo@maniac:~/Projects/SqlProject$ sh convert_sql2rst.sh 
converting sample.sql to rst-formatted document...
converting sample2.sql to rst-formatted document...
yaboo@maniac:~/Projects/SqlProject$ make html
sphinx-build -b html -d _build/doctrees   . _build/html
Making output directory...
Running Sphinx v1.0.7
loading pickled environment... not yet created
building [html]: targets for 2 source files that are out of date
updating environment: 2 added, 0 changed, 0 removed
reading sources... [100%] sample                                                
looking for now-outdated files... none found
pickling environment... done
checking consistency... done
preparing documents... done
writing output... [100%] sample                                                 
writing additional files... genindex search
copying static files... done
dumping search index... done
dumping object inventory... done
build succeeded.

Build finished. The HTML pages are in _build/html.

Finally, you can get document like this.

postgres tips: stored procedure for calculating distance on the earth

CREATE FUNCTION GET_DISTANCE
    (alat FLOAT, alon FLOAT, lat FLOAT, lon FLOAT)
RETURNS FLOAT AS
$$
DECLARE
    radius_earth FLOAT;
    radian_lat FLOAT;
    radian_lon FLOAT;
    distance_v FLOAT;
    distance_h FLOAT;
    distance FLOAT;
BEGIN
    -- Insert earth radius
    SELECT INTO radius_earth 6378.137;

    -- Calculate difference between lat and alat
    SELECT INTO radian_lat radians(lat - alat);

    -- Calculate difference between lon and alon
    SELECT INTO radian_lon radians(lon - alon);

    -- Calculate vertical distance
    SELECT INTO distance_v (radius_earth * radian_lat);

    -- Calculate horizontal distance
    SELECT INTO distance_h (cos(radians(alat)) * radius_earth * radian_lon);

    -- Calculate distance(km) and convert it to distance(meter)
    SELECT INTO distance sqrt(pow(distance_h,2) + pow(distance_v,2)) * 1000;

    -- Returns distance
    RETURN DISTANCE;
END;
$$ language plpgsql;

> createlang plpgsql mytestdb
> psql -d mytestdb
mytestdb=# CREATE FUNCTION GET_DISTANCE
mytestdb-#     (alat FLOAT, alon FLOAT, lat FLOAT, lon FLOAT)
mytestdb-# RETURNS FLOAT AS
mytestdb-# $$
mytestdb$# DECLARE
mytestdb$#     radius_earth FLOAT;
mytestdb$#     radian_lat FLOAT;
mytestdb$#     radian_lon FLOAT;
mytestdb$#     distance_v FLOAT;
mytestdb$#     distance_h FLOAT;
mytestdb$#     distance FLOAT;
mytestdb$# BEGIN
mytestdb$#     -- Insert earth radius
mytestdb$#     SELECT INTO radius_earth 6378.137;
mytestdb$#
mytestdb$#     -- Calculate difference between lat and alat
mytestdb$#     SELECT INTO radian_lat radians(lat - alat);
mytestdb$#
mytestdb$#     -- Calculate difference between lon and alon
mytestdb$#     SELECT INTO radian_lon radians(lon - alon);
mytestdb$#
mytestdb$#     -- Calculate vertical distance
mytestdb$#     SELECT INTO distance_v (radius_earth * radian_lat);
mytestdb$#
mytestdb$#     -- Calculate horizontal distance
mytestdb$#     SELECT INTO distance_h (cos(radians(alat)) * radius_earth * radian_lon);
mytestdb$#
mytestdb$#     -- Calculate distance(km) and convert it to distance(meter)
mytestdb$#     SELECT INTO distance sqrt(pow(distance_h,2) + pow(distance_v,2)) * 1000;
mytestdb$#
mytestdb$#     -- Returns distance
mytestdb$#     RETURN DISTANCE;
mytestdb$# END;
mytestdb$# $$ language plpgsql;
CREATE FUNCTION
mytestdb=# select GET_DISTANCE(34.701909, 135.4949770, 35.681382, 139.766084);
   get_distance
------------------
 405807.810663345
(1 行)

python tips: create line graph with matplotlib

*** sample code (mypg.py) ***

#/usr/bin/env python                                                       
# coding:utf-8                                                             

# need to install python-pygresql                                          
import pg
import sys
from pylab import *

def main():
argvs = sys.argv
argc = len(argvs)

# check argument                                                       
if (argc != 4):
   print "Usage: python %s dbname dbuser dbpass" % (argvs[0])
   sys.exit(1)

dbname=argvs[1]
dbuser=argvs[2]
dbpass=argvs[3]
dbobj = getConnection(dbname, dbuser, dbpass)

# input data from database tables                                      
Lx = getValueX()
L1 = collectData(dbobj, '1')
L2 = collectData(dbobj, '2')
L3 = collectData(dbobj, '3')
L4 = collectData(dbobj, '4')

# plot data using matplotlib                                           
plot(Lx, L1, label='id=1')
plot(Lx, L2, label='id=2')
plot(Lx, L3, label='id=3')
plot(Lx, L4, label='id=4')

# set labels and title                                                 
xlabel('hour')
ylabel('random_value')
title('plot test')

# draw legend                                                          
legend()

# draw graph                                                           
show()

dbobj.close()

def getValueX():
    L = []
    hour = 0
    while (hour <= 23):
        L.append(hour)
        hour += 1
    return L

# collecting data if same id                                                    
def collectData(dbobj, target):
    L = []
    sn = 0
    while sn <= 23:
        dbtable='public.table_' + str(sn)

        sql="SELECT * FROM %s WHERE id = '%s';" % (dbtable, target)
        for row in dbobj.query(sql).dictresult():
            if row['id'] == target:
                L.append(int(row['value']))
        sn += 1

    return L
# debug function                                                                
def showTables(dbobj):
    for tbl in dbobj.get_tables():
        print tbl

# initialize db connection                                                      
def getConnection(dbname, dbuser, dbpass):
    pg.set_defhost('localhost')
    pg.set_defport(5432)
    pg.set_defbase(dbname)
    return pg.DB(user=dbuser, passwd=dbpass)

if __name__ == "__main__":
    main()

*** output from above code ***

yaboo@maniac:~/$ python mypg.py mydb test test

*** matplotlib reference pages ***
ぐうたらの部屋
Matplotlib サンプル集 - Kaiseki
http://www.ike-dyn.ritsumei.ac.jp/~uchida/scipy-lecture-notes/intro/index.html

shell script tips: bash and function

*** motivation ***
sh script does not have ${RANDOM} value which generates random value if used in bash script. Below is sample code explains how to use ${RANDOM} and define functions in bash scripts.

*** sample code (makeData.sh) ***

#!/bin/bash                                                                   

function create_db(){
  dbname=$1
  dbuser=$2
  dbpass=$3
  PGPASSWORD=${dbpass} dropdb -U ${dbuser} ${dbname}
  PGPASSWORD=${dbpass} createdb -U ${dbuser} ${dbname}
}

function create_tbl(){
  id=0
  dbname=$1
  dbuser=$2
  dbpass=$3
  dbtable=$4

  sql="CREATE TABLE ${dbtable} (id text, value int);"
  PGPASSWORD=${dbpass} psql -U ${dbuser} -d ${dbname} -c "${sql}"

  while [ ${id} -le 3 ]
  do
      id=`expr ${id} + 1`
      sql="INSERT INTO ${dbtable} VALUES (${id}, ${RANDOM});"
      PGPASSWORD=${dbpass} psql -U ${dbuser} -d ${dbname} -c "${sql}"
  done
}

function select_tbl(){
  dbname=$1
  dbuser=$2
  dbpass=$3
  dbtable=$4

  sql="SELECT * FROM ${dbtable};"
  PGPASSWORD=${dbpass} psql -U ${dbuser} -d ${dbname} -q -c "${sql}"
}

function main(){
  dbname=$1
  dbuser=$2
  dbpass=$3
  create_db ${dbname} ${dbuser} ${dbpass}

  sn=0
  prefix="table_"

  while [ ${sn} -le 23 ]
  do
      dbtable=${prefix}${sn}
      create_tbl ${dbname} ${dbuser} ${dbpass} ${dbtable}
      select_tbl ${dbname} ${dbuser} ${dbpass} ${dbtable}
      sn=`expr ${sn} + 1`
  done
}


# Program starts here!!!
if [ $# -ne 3 ]; then
  echo "usage: bash $0 dbname dbuser dbpass"
  exit
fi

main $1 $2 $3

*** output from above script ***

yaboo@maniac:~$ bash makeData.sh mydb test test 
id | value                                                                   
----+-------                                                                  
1  |   632                                                                   
2  |  5999                                                                   
3  |  3495                                                                   
4  |  3127                                                                   
(4 行)                                                                        
                                                                            
id | value                                                                   
----+-------                                                                  
1  | 29475                                                                   
2  |  7581                                                                   
3  | 17044                                                                   
4  | 12726                                                                   
(4 行)                                                                        
                                                                            
id | value                                                                   
----+-------                                                                  
1  | 13619                                                                   
2  | 25982                                                                   
3  | 21835                                                                   
4  | 23054                                                                   
(4 行)                                                                        
                                                                            
id | value                                                                   
----+-------                                                                  
1  |   726                                                                   
2  | 15405                                                                   
3  | 16549                                                                   
4  | 16405                                                                   
(4 行)                                                                        
                                                                            
id | value                                                                   
----+-------                                                                  
1  | 14362                                                                   
2  | 22030                                                                   
3  | 16364                                                                   
4  | 24379                                                                   
(4 行)                                                                        

...

id | value                                                                   
----+-------                                                                  
1  |  8468                                                                   
2  | 10413                                                                   
3  | 12345                                                                   
4  | 30637                                                                   
(4 行)                                                                        
                                                                            
id | value                                                                   
----+-------                                                                  
1  |  8372                                                                   
2  | 16466                                                                   
3  | 31683                                                                   
4  | 23073                                                                   
(4 行)                                                                        
                                                                            
yaboo@maniac:~$

postgres tips: enable md5 authentication

step1. Add user

postgres@maniac:~$ createuser test
新しいロールをスーパーユーザとしますか? (y/n) n
新しいロールにデータベース作成権限を与えますか? (y/n) y
新しいロールにロールを作成する権限を与えますか? (y/n) y

step2. Set password for user

postgres@maniac:~$ psql
psql (8.4.8)
"help" でヘルプを表示します.

postgres=# alter user postgres with encrypted password 'test';
ALTER ROLE

step3. Edit pg_hba.conf (hba: host based authentication)
[default configuration]

# Database administrative login by UNIX sockets
local   all         postgres                          ident

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

[configuration enables md5 authentication for user 'postgres']

# Database administrative login by UNIX sockets
local   all         postgres                          ident
local   all         test                              md5

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

step4. Restart postgresql

sudo /etc/init.d/postgresql-8.4 restart
* Restarting PostgreSQL 8.4 database server    [ OK ]
yaboo@maniac:~$ createdb -U test sampledb
パスワード: 
yaboo@maniac:~$ psql -U test sampledb
ユーザ test のパスワード: 
psql (8.4.8)
"help" でヘルプを表示します.

sampledb=> 

postgres tips: copy and \copy

Below explanation is quoted from http://wiki.postgresql.org/wiki/COPY

COPY is the Postgres method of data-loading. Postgres's COPY comes in two separate variants, COPY and \COPY: COPY is server based, \COPY is client based.

COPY will be run by the PostgreSQL backend (user "postgres"). The backend user requires permissions to read & write to the data file in order to copy from/to it.You need to use an absolute pathname with COPY.

\COPY on the other hand, runs under the current $USER, and with that users environment. And \COPY can handle relative pathnames. The psql \COPY is accordingly much easier to use if it handles what you need.

postgres tips: How to Execute PostgreSQL Commands Inside Unix Shell Scripts

I've used to use -c option (method1) when execute postgreSQL command inside unix shell scripts. However, method1 supports single postgreSQL command and requires backslash '\' when postgreSQL is written by multiple lines. On the contrary, method2 supports multiple command at a time and does not require any backslash 8^). I think method2 is better than what I used to do(method1)

Method1: Using -c option

#!/bin/sh

dbname="mydb"
dbuser="postgres"
table1="test1"
table2="test2"

psql -U ${dbuser} -d ${dbname} -c "
DROP TABLE ${table1};"
psql -U ${dbuser} -d ${dbname} -c "
DROP TABLE ${table2};"
psql -U ${dbuser} -d ${dbname} -c "
CREATE TABLE ${table1} \
  (id int, \
  name text);"

psql -U ${dbuser} -d ${dbname} -c "
CREATE TABLE ${table2} \
  (id int, \
  name text);"

Method2: Using EOF operator

#!/bin/sh

dbname="mydb"
dbuser="postgres"
table1="test1"
table2="test2"

psql -d ${dbname} -U ${dbuser} << EOF
    DROP TABLE ${table1};
    DROP TABLE ${table2};
    CREATE TABLE ${table1}
      (id int,
      name text);
    CREATE TABLE ${table2}
      (id int,
      name text);
EOF

postgres tips: postgis operations

What is PostGIS?
PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension

PostGIS Initialization

createdb ${dbname}
createlang plpgsql ${dbname}
psql -d ${dbname} -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d ${dbname} -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d ${dbname} -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

PostGIS Tips

1. Create geometry data from x, y

-- 4326 means WGS84
CREATE TABLE sample_data AS
  SELECT
    ST_GeomFromText('POINT('||x||' '||y||')', 4326) AS the_geom
  FROM
    ${table}

2. Create spatial index to geometry data

-- GiST: Generalized Search Trees Indexes
CREATE INDEX
  geom_index
ON
  sample_data
USING GIST (the_geom);"

3. Create intersection and its area

-- intersection(geom_1, geom_2) returns intersection
CREATE TABLE intersections AS
  SELECT
    Area(intersection(a.the_geom, b.the_geom)) AS area,
    intersection(a.the_geom, b.the_geom) AS the_geom
  FROM
    geom_data_1 as a
  INNER JOIN
    geom_data_2 as b
  ON
    ST_Intersects(a.the_geom, b.the_geom);

postgresDB operation with python

below code describes how to check if 'table' is exist in database or not using pg package. dirty code ... :-(

import pg
import sys

def is_table_exist1(dbobj, table):
    """ return True if table is exist in database """
    print "is_table_exist1 called"
    try:
        for i in dbobj.get_tables():
            if i == table:
                return True
    except IndexError, (errno):
        print "%s" %(errno)
        return False
    return False

def is_table_exist2(dbobj, table):
    """ return True if table is exist in database """
    print "is_table_exist2 called"
    try:
        print dbobj.get_attnames(table)
        return True
    except pg.ProgrammingError, (errno):
        print "%s" %(errno)
        return False

def main():
    # set default hostname                                                      
    pg.set_defhost('localhost')
    defhost = pg.get_defhost()

    # set default port number                                                   
    pg.set_defport(5432)
    defport = pg.get_defport()

    # set default database                                                      
    pg.set_defbase('testdb')
    defbase = pg.get_defbase()

    # initialize pg.DB object                                                   
    mydb = pg.DB(user='postgres', passwd='postgres')

    # print names of all databases                                              
    dblist = mydb.get_databases()

    # check if table is exist in database                                       
    if is_table_exist1(mydb, 'public.foo') == False:
        mydb.close()
        sys.exit(1)

    # check if table is exist in database                                       
    if is_table_exist2(mydb, 'public.foo') == False:
        mydb.close()
        sys.exit(1)

    for r in mydb.query(
        "SELECT v1, v2, v3, v4 FROM %s" % ('public.foo')
        ).dictresult():
        print '%(v1)s %(v2)s %(v3)s %(v4)s' % r

    #close dbobj                                                                
    mydb.close()

if __name__ == "__main__":
    main()

postgres tips: import/export CSV file

skip header and import csvfile to table_name

COPY table_name FROM '/abspath/to/csvfile' WITH CSV HEADER

export query result to csvfile with header

COPY (SELECT foo,bar FROM whatever) TO /abspath/to/csvfile' WITH CSV HEADER