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

> Judd Taylor wrote:

Thanks Ed.

I figured out the:
__PACKAGE__->find_column('geometry')->placeholder('GeomFromText(?)');
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.

Thanks!
Judd

----- Original Message ----- 
From: "Edward J. Sabol" <sabol@xxxxxxxx.xxxx.xxxx.xxx>
To: <judd@xxxxxx.xxx.xxx>
Cc: <classdbi@xxxxx.xxxxxxxxxxxxxxxx.xxx>
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
> 

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