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
Ritesh Agrawal
