[Geotools-devel] Re: GeoServer with Oracle Spatial

[<ch...@op...>]

> Hi,
>
> I'm busy putting on GeoServer on an Oracle Spatial DB. I've done some
> bug
> fixes I'm explaining in this mail. May be you can improve my roadmap
>
> First question is which release of GeoServer should I rely on.
> Optimistically I started with 1.2-rc2. As I had much problems I went
> down
> to 1.1.1.
Darn, I wish you had gotten in touch earlier, I just completed a bunch
of bug fixes to get oracle spatial working with geoserver on the 1.2
branch.  I'm testing on 10g, and it would be great if you could test
out on an earlier version.  You should definitely rely on 1.2, I can
put an rc3 out for you probably tomorrow (which if all goes well I'll
probably rename 1.2.0 pretty soon, as I need to get that out).


> There is one thing the GeoServer team can do to facilitate such
> operation.
> It would be absolutly great to have a way to get the exact source
> code of
> the geotools library used for each release of GeoServer. I've had
> much trouble trying several versions and I finally decided to
> decompile the jars
> you distribute. One way would be to redistribute the GeoTools source
> code in your source bundle. Another way would be to provide the exact
> versionnumber to use in the geotools CVS. This is important because
> GeoTools seems to be moving on fast and the coupling between both
> products is strong.
Yes, we've been working on this.  The problem in the past has always
been that geotools doesn't really release, so it was hard to match up
exactly with it.  In the old geotools cvs I put in a few geoserver
tags, so that one could check out the exact source, but I've gotten
lazy on the past few releases.  The next releases of both are going to
actually be coordinated though.  GeoServer 1.2.0 will use GeoTools
2.0-rc1 - I'm doing all my bug fixes on the 2.0.x branch of geotools,
which is quite nice because in the past we had to hand roll jars to
avoid unstable portions of geotools.  Once we put out gs 1.2.0 we will
release gt 2.0-rc1 using the exact same code that geoserver uses.  And
if you're interested in working off of cvs the geotools 2.0.x branch
(http://svn.geotools.org/geotools/branches/2.0.x/) is what the
geoserver cvs is using - I'm keeping the jars in geoserver inline with
those built from that branch.

> As my comments have much impact on GeoTools, I copy them to the
> GeoTools mailing list
Cool, I'm moving this to geotools-devel.  Most of the core geotools
developers are on both lists, as we're tightly coupled projects, both
in code and the developers writing the code.

> Let's go on !
>
> Problem 1: There are 2 major releases of sdoapi that have sightly
> different
> interfaces
Ok, I think we may have already solved this problem, by skipping the use
of the sdoapi.jar completely.  Jody Garnett at Refractions wrote an
sdoapi replacement, in anticipation of the fact that 10g was not going
to include the sdoapi.  Since that has happened we've put his changes
in geotools and geoserver makes use of them.  So for geoserver you just
need to include the classes12 jar, and oracle should work.  It would be
great if you could test these out on earlier versions of oracle, since
I've only done 10g - but it was written before 10g was out, so should
work with earlier versions.


> Here while everything compiles with the original source code and
> while the
> casting to OracleConnection is correct as you are connected to
> Oracle, for
> a reason I could not understand, the VM could not resolve the
> getGeometryMetaData at runtime ?!? The previous version of sdoapi
> required
> an OracleConnection so it is correct in a way to do it as it used to
> be. It
> just did not work.
That is _very_ odd.

> There is a more protable alternative for
> determining the
> srid by doing the following query:
>
> "select srid from user_sdo_geom_metadata where table_name='"
> +tableName.toUpperCase()+"' and column_name='"
> +geometryColumnName.toUpperCase()+"'"
Yes - this is exactly what I did:
http://svn.geotools.org/geotools/branches/2.0.x/gt/plugin/oraclespatial/src/org/geotools/data/oracle/OracleDataStore.java
Ok, not exactly, I didn't do toUpperCase, which I suppose wouldn't be a
bad thing - though Jody is against such 'magic'.  I actually think it
makes sense for oracle, as nothing works if things aren't upper case.

I'm sorry that we just had to replicate work, I probably should have
notified the list more of my fixes.


> 1c. I had another such problem in
> org.geotools.data.oracle.OracleSDOAttributeReader constructor with
> the call
> to OraSpatialManager.getSpatialReferenceManager(conn); The new code
> of this
> functions stands here:
>
>       public OracleSDOAttributeReader(AttributeType metaData,
> QueryData
> queryData,
>
> int
> columnIndex) throws DataSourceException
>       {
>             super(new AttributeType[]
>                         {
>                         metaData
>             }
>                   , queryData, columnIndex, columnIndex + 1);
>             this.queryData = queryData;
>             this.columnIndex = columnIndex;
>             try
>             {
>                   String tableName = queryData.getFeatureTypeInfo
> ().getFeatureTypeName();
>                   String columnName = metaData.getName();
>                   LOGGER.fine("About to create Geometry convertor for
> " +
> tableName +
>                                           "." +
>                                           columnName);
>                   //BDT: Used to be OracleConnection
>                   Connection conn = queryData.getConnection();
>                   oracle.sdoapi.geom.GeometryFactory gFact = null;
>                   int srid = queryData.getFeatureTypeInfo
> ().getSRID(columnName);
>                   if (srid != -1)
>                   {
>                         SRManager srManager =
> OraSpatialManager.getSpatialReferenceManager(
>                               conn);
>                         oracle.sdoapi.sref.SpatialReference sr =
> srManager.retrieve(srid);
>                         gFact =
> OraSpatialManager.getGeometryFactory(sr);
>                   }
>                   else
>                   {
>                         gFact =
> OraSpatialManager.getGeometryFactory();
>                   }
>                   adapterSDO = new AdapterSDO(gFact, conn);
>                   adapterJTS = new AdapterJTS(gFact);
>             }
>             catch (SQLException e)
>             {
>                   queryData.close(e, this);
>                   String msg = "Error setting up SDO Geometry
> convertor";
>                   LOGGER.log(Level.SEVERE, msg, e);
>                   throw new DataSourceException(msg + ":" +
> e.getMessage(),
> e);
>             }
>             catch (SRException e)
>             {
>                   throw new DataSourceException(
>                         "Error setting up SDO Geometry convertor",
>                         e);
>             }
>       }
If you could review this on the 2.0.x branch that would be great, I'm
not quite sure of the fix here.

> Problem 2 : The BBOX constraint does not work because the name of the
> geometric column is implicit. Fix in
> org.geotools.filter.SQLEncoderOracle
>
> In the constructor I take note of the first geometric column from the
> srids
> Map
>
>       public SQLEncoderOracle(String fidColumn, Map srids)
>       {
>             LOGGER.fine("SQLEncoderOracle("+fidColumn+","+srids+")");
>             escapedWildcardMulti = "\\.\\*";
>             escapedWildcardSingle = "\\.\\?";
>             currentGeomColumnName = null;
>             inGeomFilter = false;
>             this.srids = srids;
>             this.fidColumn = fidColumn;
>
>       //BDT try and determine the geometric column name from the
> provided
> srids
>             Set geomCols = srids.keySet();
>             if (geomCols.size() > 0)
>             {
>                   currentGeomColumnName = (String)
> geomCols.iterator().next
> ();
>             }
>             LOGGER.fine("SQLEncoderOracle: Geometric Column is:"
> +currentGeomColumnName);
>       }
>
> In doBBoxFilter I add a tolerence for left == null and in this case
> use
> currentGeomColumnName instead
>
>       private void doBBoxFilter(GeometryFilter geomFilter) throws
> IOException
>       {
>             Expression left = geomFilter.getLeftGeometry();
>             Expression right = geomFilter.getRightGeometry();
>             if ((left != null || currentGeomColumnName != null) &&
> right !
> = null)
>             {
>                   inGeomFilter = true;
>                   out.write("SDO_GEOM.RELATE(");
>                   if (left != null)
>                   {
>                         left.accept(this);
>                   }
>                   else
>                   {
>                         out.write(currentGeomColumnName);
>                   }
>                   out.write(",'ANYINTERACT',");
>                   right.accept(this);
>                   out.write(",0.001) = 'TRUE' ");
>                   inGeomFilter = false;
>             }
>             else
>             {
>                   LOGGER.warning(
>                         "Invalid filter. Cannot have a Geometry
> filter with
> only one expression.");
>             }
>       }

This is the one fix I didn't get to yet - I noticed it but didn't do it.
 You definitely have the right approach, it's what I do in postgis
datastore.  I'll commit it soon, and hopefully you can test it out? 
That would help me out a lot, since setting up my oracle testing
environment is a bitch.

thanks a lot for this work, it's all solid code, it's just unfortunate
that we were working on the same stuff at the same time.  If you could
test out 2.0.x branch and geoserver 1.2 that would be great, and I can
get you an updated geoserver war if you'd like.

Chris



----------------------------------------------------------
This mail sent through IMP: https://webmail.limegroup.com/