The development version of the Trip Planner uses a Postgres/PostGIS backend (instead of MySQL) SQLAlchemy as the ORM (instead of raw SQL), and PCL for Python geometry types (instead of our own ugly hacked versions).
Question: How do you move geometries out of Postgres/PostGIS into PCL types via SQLAlchemy and vice versa?
Answer: Create a custom geometry column type.
Here’s our SQLAlchemy geometry type definition and subtypes (points, linestrings, and multilinestrings; adding other types should be trivial):
Look here for an example of using it:
The type is pretty simple. The only tricky part was figuring out how the database stores the geometry. It’s in ASCII hex, so we use binascii.a2b_hex to get a binary representation and feed that to the PCL fromWKB factory. In the other direction we use binascii.b2a_hex on the WKB representation of the PCL geometry.
[8/7/09: Updated links to source code. Note: latest version uses Shapely, not PCL.]