Dear GeoTools comunity,
I'm struggling to make ImageMosaic work with time dimension when using
Oracle database. I'm not quite sure if this should go to the devel
mailing list or to the issue tracker directly so I'm using this list as
a starting point for discussion.
The following schema (an excerpt from the indexer.xml file) does not
work with Oracle datastore (however, it works perfectly in Postgres):
<schema name="default">
<attributes>*the_geom:Polygon,location:String,time:java.util.Date</attributes>
</schema>
The problem is that my data have date AND time, but /something /between
GeoServer and the database itself truncates the time part.
After digging into the GeoServer and GeoTools code I found out my main
suspect. I believe that the existing mapping between Oracle database
types and Java types in the org.geotools.data.oracle.OracleDialect class
in
gt-jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java
is the cause. On line 165 (in current geotools 16.x branch) it maps the
DATE type to the java.sql.Date class. As we all know, java.sql.Date does
not store the time information.
In my opinion, this mapping is incorrect. The correct mapping should be
DATE to java.sql.Timestamp, because the DATE type in Oracle database
includes the time down to a second. The current Oracle JDBC driver maps
DATE to java.sql.Timestamp by default, as stated on their website (they
used to have it wrong too, but fixed it):
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01
...the 11.1 drivers by default convert SQL DATE to Timestamp when
reading from the database. This always was the right thing to do and
the change in 9i was a mistake...
The commit which introduced the DATE ->java.sql.Date mapping looks like
it was a deliberate decision (
https://github.com/geotools/geotools/commit/ba023c75ebd13279c3beb69721bfd361bf3a6908
https://osgeo-org.atlassian.net/browse/GEOT-3216 ). Maybe changing the
mapping is not a good idea after all (I don't know what might break in
the GeoTools universe).
So, I tried to change my schema to the following:
<schema name="default">
<attributes>*the_geom:Polygon,location:String,time:java.sql.Timestamp</attributes>
</schema>
Unfortunately, it creates the /same /database table, with the DATE type
for the time column. Maybe if this schema would create a TIMESTAMP
column, then the default JDBC mapping would be from TIMESTAMP to
java.sql.Timestamp and the time information would be retained.
When figuring out why java.sql.Timestamp is translated to the DATE
database column, I found out that:
* initially, there is no mapping from java.sql.Timestamp to any Oracle
data type (org.geotools.jdbc.JDBCDataStore.getSQLTypeNames, lines 3126
to 3156). the integer value to map is 93 (
http://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.TIMESTAMP
)
* the metaData.getTypeInfo() (line 3198) is used to get default database
type mappings
* the type java.sql.Timestamp gets mapped to DATE database type in an
interation of the following while loop
To sum up:
1) Oracle JDBC driver mapsjava.sql.Timestamp to the DATE database type
because, well, it's theirs decision (if there is any millisecond
information in the Timestamp it would be lost I guess, but who wants
milliseconds anyway?).
2) GeoTools maps DATE database type to java.sql.Date type, apparently
because of backwards compatibility issues.
Therefore, when creating ImageMosaic with time dimension and some actual
time values, the features-to-be-inserted are converted from
java.sql.Timestamp to java.sql.Date and the time information is lost.
What can we do about it? Is it better to discard the DATE ->
java.sql.Date mapping and leave it all to Oracle JDBC defaults; or we
should add another mapping saying java.sql.Timestamp should go to the
TIMESTAMP database type? Or do nothing, because there is some hidden
Oracle JDBC switch which achieves the desired mapping and I'm just not
aware of it?
I'm looking forward to any suggestions.
I found all of this while working with GeoServer 2.10.0 and GeoTools 16.
My Oracle version is 12cR1, I'm using the ojdbc7.jar, my Java is
jdk1.8.0_102, everything runs in Tomcat 8.5.4 and I'm using the
-Doracle.jdbc.J2EE13Compliant=true property to get java.sql.Timestamp
instead of oracle.sql.TIMESTAMP when using SQL Views in GeoServer.
Cheers,
Peter
--
Peter Kovac
IMS Programmer
MicroStep-MIS
peter.ko...@microstep-mis.com
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
GeoTools-GT2-Users mailing list
GeoTools-GT2-Users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
