In Loki We Trust

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 connect.py to import pyspatialite instead of pysqlite2.
2. Added my own Column and Data types (Domains) in domain.py
3. Modified introspect.py 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/serializer.py:

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 setup.py 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 https://bitbucket.org/lokkju/htsql-appengine/src/3d7b7d8e8580/

A quick one-liner using ogr2ogr to convert from spatialite to postgis:
ogr2ogr -f PostgreSQL PG:"host=host_ip user=username password=password dbname=database" -lco LAUNDER="YES" sqlite.sqlitefile -skipfailures

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

Though there are a lot of articles out there on office automation in dotnet (most of them telling you not to do it), there are very few covering how to get office automation up and running under IIS7 on a 64bit machine – and it is possible.

I needed to do this recently, and found one hint on how to get it working at http://forums.asp.net/t/1328690.aspx . They key is to use Process to launch the application you need, then attach to it.

Sample code in C#:


Microsoft.Office.Interop.Word.Application app = null;
Process proc = null;
Document doc = null;
try
{
ProcessStartInfo procinfo = new ProcessStartInfo(WORD_PATH, "");
procinfo.WorkingDirectory = AppDomain.CurrentDomain.BaseDirectory;
procinfo.CreateNoWindow = true;
procinfo.WindowStyle = ProcessWindowStyle.Hidden;
proc = Process.Start(procinfo);
proc.WaitForInputIdle();
app = (Microsoft.Office.Interop.Word.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Word.Application");
if (app == null) { throw new Exception("Word not found"); }
app.Visible = false;
app.DisplayAlerts = WdAlertLevel.wdAlertsNone;
#region Declare Params
object fileName = filename;
object ConfirmConversions = false;
object ReadOnly = true;
object AddToRecentFiles = Type.Missing;
object PasswordDocument = Type.Missing;
object PasswordTemplate = Type.Missing;
object Revert = Type.Missing;
object WritePasswordDocument = Type.Missing;
object WritePasswordTemplate = Type.Missing;
object Format = Type.Missing;
object Encoding = Type.Missing;
object Visible = false;
object OpenAndRepair = Type.Missing;
object DocumentDirection = Type.Missing;
object NoEncodingDialog = Type.Missing;
object XMLTransform = Type.Missing;
#endregion
doc = app.Documents.Open(ref fileName, ref ConfirmConversions, ref ReadOnly, ref AddToRecentFiles, ref PasswordDocument, ref PasswordTemplate, ref Revert, ref WritePasswordDocument, ref WritePasswordTemplate, ref Format, ref Encoding, ref Visible, ref OpenAndRepair, ref DocumentDirection, ref NoEncodingDialog, ref XMLTransform);
// DO WHATEVER YOU NEED TO HERE
}
finally
{
object saveChanges = WdSaveOptions.wdDoNotSaveChanges;
object OriginalFormat = Type.Missing;
object RouteDocument = Type.Missing;
try
{
((_Document)doc).Close(ref saveChanges, ref OriginalFormat, ref RouteDocument);
doc = null;
}
catch { }
try
{
((Microsoft.Office.Interop.Word._Application)app).Quit(ref saveChanges, ref OriginalFormat, ref RouteDocument);
app = null;
}
catch { }
try
{
proc.Kill();
}
catch { }
}


We’ve finally managed to find space (and get together the money) to start a Portland Hackerspace!

Check it out at http://www.pdxhackserspace.com/.

Well, I finally got around to restoring the blog after all this downtime. Hopefully I can start putting up more code here soon – I have a large backlog of small projects to post about.

Microsoft still hasn’t released a CentOS or RHEL RPM for Hyper-V’s Linux Integration Components, so you still have to build them yourself. Of course, since neither RHEL or CentOS are supported platforms, Microsoft won’t help you much.

So, with help from Julian Field’s work log on installing the original LIC v1, I’ve put together a minimal instruction set for performing a LIC v2 install on a fresh CentOS 5.2 install.

I started with an absolute minimal install, so there should be no packages other then those below that are needed.

Lines starting with “$” are shell commands.
Lines starting with “#” are something you need to do.


# In Hyper-V: Mount CentOS 5.2 ISO image

YOU ARE DONE!

To verify, look for the new seth* network interface.

Wasn’t that easy?

Boston Legal just brought up a good point tonight. one of the major purposes behind our revolution against England was to stop taxation without representation. Yet, today, many people under 18 earn enough to have to file taxes, but can not vote. While there are many arguments regarding the age at which you should be able to vote, one that is directly related to the founding of the USA does have a certian ring to it… comments?

If you are using VMWare Fusion under OS X 10.5 (Leopard) with bootcamp, and get an “Error loaded operating system message”, there is finally a simple fix.
Open your *.vmx file for your virtual machine, and add the following line:

ide0:0.biosGeometry=”1024/255/63″

that should do the magic!

Mobile Portland Group’s Slideshare.Net slides have been spotlighted on the front page of Slideshare.net

My iPhone Development presentation slideshow:

| View | Upload your own