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


About Ritesh Agrawal

I am a applied researcher who enjoys anything related to statistics, large data analysis, data mining, machine learning and data visualization.
This entry was posted in Database, postgis, Postgres, Tips. Bookmark the permalink.

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