Hi Jukka,
I definitely agree with you: as is, the Oracle support is not useful for
big databases.
The geometry type stuff was more a test: I tried to be consistent with
Postgis code, that can use metadata table/view to get this information.
And yes, the strength of OJ compared to, say, Qgis, is its ability to
handle mixed geometry types.
I will change the code:
• To use the index metadata query in a safe transaction, to see I we can
get any information on geometry type at this level
• To remove the ugly DISTINCT query and default to GEOMETRY type for Oracle
spatial stores.
Yes, multiple geometry column should work. (only tested it with some simple
tables, with 2 or more SDO_GEOMETRY columns.
One datasource is created for each distinct combination of
table(view)/geometry column.
I'll keep you posted.
Thanks a lot for the feedback !
Nicolas
On 7 December 2015 at 14:29, Rahkonen Jukka (MML) <
jukka.rahko...@maanmittauslaitos.fi> wrote:
> Hi Nicolas,
>
>
>
> Thanks for improving logging. I am testing with r4578 and I don’t get ORA
> errors. However, it will be very impractical or impossible to use our
> databases like the datastore is trying to do it now.
>
>
>
> 1. The first query is “select distinct owner, table_name from
> ALL_SDO_GEOM_METADATA” and that’s OK.
>
> 2. Second query to be executed for each row that is returned by the
> first query is “select column_name, srid from ALL_SDO_GEOM_METADATA WHERE
> owner = '<owner>' AND table_name =’< table_name>' and that’s OK as well.
>
> 3. Third query, to be executed for each table_name that is returned
> by query 1. is “select distinct t.<geometry>.sdo_gtype from <table_name> t”
> and that breaks the whole system for us.
>
>
>
> We have many large tables with millions of rows and “select distinct
> geometry.sdo_gtype” takes several minutes per table. What is much worse is
> that we have views with complicated relations between source tables and
> “select <geometry.sdo_gtype>” will take hours. Those views do not suit at
> all for simply adding them to map but user must always make a query with
> well-defined filters. However, we must register the views into
> SDO_GEOM_METADATA and there is no way for preventing them to appear on the
> table list of OpenJUMP Oracle datastore.
>
>
>
> I am not sure if it is really necessary for OpenJUMP users to know the
> geometry type of the layer because OpenJUMP can handle all geometry types
> including mixed geometries on the same layer. As far as I know there are
> exactly two ways to check the GTYPE of an Oracle table or layer:
>
> 1) To use the SELECT DISTINCT sdo.gtype method. It gives definitely
> a correct answer but unfortunately the query may take too long time if
> tables are big, views are complicated, or there are lots of tables and
> views.
>
> 2) If spatial index is created with layer_gtype parameter then the
> corresponding table can hold only that sort of geometries
>
> CREATE INDEX SPATIAL_IDX ON TABLE(GEOM)
>
> INDEXTYPE IS MDSYS.SPATIAL_INDEX
>
> PARAMETERS ('layer_gtype=POINT');
>
> In that case the gtype of the layer can be queried from the metadata
> tables. It is a bit complicated, though, but there is code in GeoTools
> sources that can be used as an example
> https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java
>
>
>
> This kind of query works for me with SQL Plus:
>
> SELECT META.SDO_LAYER_GTYPE
>
> FROM ALL_INDEXES INFO
>
> INNER JOIN MDSYS.ALL_SDO_INDEX_METADATA META
>
> ON INFO.INDEX_NAME = META.SDO_INDEX_NAME
>
> WHERE INFO.TABLE_NAME = ‘TEST_TABLE'
>
> AND REPLACE(meta.sdo_column_name, '"') = 'GEOM';
>
>
>
> I had to edit the GeoTools code in the REPLACE but perhaps it is only
> because " must be escaped for Java as \" . The name of the geometry_column
> is saved with double quotes into SDO_INDEX_METADATA "geom_column" and that
> makes the need for REPLACE.
>
>
>
> I have two alternative suggestions:
>
> 1) Don’t care about the geometry type at all when building the list
> of spatial layers and views for Oracle but simply label them all as generic
> GEOMETRY.
>
> 2) If you want to try to find the exact geometry type, re-use the
> GeoTools query even it has its limits: It can only work if layer has a
> spatial index which is created with layer_gtype parameter which is not the
> default. For registered views it will fail. Also, I have learned that is DB
> administrator grants select rights for tables and views, the rights for
> making queries from SDO_INDEX_METADATA do not follow automatically.
>
>
>
> I have not been testing yet if the Oracle datastore can handle correctly
> tables with two or more geometry columns. At least it starts right because
> query 2 will get a row for each geometry column
>
>
>
> -Jukka Rahkonen-
>
>
>
>
>
> *Lähettäjä:* Nicolas Ribot [mailto:nicolas.ri...@gmail.com]
> *Lähetetty:* 4. joulukuuta 2015 23:36
> *Vastaanottaja:* OpenJump develop and use
> *Aihe:* Re: [JPP-Devel] How to debug Oracle data store?
>
>
>
> I have committed my local modifications for Oracle Support.
>
>
>
> I tested the release and was able to load spatial tables (if properly
> registered to Oracle Spatial Metadata tables) from Oracle 11g and 12c.
>
>
>
> Jukka, could you post the errors you see when using Oracle DataStore ?
>
>
>
> Nicolas
>
>
>
> On 4 December 2015 at 21:41, Nicolas Ribot <nicolas.ri...@gmail.com>
> wrote:
>
> I had commented out Oracle dependencies in the pom.xml and my Netbeans IDE
> did not like that: the running classpath (running OJ directly from NB) was
> not set correctly, and ojdbc6.jar was missing at runtime...
>
>
>
> Thanks for introspection hint.
>
>
>
> With the current code, at runtime, if no Oracle driver is found, the
> OracleDatastore is disabled.
>
>
>
> Jukka:
>
> I intended to support Oracle Spatial (and other spatial databases like
> Spatialite, MariaDB, SQL Server) through OGC SF SQL specs. ie, using
> spatial metadata tables/views (geometryColumns for instance).
>
> Spatial layers have to be properly registered into the metadata table to
> be seen by the plugin.
>
>
>
> As you said, DB Query is really amazing to execute direct SQL against
> several spatial DB. That's also why I did not want to support all spatial
> tables: table not registered in the metadata can still be accessed with DB
> Query.
>
>
>
> Concerning Oracle queries:
>
>
>
> To get list of tables: select distinct owner, table_name from
> ALL_SDO_GEOM_METADATA;
>
> Then to get metadata information (table extent for instance, to be able to
> zoom at it), I run:
>
> SELECT dim.* FROM ALL_SDO_GEOM_METADATA usgm, TABLE(usgm.diminfo) dim
>
> WHERE table_name = '<datasetName>' and owner='<datasetName>' and
> column_name = '<attributeName>'
>
> with <...> variables replaced by their actual values.
>
>
>
> I will add some debug log information for the sent queries, that could be
> set with log4j configuration.
>
>
>
> Nicolas
>
>
>
> On 4 December 2015 at 21:07, <edgar.sol...@web.de> wrote:
>
> On 04.12.2015 18:17, Nicolas Ribot wrote:
> > Hi,
> >
> > Yes, this is part of the code I fixed.
> > Right now, I'm fighting with a strange java.lang.ClassNotFoundException:
> oracle.jdbc.driver.OracleDriver when opening my Oracle connection.
> > Driver is there, tried to put it in both lib and lib/ext.
> > If I remove ojdbc.jar, The OracleDataStore correctly detects its missing
> and disables itself, as stated in the log file.
> >
> > When looking at the running classpath, I only see trunk/target/classes
> folder and all jars.
>
> generally i achieved a compilation w/o gt2 and ojdbc via reflection, see
>
> https://sourceforge.net/p/jump-pilot/code/HEAD/tree/core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleValueConverterFactory.java#l50
>
> that means you can compile the code w/o the dependencies as they are
> resolved (or not) during runtime. of course you have to make sure manually
> that
> gt2-oracle-spatial-2.3.2.jar
> and
> ojdbc6.jar
> are within your classpath in your runtime config. in the distro this is
> done via packaging and start script.
>
> hope that helps.. ede
>
>
>
>
>
>
> ------------------------------------------------------------------------------
> Go from Idea to Many App Stores Faster with Intel(R) XDK
> Give your users amazing mobile app experiences with Intel(R) XDK.
> Use one codebase in this all-in-one HTML5 development environment.
> Design, debug & build mobile apps & 2D/3D high-impact games for multiple
> OSs.
> http://pubads.g.doubleclick.net/gampad/clk?id=254741911&iu=/4140
> _______________________________________________
> Jump-pilot-devel mailing list
> Jump-pilot-devel@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
>
------------------------------------------------------------------------------
Go from Idea to Many App Stores Faster with Intel(R) XDK
Give your users amazing mobile app experiences with Intel(R) XDK.
Use one codebase in this all-in-one HTML5 development environment.
Design, debug & build mobile apps & 2D/3D high-impact games for multiple OSs.
http://pubads.g.doubleclick.net/gampad/clk?id=254741911&iu=/4140
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
