## 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 http://spatialreference.org/

 /> 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