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 [...]

PostGIS Error: Operation on mixed geometries

PostGIS usually raises “operations on mixed geometries” while trying to use topological functions (such as st_intersects, st_within, etc). There can be many different reasons because of which one might encounter this error. Below are the steps that I found useful in resolving this error.
1. Validate Geometries: Make sure all the geometries are valid. psql> [...]

Dummy’s guide for converting character set of a web application

When developing a web application, one has to be careful about the “character encoding”. There are many options such as latin1, utf8, etc, and the best character encoding depends on the language that your targeted audience use for writing. Since most  web applications are required to handle all types of languages and, thus, are required to handle all different types [...]

“Size does matter” – using cursor to sequentially process large database

Recently, I started working on a new project which uses postgres database and contains more than 3 million addresses. One of my first task was to geocode address. To do so, I wanted to sequentially access all the records ordered by address. As I never dealt with such a large database before, my first attempt was a [...]