[CDBI] Re: Working with OGC geometry data types...

Judd Taylor judd at marine.usf.edu
Tue Feb 28 23:57:54 GMT 2006

Thanks Ed.

I figured out the:
thing a little while ago, and it's working well now. You're not kidding 
about undocumented, either :)

I'll try the SQL overrides as soon as I get a chance tomorrow. I'll post the 
results here.


----- Original Message ----- 
From: "Edward J. Sabol" <sabol at alderaan.gsfc.nasa.gov>
To: <judd at marine.usf.edu>
Cc: <classdbi at svr02.digitalcraftsmen.net>
Sent: Tuesday, February 28, 2006 6:46 PM
Subject: Re: Working with OGC geometry data types...

> Judd Taylor wrote:
>> I'm pretty new to cdbi, and I got a question on how to work with the
>> Open Geospatial Consortium (OGC) geometry data types now present in
>> MySQL and other databases.
>> Basically, the OGC geometry data type is a binary datatype that can
>> represent all sorts of geometries (points, lines, etc).
>> However, I don't want to deal with this thing as binary, since I don't
>> want to maintain code for doing things that the database itself can do.
>> When I pull the value, I want it's Well Known Text representation (aka
>> WKT), which is easily done in a simple modification to the SQL select
>> statement:
>> SELECT AsText(geometry) FROM __TABLE__;
>> When I input values to the database, I want to use the WKT format as
>> well, which needs another function:
>> INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));
>> In summary, I just want $class->geometry() to return text, and
>> $class->insert({ geometry => 'POINT(1 1)' }) to work.
>> What's the best way I can do this through Cdbi? Accessors, triggers, or
>> something else?
> I think this is somewhat similar in concept to working with DBMS-specific
> datetime or timestamp fields. Refer to the following wiki nodes for
> background:
> http://wiki.class-dbi.com/wiki/Setting_system_date_fields
> http://wiki.class-dbi.com/wiki/Setting_default_values
> I've never done anything like this, but I would start by redefining some 
> of
> the internal SQL statements used by Class::DBI, namely Retrieve and
> RetrieveAll, I believe. If you can ensure that the geometry column is not
> declared as an Essential column, then the Retrieve and RetrieveAll SQL
> statements are easily re-defined like so:
> __PACKAGE__->set_sql(Retrieve => <<'');
> SELECT __ESSENTIAL__, AsText(geometry)
> FROM   __TABLE__
> WHERE  %s
> __PACKAGE__->set_sql(RetrieveAll => <<'');
> SELECT __ESSENTIAL__, AsText(geometry)
> FROM   __TABLE__
> For insert() and update(), you want to specify the placeholder for the
> "geometry" column like so:
> __PACKAGE__->find_column('geometry')->placeholder('GeomFromText(?)');
> This last part is undocumented, I believe, so some warnings apply, but I
> very much doubt it will change, so you should be safe using it.
> Please report back and let us know if it works! And possibly add a wiki 
> node
> to the CDBI cookbook at http://wiki.class-dbi.com/wiki/Cdbi_cookbook
> Later,
> Ed

More information about the ClassDBI mailing list