Technical Ramblings

This week, I had the oppourtunity to work with Schuyler in writing stored procedures in Postgres/PostGIS for the first time.

At first, we were writing in plpgsql, but found it didn’t suit our needs… and switched to Python.

Yes, Python. Our database now has stored procedures which decode a cPickle pickle structure from a column (go go unstructured data) and return the output of a key/value pair (based on the key).

It was a very weird thing to see this actually work.

In the vein of my previous post, I bring you another nifty trick: This time integrating Google Maps and Mapserver (kind of).

Visit static renderer. Browse Google Map. Then, when you’re looking where you want to be, hit the link up top, and off you go — transported to a world where data is public domain or licensed for re-use 🙂

I’m still trying to find a happy medium level of size of markers — when you’re zoomed way out, they’re too big, but when you zoom way in, they’re too small, so I don’t know what to do, but it *works* and that’s the important part.

Interface suggestions welcome — perhaps a side by side view is better? (I think I may be having a bit too much fun…)

After hours of fighting with Postgis, Schuyler finally got bia into a state where she would do the right thing when told to install it, so I was able to load the data from the Open Guide to Boston into postgis, and from there, to talk to it with mapserver. The result is a couple of pretty cool looking maps: Boston Metro, and Boston Metro Big, 1000×800 and 2000×1600 respectively.

The maps underneath are provided by Public Domain datasets, wrapped up in a tidy little easy to use package by the folks at OpenPlans through their “Sigma” project, and I’m extremely grateful to them for their efforts! They’ve saved me a ton of work, and allowed me to produce something that looks pretty damn cool.

If that’s not an advertisement for Open Geodata, I don’t know what is.

Thanks to Schuyler and Rich Gibson, I now have a spatially aware postgres database.

Later today, thanks to Schuyler and zool, I’ll have a copy of Mapping Hacks, and a bluetooth GPS.

Last night, I learned how to use centroid(), astext(), and distance_spheroid(), and calculated the distance from my house to zool’s house, and from there to Darwin’s, where I ate lunch and used the wireless yesterday. I loaded some data, learned the frustration of having data in different projections, and learned a little bit about the various types of geometry. I loaded data from an ESRI shapefile. I found that “” in Postgres is equivilant to “ in mysql — that is, “GEOMETRY” means ‘the value from column Geometry’, not ‘GEOMETRY’, which is the literal. (If you ever get “Error: column “Foo” does not exist, that might be a good thing to check.)

Last night, I made my first foray into spatial databases.

Last night, I took control of space on my machine.

Tomorrow, I take control of space in the world!

But today, I need to work on things that I’m actually paid for at the moment. 😉