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

[Alexandre Pretyman]

Hi Boris,

On Thu, Jul 12, 2012 at 5:39 AM, Boris Kolpackov
<boris at codesynthesis.com> wrote:
> Hi Alexandre,
>
> Alexandre Pretyman <alexandre.pretyman at gmail.com> writes:
>
>> Yes, I would like to give it a try! Should I just clone from
>> http://scm.codesynthesis.com/?s=odb/?
>
> Yes, master has this feature merged, though bootstrapping ODB from
> the repository can be a bit challenging (see INSTALL-GIT). I am also
> planning to package a pre-release in a few days, so you may want to
> wait for that.

Ok, I will wait then.

>> I tried searching the manual for it, but I guess it's too early to
>> be documented, since I did not find it.
>
> It is Section 12.6, "Database Type Mapping Pragmas". There is also a
> test (<database>/custom) for each database in the odb-tests package
> that tests this functionality. The ones for MySQL and MSSQL test
> GEOMETRY, besides other things.

I see it now in the git repository under odb/doc/manual.xhtml , thanks

>
>> 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)"
>
> Yes, the new mechanism allows you to specify to/from conversion
> expression that will transform the ODB-generated statements to the
> ones you've shown.
>
>
>> 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?
>
> Yes, I've also been thinking about this same issue. Right now the
> mechanism only supports "one-value to one-value" mapping. While, if
> we want to pass both, say, a point ("POINT(1 1)") and its SRID (4326),
> then we are looking at two separate values. I also find it strange
> that GeomFromText/AsText don't encode this information in the
> returning text representation.
>

>From what I've searched, it doesn't provide the SRID from those
functions because the WKT and WKB formats are defined by the Open
Geospatial Consultorium, and they defined (for a reason unknown to me)
that it shouldn't carry the SRID.

>
> I see several alternative ways to handle this:
>
> 1. Most applications will probably be using a single SRID, so the
>    simplest way is to just hardcode this value into the conversion
>    expression:
>
>    #pragma db map type("GEOMETRY")              \
>                   as("TEXT")                    \
>                   to("GeomFromText((?), 4326)") \
>                   from("AsText((?))")
>
>    This is what the tests currently do.
>

I guess you are talking about the tests with under
odb-tests/pgsql/custom/tests.hxx and traits.hxx where you persist a
type POINT.  This looks like it will do the job!

> 2. If the application uses several different SRIDs, then another
>    alternative would be to create aliases for GEOMETRY with specific
>    SRIDs and then use that. In case of PostgreSQL:
>
>    CREATE DOMAIN GEOMETRY4326 AS GEOMETRY;
>
>    #pragma db map type("GEOMETRY4326")          \
>                   as("TEXT")                    \
>                   to("GeomFromText((?), 4326)") \
>                   from("AsText((?))")
>
> 3. Finally, if you want completely dynamic SRID support, then one
>    way to achieve this would be to create wrapper functions in the
>    database's procedural language that basically extend GeomFromText
>    and AsText to include SRID in the text representation. Say,
>    something like "POINT(1 1) 4326". So if we had GeomFromTextWithSRID
>    and AsTextWithSRID, then we could write:
>
>    #pragma db map type("GEOMETRY")                \
>                   as("TEXT")                      \
>                   to("GeomFromTextWithSRID((?))") \
>                   from("AsTextWithSRID((?))")
>

Just FYI, PostGIS has asewkt and asewkb which are extended versions of
the WKT and WKB which give you the SRID at the beggining:
"SRID=5432;POINT(1 1)". Then it's up the the Geometry Library to parse
it, but I'm not certain that GEOS supports it.


>
>> 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.
>
> ODB doesn't support GEOS or Boost.Geometry directly (though having
> a profile that provides this support for all the databases would be
> nice). Rather, ODB provides a generic mechanism with which you can add
> this support yourself.
>
> Let me know what you think.
>

>From what I've seen in the git repository under odb-tests/pgsql/custom
and in the manual, it looks like this will suffice! Great work. I will
be on stand by for the pre-release package.

>
> Boris



-- 
Alexandre Pretyman