create database world_borders

shp2pgsql world_borders.shp world_borders.sql
createdb world_borders
createlang plpgsql world_borders
psql -d world_borders -f /usr/local/pgsql/share/lwpostgis.sql
psql -d world_borders -f /usr/local/pgsql/share/spatial_ref_sys.sql
psql -d world_borders -f path-to/world_borders.sql

Setting-up postgresql with postgis on a grhat box

Time and again, I have to setup PostgreSQL database with PostGIS extension, and it's aways been a painful experience. So I have decided to write down detailed instructions for future reference.

Install PostgreSQL with PostGIS with PROJ4 projection library, GEOS geometry engine and python interface on a grhat box

As this requires custom compilation of PostgreSQL, I decided to install everything from source rather than available rpms from our apt repository.

Downloaded:

postgresql-8.1.4.tar.gz ( http://www.PostgreSQL.org )
postgis-1.1.3.tar.gz ( http://postgis.refractions.net )
geos-2.2.3.tar.bz2 ( http://geos.refractions.net )
proj-4.4.9.zip ( http://www.remotesensing.org/proj )

1. PostgreSQL install

tar xvfz postgresql-8.1.4.tar.gz
cd postgresql-8.1.4

PostGIS manual warned that for GEOS support I might need to explicitly link PostgreSQL against the standard c++ library and use the following configure syntax

LDFLAGS=-lstdc++ ./configure

gmake
sudo gmake install

2. Once PostgreSQL is successfully installed, need to add the postgres user and postgres group

Need to specify a home directory for the postgres user. I prefer using the the data directory as home directory.

Add to /etc/passwd
postgres:x:26:26::/usr/local/pgsql/data:/bin/bash

Add to /etc/group
postgres:x:26:

3. Create postgres data directory and initialize database

sudo mkdir /usr/local/pgsql/data
sudo chown postgres:postgres /usr/local/pgsql/data
sudo chmod 700 /usr/local/pgsql/data


Now initialize the database

First login as a postgres user
sudo su - postgres
( supply your LDAP password )

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

4. Configure PostgreSQL

Install postgres service
sudo cp contrib/start-scripts/linux /etc/rc.d/init.d/postgres
sudo chmod 755 /etc/rc.d/init.d/postgres
sudo chkconfig --add postgres

you can start the PostgreSQL server by typing
sudo /etc/init.d/postgres start

PostgreSQL server should also start automatically on reboot from now on.

It will be useful to add the path /usr/local/pgsql/bin to your PATH variable. I put this in .bashrc file in my postgres home directory ( /usr/local/pgsql/data )
PATH=$PATH:/usr/local/pgsql/bin

Now install GEOS and PROJ libraries and install PostGIS

5. PROJ installation

unzip proj-4.4.9.zip
cd proj-4.4.9
./configure
gmake
sudo gmake install

6. GEOS installation
bunzip geos-2.2.3.tar.bz2
tar xvf geos-2.2.3.tar
cd geos-2.2.3
./configure
gmake
sudo gmake install

I had problems with PostGIS not able to locate the GEOS library correctly. So I had to manually edit ld.so.conf file put the path in there
sudo vi /etc/ld.so.conf and add /usr/local/lib
sudo ldconfig

7. Install PostGIS
tar xvfz postgis-1.1.3.tar.gz
cd postgis-1.1.3

./configure

PostGIS configure should produce a nice summary, make sure that GEOS and PROJ paths are listed there.

gmake
sudo gmake install

8. Python interface
I use python to access PostgreSQL database. I found that it's easiest to install the rpm from the apt repository.
sudo apt-get install postgresql-python


9. This is all that is required for installation. Following steps are for transferring my existing databases, enabling PostGIS support for these databases and loading PostGIS function and object definitions.

Supposing a database called "dgvbr" exists on another server. To copy this database to this new server

On the existing server:

sudo su - postgres
pg_dump dgvbr > /tmp/dgvbr.out

transfer this database to another server using scp
scp /tmp/dgvbr.out username@server:/tmp/

On the new server:

sudo su - postgres
createdb dgvbr

Load PL/pgSQL language extension
createlang plpgsql dgvbr

Load PostGIS object and function definitions in the database
psql -d dgvbr -f lwpostgis.sql (this file lwpostgis.sql can be found in the source directory i.e. postgis-1.1.3 )

Restore the database
psql -d dgvbr -f dgvbr.out

and voila! all set to for some serious PostgreSQL fun.

研究了MPEG的bitrate问题 (如何使用Adobe Premiere Pro 导出质量高，文件小的视频）

使用Adobe Premiere Pro 导出我的SONY Video Camera:
Export to AVI:
1. File -> Export -> Movie
2. Click settings:
====
The General Section:
*File Type: Microsoft AVI
====
The Video Section:
* Compressor: ffdshow Video Codec
-- Click Configure and Choose Encoder: XviD. Bitrate: 2200, Mode: one pass - constant bitrate (Don't try to use two pass, will create a very large output)
-- Click output on the left hand side, Pixel aspect ratio: 1:1; Display aspect ratio: 16:9
* Change Frame Size: 852 x 480; Pixel Aspect Ratio: Square Pixels (1.0)
* Uncheck Recompress
====
The Keyframe and Rendering Section:
* Check Deinterlace Video Footage
====The Audio Section:
*Compressor: Microsoft ADPCM
*Sample Rate: 22050Hz



Export to MPG (not recommended because the output will be large)
1. File -> Export -> Adobe Media Encoder
Check Deinterlace.
Format: MPEG2
PreSet: NTSC-MPEG 2 Generic
Choose pixel ratio: 16:9
Field Order: Progressive
Use default min max and avg bitrates if to burn a DVD
or
min: 1.44; max 2.40; avg: 2.376 if you want smaller size if create for PC


Notice the Estimated File Size at the bottom (250 KB/s ~ 300 KB/s are normal values; Don't make it more than 300). Export to a .mpg file.
Note: 1KB/s = 8Kbit/s