JBossDeveloper

Very cool! Thanks for cleaning up the SELECT/LITERAL stuff and adding JTS to the kit. Rewriting with the ST_* functions makes a lot more sense than what I had with CONVERT.

-----------

I'm hoping to get some time towards the end of the week to tackle Geometry SRID. This shifted priorities because (unlike MySQL) Oracle requires SRID to be set for any kind of comparison (unless both are NULL). This isn't something that can be stored in WKB, so I'm thinking of adding an int field to GeometryType to keep track of it. The tricky thing is going to be getting it out of the database with one select. MySQL GEOMETRY type seems to be the 32bit SRID concatenated with the WKB blob, so my idea is to force something similar and then split off the SRID when reading the value back.

On MySQL this returns the SRID + WKB.

select CONCAT(RPAD(REVERSE(CHAR(SRID(shape))), 4, CHAR(0)), AsWKB(shape)) from cola_markets;

(Note that "select shape from cola_markets" does the same thing, but I can't find this in any documentation. It looks like GeoTools relies on it however.)

The strategy would be to rewrite Teiid to MySQL like this:

select shape from cola_markets;

select ST_AsTeiidFormat(shape) from cola_markets;

select  CONCAT(RPAD(REVERSE(CHAR(SRID(shape))), 4, CHAR(0)), AsWKB(shape)) from cola_markets;

When retrieving values from results, would splice off the first 4 bytes and save as SRID int field on GeometryType. Going the other direction (literals) would be pretty straightforward because the source functions to convert from WKB/WKT all take an optional SRID value. Work in progress implementation: gist:2e73037eff85fe9eddff

(Note that gist is slightly outdated; I will rebase and work from latest.)

-----------

Note that the Oracle support was added from docs + memory and has not been tested yet! (I am downloading Oracle Enterprise as I type this.)

There are some known flaws in the Oracle support as-is. First off, the SDO_UTIL functions (at least dealing with WKT/WKB) do not work on Oracle XE because they are implemented in Java and there is no JVM in that edition. Secondly they only support an early version of the OGC spec which means that even though Oracle supports more than 2 dimensions, it is not possible to specify Z or M coords with them. I don't think the second part is that big of a deal for now because >2d is missing from most data sources I can think of. One workaround I'm investigating is reimplementing the conversion functions in PL/SQL, similar to the work done here: general/pg_user_geom_util_body.sql at master · TolonUK/general · GitHub

Is it possible that anyone is currently selecting the Oracle geometry structs through Teiid and using the OJDBC classes to work with them? I think these changes would break that use case, so it might be good to add a system property to disable recognizing columns as GEOMETRY.

Thanks!

Tom

Closest thing to a EWKB spec (that I can find) is here http://tsusiatsoftware.net/jts/javadoc/com/vividsolutions/jts/io/WKBWriter.html.

Pulling back as text would definitely be simpler, but it's the same basic idea. I've been trying to stick with the binary format for supposed efficiency. I'm not sure how array/struct would work.

select 'SRID=' || SRID(shape) || ';' || AsText(shape) from cola_markets;

I will focus on adding support for sending SRID to the database first, as that should be simple.

• Add SRID integer field on the GeometryType
• Add optional SRID argument to Java functions that convert from WKB/WKT to GeometryType.
• Change Literal GEOMETRY rewrite in JDBCExecutionFactory to pass geometry SRID as second value.

For reading SRID back it might be simpler to just store it as a property on the column metadata since it _should_ be the same for all values in that column. From a quick survey of the GeoTools JDBC modules it appears they all read the SRID from geometry_columns[1] table. This could initially be set manually by DDL, and we could phase in support for the importers to pick it up. [2]

[1] From OGC Simple Features (?) spec. One row for every GEOMETRY column in the database. Keeps track of geometry type, SRID, coordinate dimension, etc.

[2] geometry_columns (and spatial_ref_sys) are not implemented across the board. Oracle names them something else, MySQL doesn't have them, etc. There will need to be some DB-specific logic to detect this stuff.

Thanks,

Tom

> I see that fits with mysql, but it does not line up with Oracle as their conversion functions from WKB, WKT do not consider SRID.

These look a little more promising.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm#CBBFGHAE

SDO_GEOMETRY(wkt CLOB, srid NUMBER DEFAULT NULL);

SDO_GEOMETRY(wkt VARCHAR2, srid NUMBER DEFAULT NULL);

SDO_GEOMETRY(wkb BLOB, srid NUMBER DEFAULT NULL);

Thanks,

Tom

Need to find some time this week to test these, but here they are:

Initial cut at SRID support:

https://github.com/Tom9729/teiid/commit/fe2d5c428a34b86f2fa57e136a46a0e85ac5b242

Oracle pushdown support:

https://github.com/Tom9729/teiid/commit/42dc924df2e08f41797260fa721ce2cba2a35b17

I will see about filling out the spatial docs.

Thanks,

Tom

Fixed some issues with spatial filter pushdown and MySQL.

https://github.com/Tom9729/teiid/commit/183e660e77ff6fca2d7421aa6a4d2bf93b55dabb

• Default value of pushdown for the function annotation is CANNOT_PUSHDOWN, and all of the geometry functions were set like this. I changed all of them (except the conversion ones) to CAN_PUSHDOWN.
• MySQL translator uses DB version to determine whether to enable spatial filter pushdown, but usesDatabaseVersion=false so the database version never gets set on startup. I changed this to true to fix it, but I think this is kind of confusing. IMO it would be better to have getVersion() throw an exception if the version is null.

I can't seem to track down the cause of this issue, but this insert seems to cause a NPE if org.teiid.COMMAND_LOG is set to TRACE. I only see an error in Squirrel, and the insert appears to work.

insert into cola_markets(name,shape) values('foo124', ST_GeomFromText('POINT (300 100)', 8307));

Error: java.lang.NullPointerException

SQLState:  38000

ErrorCode: 0

Other than that, seems to be working well. My next focus will be to give some attention to Oracle.

Thanks,

Tom

> The use of SDO_GEOMETRY with a blob binding (as a stream) fails for me

Are you passing a valid SRID? If unspecified, it will send UNKNOWN_SRID (aka 0) which might make Oracle unhappy.

https://github.com/Tom9729/teiid/commit/98e3ade67e3561fb91b2a5ab4a28074581f1260a

Thanks,

Tom