Tired by PGAdmin’s slowness and crashes? Try SQLTools for Sublime Text 3

PGAdmin did it again. It crashed without reason and warning. Fifteen minutes worth of SQL coding lost. Boom.

I often also experience PGADMIN being very slow when working on larger remote databases.

So I tried the SQLTools package for my favourite text editor, Sublime Text 3. And bam! My queries work directly from my text editor, and at a reasonable speed, given the remote database it is connected to.

Backup PostGreSQL to S3: Simple and cost efficient approach via s3cmd

I was looking into backing up some medium sized postgres databases that are running on AWS.

There are loads of solutions out there , most of which rely on writing the backup to either a replication server or dumping it to a directory on the same server.

All of those solutions have increased cost for EBS storage in common. S3 storage is much cheaper.

So why not directly stream the backup to S3 ?

The only simple approach besides WAL-E that I could find was this interesting adaption of the old backup script on the PostGres WIKI.

It is a work in progress, but I think it can be brought to working stat in 1-2 hours.

Export from PostGIS to GeoJSON

In order to extract tables or results from queries to a geojson file quickly, the well know ogr2ogr tool is my standard tool.

It ha but one caveat: If there are several geometry columns in the input data, it will only extract the first geometry column to a geojson geometry.



Find overlaps between geometries in the same geometry column


Finding clusters of points

In a cloud of points, I wanted to identify clusters of points that are located within a certain range of each other.

After scouring the net for a neat, simple solution and not really finding one, I came up with this python function, using shapely :

If you have your geometries in an unprojected coordinate reference system and want to set the clustering range to a unit other than degree, you should project your geometries into a CRS with the desired unit (for example LAEA, aka EPSG 3034).

To do so, I have another function:

Import CSV data into postgresql, the comfortable way ;-)

Do you also loathe the task of importing CSV data into postgres? Create the table structure, then import data via COPY, end up with a headache because it keeps on complaining about permissions and whatnot…

This can be avoided, by using the wonderful CSVKIT

The following instructions assume that:

  • CSVKIT is installed. If that’s not the case, install it by typing
  • We are importing to a postgresql database on localhost, port 5432

If these preconditions are satisfied, follow those steps (1 and 2 are optional and only needed if you are not sure about the encoding of your csv file or if the file has corrupted data):

1. Find out encoding of file:

2. Clean CSV file:

This produces an _err.csv file with the error reports and a comma-separated _out.csv file with the cleaned data

3. Main process: Create a postgres table with the  CSV column structure and insert data, all in one step! Yay!

This will produce a table in the database with the name of the csv file and its column names. It will also insert the data from the csv file into the table. All done!

Hint: You can also bulk-import a whole folder of csv files, see below:

Change positions of characters in a string via shell

Let’s say you want to convert a date from DDMMYYYY to YYYYMMDD format, i.e. 30112015 to 20151130.

A solution that works also when processing bulk data is to use sed (stream editor) that is preinstalled in most linux distributions

Export all tables from postgis to shapefiles in one step

The following will export all tables from several schemas from postgis to individual shapefiles (shapefiles get name of source table) in the directory /home/me/my_shp