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). An excellent resource to find SRID number is

/> select st_area(st_transform(the_geom, 3035 ) 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);

Tip 4: Fixing “Operations on mixed Geometries” Error
Checkout my previous post

Tip 5: Fixing “Ring Self-Intersection” Error
Use ST_Simplify or ST_SimplifyPreserveTopology functions to make sure all the geometries are valid geometries and there are not self-intersections. I would recommend first trying ST_SimplifyPreserveTopology operation and then ST_Simplify operation as shown below.

> update <table> set the_geom = ST_SimplifyPreserveToplogy(the_geom, 1) where ST_IsValid = false
> update <table> set the_geom = ST_Simplify(the_geom, 1) where ST_IsValid = false

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s