Codex

A close reading of my last post reveals that I was confused – when installing PHPPgAdmin on the same computer as postgres, all that needs to be enabled for TCP/IP is localhost (127.0.0.1), not whatever local network you happen to have.  This got to be a bit frustrating when I tried to connect to my PostGIS database using Quantum GIS on a computer on my local network – the connection was rejected.

Luckily, the fix was easy.  Following this quick walkthrough for enabling remote access to PostgreSQL, I just had to edit one line in my /etc/postgresql/8.4/main/postgresql.conf file, from #listen_addresses='localhost' to listen_addresses='*'.  When commented out, it only listens on localhost; when specified to ‘*’, it listens for all connections.

Webmin, looks like a change could improve your PostgreSQL remote administration significantly – when users specify non-localhost TCP/IP hosts, you could check the settings in postgresql.conf.

Configuring PostGIS is almost as easy as the documentation makes it seem.  I just had two hangups -

1. I was skimming pretty quickly, so stupidly didn’t realize that the commands are from the command prompt instead of from the psql console.
2. The location of the files postgis.sql and spatial_ref_sys.sql was not immediately obvious.  Turns out they’re in /usr/share/postgresql/8.4/contrib/.

A quick SELECT postgis_full_version(); shows that I’m on the right version of PostGIS (1.4.0), but it looks like the GEOS & Proj4 libraries I compiled it against are pretty old (4.6 instead of 4.7 for Proj4; 3.0.2 instead of 3.2.0 for GEOS).  On the other hand, the functions list in phppgadmin shows the function I was looking for (st_asgeojson), so I’m calling this one a success.

Postgres has a really confusing authentication structure (at least for those of us used to the MySQL authentication).  It’s obviously very powerful, but like many Linux tools, that power comes at a price – unless you spend hours reading documentation, experimenting with setups, etc., you’re never going to fully understand what you’re doing.  At least, that’s my experience.

Using psql is pretty easy – su to the postgres user, then launch, since the postgres user by default has complete access locally.  Connecting over TCP/IP  is a different matter.  By default it’s locked down, so phppgadmin doesn’t allow you to log in as anyone by default.  I suppose that’s why the local (non-web) pgAdmin 3 is the default GUI administration tool.  Unfortunately for me, I’m very accustomed to phpmyadmin, and I’m running my postgres install on a mostly headless box, so the standard tool doesn’t really cut it.

I’ve set up the package before using this great phppgadmin setup walkthrough, but this time it just didn’t work for me.  Messing with pg_hba.conf files, etc. was getting me nowhere, until a random forum post made me realize something really straightforward – I have Webmin installed!  After that, it took about 2 minutes to set up and log in.

PhpPGAdmin setup with Webmin

• Log in to Webmin
• Select Servers -> PostgreSQL Database Server
• Select PostgreSQL Users
• Under the list of users, select Create a new user
• Enter username, password, & general permissions, then click Create
• Go back to the database list, then select Allowed Hosts
• Click on Create a new allowed host
• Check Host Address -> Network/CIDR.  If your local network is like mine (most devices assigned an IP via DHCP, you’ll want to put in the IP address for our local machine (192.168.11.5 for me), then set CIDR length to 24.  This tells postgres to allow users from 192.168.11.x; the CIDR length specifies how many parts of the IP address to ignore (32 means it matches against the entire number; 24 = first 3 parts; 16 = first 2; 8 = first 1).
• I allowed access to all databases
• Under Users select listed users, and type in the name of the user you just created
• Set the authentication mode to MD5 password
• Click Save

Worked perfectly.

Installing PostGIS was actually much easier than re-installing Postgres.  I did discover there are a couple of tricks to it, though:

• The steps for compiling are pretty straightforward – ./configure, make, make install, make check.  However, the documentation makes it seem like make check comes before make install, when it actually comes after.
• The default Postgres install grants all rights to the user postgres.  Installing PostGIS requires administrative rights at some point (or at least the ability to modify some system directories), which the postgres user doesn’t have by default.  My default user had not been granted any rights in Postgres.  It wound up being easier to temporarily add the user postgres to the admin group, run the full compile/install from the postgres home directory, then remove the user from the group when done.

The make check and user rights/Postgres rights issues are really straightforward to deal with, almost non-issues, but they did cost me a fair amount of head-scratching time.  Make check was really worth figuring out.  It’s a great utility for verifying your PostGIS install and ensuring that the various functions you’re looking for (GeoJSON support, in my case) are there and working.

Living in north Minneapolis, I’ve seen a lot of houses demolished over the past couple of years.  I’m sure this is a real pain for the city – vacant lots contribute approximately nothing to the community.  That said, I think it’s the right choice.  Most of the houses I’ve seen demolished appeared to be way past the point where any reasonable renovator would touch them.

One thing Minneapolis could be doing to add some sort of value to vacant lots is to plant them with a prairie mix.  Right now it looks like once the house is demolished they’re just leveled and left to sit.  The city could easily equip their public works crews with a few bags of a MnDOT seed mix and have them spread seed after a demo.  The rewards for this aren’t massive, but they are straightforward – nicer looking lots with wildflowers & prairie grasses are way better neighbors than lots with bare dirt and weeds.  With essentially no outlay in expense, the city could make a difference, even a slight one, in property values and bird & insect habitat.

There are other proposals about what to do with vacant lots.  This Daily Planet article has a comment offering a suggestion about community gardens that I’ve heard before, for example.  The advantage of seeding with a prairie mix is that it’s not a case of doing one instead of the other.  You can always plant prairie while you’re waiting for the paperwork to line up for a garden.  Establishing a better mix of plants in urban areas is a cheap win-win.

In the Terry Prachett book Eric, the demons of hell deal with their bureaucracy-obsessed king by convincing him to undertake the (eternal) task of creating a plan for all of damnation, complete with goals, policies, and mission statements, so that they can get back to their own work.  Even the inmates are relieved that plastic plants and offensively bland music have been replaced with fire and brimstone.  I considered this to be one of the more genius things I had ever read, with many potential applications in my working life.  Then I realized that without goals, policies, and mission statements my job probably wouldn’t exist, and I decided not to think about it any more.

Can’t believe this utter crap was on the front page of google news.  Cause really, it doesn’t make any sense to question a system that creates ungodly expensive treatment options that might allow an elderly person a few more miserable years or months of life, then call the ethics of their families into question when they’re not absolutely certain that it’s right to ruin themselves financially to pay for it.  Yeah, that’s the way health care should be – force people to make agonizing choices so that pharmaceutical and medical device companies pull in massive profits.

The Obama response to the economic resession is to try and make some big reforms (which really bothers people on the far right, who think the concept of “not wasting a crisis” is dirty politics), and to implement the New Deal style of economic crisis management.  Most economists seem to be all for some kind of government intervention, but the general theme appears to be that the best way for government to spend on infrastructure is to pay for things that will actually increase economic activity, and not just put people to work.  That’s too bad, because I think we’re about 10-20 years away from a couple of major infrastructure projects that could massively transform the modern economy. Read the rest of this entry »

I hear that Minneapolis Mayor RT Rybak is all about the alternative approaches to economic development, so I thought I would offer one up myself: government supported, neighborhood based peer-to-peer lending. Read the rest of this entry »