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

[prev] [thread] [next] [Date index for 2006/02/28]

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

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi