[odb-users] Adding support for a new sql type

[Alexandre Pretyman]

Hi Boris,

On Wed, Jul 11, 2012 at 6:28 AM, Boris Kolpackov
<boris at codesynthesis.com> wrote:
> Hi Alexandre,
>
> Boris Kolpackov <boris at codesynthesis.com> writes:
>
>> Now, if the new type is "interfaced with" using one of the existing SQL
>> types (which, as I understand, is the case with GEOMETRY), then this
>> could be easier. Then we could probably "map" the new type to the
>> existing type. We would probably also need to specify to/from
>> conversion expressions (ST_GeomFromText/ST_asText in case of GEOMETRY?)
>> that will need to be applied in the INSERT, UPDATE, and SELECT statements
>> generated by the ODB compiler.
>
> In case you are still interested in GEOMETRY support in ODB, I just
> finished implementing this feature for 2.1.0. As it turns out it can
> be used to support all kinds of "additional" database types besides
> GEOMETRY (things like user-defined types, arrays, XML, etc.).

> Let me know if you would like to give this feature a try before the
> release.

Yes, I would like to give it a try! Should I just clone from
http://scm.codesynthesis.com/?s=odb/ ? I tried searching the manual
for it, but I guess it's too early to be documented, since I did not
find it.

> Boris

Being embarrassed that I never actually answered you, I'll answer you now.

I've searched around to see what would be a standard way to deal with
the GEOMETRY type in some RDBMSs supported by ODB (the links below are
for reference only)
    PostgreSQL [
http://postgis.refractions.net/docs/using_postgis_dbmanagement.html#OpenGISWKBWKT
]
    MySQL [ http://dev.mysql.com/doc/refman/5.6/en/populating-spatial-columns.html
and http://dev.mysql.com/doc/refman/5.6/en/fetching-spatial-data.html
]
    SQLite [ http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html ]

And there seem to be 2 standard ways of interfacing with GEOMETRY
types, one is text (as WKT - Well Known Text) and in binary (WKB -
Well Known Binary).

The example below was done in PostgreSQL using WKT to make it
readable. Consider the following table:

CREATE TABLE gisexample
(
  id bigserial,
  the_geom geometry
);

Inserting:
INSERT INTO gisexample (the_geom) VALUES (GeomFromText('POINT( 0 0 )'));

Updating:
UPDATE gisexample SET the_geom = GeomFromText('POINT( 1 1 )') where id = 1;

Selecting:
Select AsText(the_geom) from gisexample;
Returns: "POINT(1 1)"

With the returned string, one would be able to use a geometry library
like the GEOS Geometry Library or Boost.Geometry to build a geometry
instance.

However, it is also interesting (but not prohibitive, if it is not
possible) to extract the reference of the coordinate system of the
points, known as SRID (Spatial Reference ID), from the GEOMETRY type:

SELECT ST_SRID(the_geom) from gisexample

which returns -1 since it wasn't explicity set. One way to set it is
using the  SetSRID function

UPDATE gisexample SET the_geom = SetSRID(the_geom, 4326) where id = 1;


In GEOS, the SRID is stored in the geometry itself with
Geometry::setSRID method, but Boost.Geometry on the other does not
treat the SRID and the client programmer is required to store it by
himself. Do you think this new functionality you've just added can
treat this?

Today I'm using Boost.Geometry with Qt, so I would have a class, for
example like:

class MyGeometry
{
long id; // db identifier
QString descriptiveText;
int srid; // value filled from ST_SRID(the_geom)
QPolygon boostGeometryPolygon; // value filed from AsText and
populated with boost::geometry::read_wkt
}

Here the srid and boostGeometryPolygon would point to the same
database column. Would it be possible?

I'm looking forward for to see the solution you proposed, specially if
you are able to support GEOS and Boost.Geometry. I can test them both
for you.

Kind regards,
-- 
Alexandre Pretyman