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

[Boris Kolpackov]

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.

> 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.


> 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.

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.

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((?))")
   

> 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.

Boris