In Loki We Trust The many projects of Lokkju, Inc


Adding functionality to HTSQL v2

HTSQL is a very cool open source product that gives you a RESTful interface to multiple database backends. Because it uses it's own simple, but very powerful, syntax, you avoid most of the risks involved in passing in SQL. Currently it supports SQLite and PostgreSQL, but for my current project, I need to support Geometry columns.

For the first draft, I just wanted to add support for Spatialite, a spatially enabled version of SQLite. Since SQLite is already supported, this turned out to be relatively easy - though in hindsight, I may not even have implemented it in the easiest way possible - but I'll get to that in another post.

So, each database backend is in a namespace called htsql_[name], and they all subclass files in the main htsql namespace. I started off by cloning the htsql_sqlite tree into a new namespace called htsql_spatialite, and ripped out most of the code, leaving me with a basic structure. I then subclassed any SQLite classes I wanted to override - most importantly:

  1. Changed to import pyspatialite instead of pysqlite2.
  2. Added my own Column and Data types (Domains) in
  3. Modified to handle my custom Domains, as well as to handle the blank Column type sometimes given for Geometry columns

I also, and here is where most of my functionality was added, overrode a few classes in tr/

  1. Class SpatialiteSerializeLeafReference(subclassing SerializeLeafReference) tests if I am selecting a Geometry column, and if I am, wraps it in the "AsText" function, to return WKT.
  2. a new Adaptor, FormatGeometry, which handled the representation of the WKT when returned to the client. Right now, only HTML is supported, but JSON, CSV, and the rest are easy to add in the same way.

The last thing you have to do is add a line in to point to your new database engine's entry point - it is in a list called ENTRY_POINTS.

Interestingly, I think I could better utilize the plugin architecture - but as I'm just discovering HTSQL, and there isn't all that many samples, nor much documentation, I'm pretty happy with what I accomplished.

You can see the full source of my additions at


Setting up PostGIS 1.5 on PostgreSQL 8.4.1 (on Debian)

I found that getting the template database for postgis set up was somewhat poorly documented - so:

First, create a role that will own the tables within the template database:
psql -c "CREATE ROLE gisgroup;"

Second, create and populate the template database:

createdb -E UNICODE template_postgis
createlang -d template_postgis plpgsql
psql -d template_postgis < /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d template_postgis < /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d template_postgis < /usr/share/postgresql/8.4/contrib/postgis_comments.sql

Third, set the ownership to the role you created:

psql -c "ALTER TABLE geometry_columns OWNER TO gisgroup;" template_postgis
psql -c "ALTER TABLE spatial_ref_sys OWNER TO gisgroup;" template_postgis

Fourth, we create the user for our database:

psql -c "CREATE USER yourgisuser WITH PASSWORD 'yourpassword';"
psql -c "GRANT gisgroup TO yourgisuser;"

Fifth, and last, we create a new postgis enable database:
createdb -T template_postgis -O yourgisuser your_new_postgis_database