PostGIS template on macport postgresql84

We use postgresql and postgis for one of our websites we maintain: byways.org.

I use a mac with development environment installed via macports. I originally followed most of the instructions on: http://www.robbyonrails.com/articles/2008/01/22/installing-ruby-on-rails-and-postgresql-on-os-x-third-edition

Today I did an update to postgresql 8.4 and it was messier than I had hoped. After running:
sudo port selfupdate
sudo port upgrade outdated

I waited about 3 hours to download and compile all my macports.

Then:

port install postgresql84 postgres84-server
at the end there are instructions to modify your path and launchDeamon as mentioned in the robby on rails post.

However - here is where it gets fun -- I uninstalled postgresql83-server and modified my path to point to 84 not 83. You have to re-boot to get the database up and running again with the appropriate path etc.

Then, I wanted to build me a template_postgis database as outlined here:
http://geospatial.nomad-labs.com/2007/12/16/template-postgis-database/

except they moved some files, and I had to run it like this:
$ psql template1
\c template1
CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'UTF8';

-- next set the 'datistemplate' record in the 'pg_database' table for
-- 'template_postgis' to TRUE indicating its a template
UPDATE pg_database SET datistemplate = TRUE WHERE datname =
'template_postgis';
\c template_postgis
CREATE LANGUAGE plpgsql;
\i /opt/local/var/macports/software/postgis/1.4.0_1+postgresql84/opt/local/share/postgresql84/contrib/postgis.sql
\i /opt/local/var/macports/software/postgis/1.4.0_1+postgresql84/opt/local/share/postgresql84/contrib/spatial_ref_sys.sql

GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;

-- vacuum freeze: it will guarantee that all rows in the database are
-- "frozen" and will not be subject to transaction ID wraparound
-- problems.
VACUUM FREEZE;

Restore my database from backup and away we go.

Hope this helps someone out there.

Error Fix: Server msg 18, level 16, state 1

Today I ran into one of those really annoying bugs, the kind you get right at the opportune moment you need the software to work and it doesn't.

This time I was trying to run my application that uses Microsoft's SQL Server 2000. I couldn't connect to the database for some reason.

From SQL Query Analyzer it said:

Unable to connect to     server. Server msg 18, level 16, state 1. [Microsoft] [ODBC SQL Server Driver}     [Shared Memory] SSL security error

From Enterprise Manager it said:

Unable to connect to server. Reason: SSL security error.     ConnectionOpen (SECDoClientHandShake())...

Microsoft had a solution for the problem that said it was trying to use a certificate that wouldn't allow it to work. See: http://support.microsoft.com/kb/302409

However, that wasn't the solution I needed. After looking at some stuff for a while, I called my co-worker and he clued me into a possible solution.

We ran start->run application -> "MMC", then added a snap-in via File->Add/Remove Snap-in, click the button to add ..., then choose Certificate in the snap-in options. I chose Computer Account, then local computer, then it adds the snap in. Close the add form and the Add/Remove Snap-in form.

On the tree view of the MMC console - open Cerficates -> Personal -> Certificates. I had two certificates listed there. (remembering back when I did it, I accidentally created two when I ran selfssl.exe - a tool to create a development testing self signed certificate.) Apparently SQL Server has a tissy-fit when it finds more than one certificate.

I deleted one of the certificates after backing it up just in case I got the wrong one. Restarting my database server refreshed the certificate registration and did the trick. I'm up and running again. Grrr - I hate these one hour find a fix bugs that stop my flow of normal work, but thought I would put this down here so that others could perhaps benefit - even just myself when I do it again.

Git Hub

I just created my first github account, it was painless (except the part of finding my ssh key - I can never remember where that is, for some info see the help link on the registration page if you need more info).

I'll be putting some of my little pet projects to this shared repository. I don't mind collaboration on code and projects, though I doubt people will want to even look at my code.

See http://github.com/technolingua

TextMate

I've tried out the mac editor called TextMate. A lot of people rant and rave at how wonderful this little application is.

I watched a few videos last night about how to use columns, move text around and all that. I was very impressed. I have 6 more days left on my 'trial' before I have to pay to continue using it. I'm quickly leaning to buying it.

I'm currently installing smarty bundle for Text mate. I was following the instructions on http://blog.clickablebliss.com/2006/01/04/teach-textmate-about-smarty/ and had an issue. The subversion checkout has a space in the url and kept returning a 404 page. The current url is
http://macromates.com/svn/Bundles/trunk/Bundles/PHP%20Smarty.tmbundle/

So you need to run to get the subversion code.
SVN --username anon --password anon co http://macromates.com/svn/Bundles/trunk/Bundles/PHP%20Smarty.tmbundle/

That post was a little old - posted in 2006, and needed to be updated. I also had to use the Bundles->Bundle Editor->Show Bundle Editor

Then I had to filter for languages, and the rest of the instructions made sense.

Hope that helps someone! Here's hoping I enjoy TextMate