[Geoserver-users] Suggestion about improving FeatureType configuration application for Oracle

Jukka_Rahkonen · April 20, 2006, 8:29am

Hello,

I have a few suggestion about improving the FeatureType configuration tool for Oracle feature types. The Oracle DataStore document found from Geoserver documentation is good and by following it even a beginner may be able to create new Oracle data store and Oracle feature types. However, I wonder if it could be possible to improve the configuration tool so that the user could make some basic checks with that instead of using SQL *Plus or something.

For example the following tests might be possible to perform with the configuration tool. However, I do not understand much about Oracle so it is quite possible that those tests cannot be performed in a way I suggest but anyway, here is my list:

- Check if the selected table has geometry field by giving "DESC MY_SPATIAL_TABLE" to Oracle and checking if there exists any field of datatype MDSYS.SDO_GEOMETRY. If not, the user should be informed. By the way, why to show other tables than the spatial ones on the selection list at all?

- Check if the table has primary key. Perhaps it can be done by something like SELECT * FROM ALL_CONSTRAINTS WHERE TABLENAME='MY_SPATIAL_TABLE' AND CONSTRAINT_TYPE='P'
If primary key does not exists the user should be advised to create one.

- Check if there is corresponding entry in ALL_SDO_GEOM_METADATA by SELECT * FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME='MY_SPATIAL_TABLE'
If the entry is missing user could be prompted to create one, perhaps with short instruction about how to do it.

- Check if the table has spatial index: SELECT INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME='MY_SPATIAL_TABLE' AND ITYP_NAME='SPATIAL_INDEX'
If spatial index is missing the user could be advised to create it, perhaps with example SQL:
CREATE INDEX MY_SPATIAL_TABLE_SP_IDX ON MY_SPATIAL_TABLE(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX

Maybe these are the most important things to be checked about the tables. SRID related thing may be useful in the future but not now, because I have understood that at the moment Geoserver does not care about the Oracle SRID at all. Perhaps tests could be run on demand by pressing some "Check database" button. I gues the user might miss rights to perform the queries, and in that case the response might be like "You do not have rights to perform test(s). Please consult your database administrator."

In addition to these tests the behaviour of the "Generate" button for generating the bounding box might be changed a little. I suppose that now it is starting a query that makes full scan to the spatial table to get the max and min coordinates. If the spatial table is big then this query tends to fail, and in any case it takes a long time. Perhaps it could first check how large the table is by SELECT COUNT (*) FROM MY_SPATIAL_TABLE?
Then you might have alternatives. If the table were small, perhaps <100000 records, then the query could be run automatically. If the table is larger the user could be informed "Your spatial table has xxx xxx records. Defining bounding box based on the actual data will take some time or it may fail. Are you sure you want to do it that way?" Then the user may have two other alternatives to select from: either feed the bounding box by hand or use the extents that are stored to SDO.GEOM_METADATA by using query SELECT DIMINFO FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME='MY_SPATIAL_TABLE'

With best regards,

-Jukka Rahkonen-

Bart_van_den_Eijnden · April 20, 2006, 8:39am

Hi Jukka,

first of all thanks for your suggestions.

Please remember it is possible to use non spatial tables from Oracle with
Geoserver, and this can be a very handy feature. I use it for instance to
serve out a DISTINCT view to populate selection lists, but there can be
many more use cases.

Most of your checks only apply to spatial tables, and not all tables used
need to be spatial.

IMHO automating this kind of stuff is a bit overdone, but maybe it would
be good to add some more text to the manage tool stating the requirements.

Best regards,
Bart

Hello,

I have a few suggestion about improving the FeatureType configuration tool
for Oracle feature types. The Oracle DataStore document found from
Geoserver documentation is good and by following it even a beginner may be
able to create new Oracle data store and Oracle feature types. However, I
wonder if it could be possible to improve the configuration tool so that
the user could make some basic checks with that instead of using SQL *Plus
or something.

For example the following tests might be possible to perform with the
configuration tool. However, I do not understand much about Oracle so it
is quite possible that those tests cannot be performed in a way I suggest
but anyway, here is my list:

- Check if the selected table has geometry field by giving "DESC
MY_SPATIAL_TABLE" to Oracle and checking if there exists any field of
datatype MDSYS.SDO_GEOMETRY. If not, the user should be informed. By the
way, why to show other tables than the spatial ones on the selection list
at all?

- Check if the table has primary key. Perhaps it can be done by something
like SELECT * FROM ALL_CONSTRAINTS WHERE TABLENAME='MY_SPATIAL_TABLE' AND
CONSTRAINT_TYPE='P'
If primary key does not exists the user should be advised to create one.

- Check if there is corresponding entry in ALL_SDO_GEOM_METADATA by SELECT
* FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME='MY_SPATIAL_TABLE'
If the entry is missing user could be prompted to create one, perhaps with
short instruction about how to do it.

- Check if the table has spatial index: SELECT INDEX_NAME FROM ALL_INDEXES
WHERE TABLE_NAME='MY_SPATIAL_TABLE' AND ITYP_NAME='SPATIAL_INDEX'
If spatial index is missing the user could be advised to create it,
perhaps with example SQL:
CREATE INDEX MY_SPATIAL_TABLE_SP_IDX ON MY_SPATIAL_TABLE(GEOM) INDEXTYPE
IS MDSYS.SPATIAL_INDEX

Maybe these are the most important things to be checked about the tables.
SRID related thing may be useful in the future but not now, because I have
understood that at the moment Geoserver does not care about the Oracle
SRID at all. Perhaps tests could be run on demand by pressing some "Check
database" button. I gues the user might miss rights to perform the
queries, and in that case the response might be like "You do not have
rights to perform test(s). Please consult your database administrator."

In addition to these tests the behaviour of the "Generate" button for
generating the bounding box might be changed a little. I suppose that now
it is starting a query that makes full scan to the spatial table to get
the max and min coordinates. If the spatial table is big then this query
tends to fail, and in any case it takes a long time. Perhaps it could
first check how large the table is by SELECT COUNT (*) FROM
MY_SPATIAL_TABLE?
Then you might have alternatives. If the table were small, perhaps <100000
records, then the query could be run automatically. If the table is larger
the user could be informed "Your spatial table has xxx xxx records.
Defining bounding box based on the actual data will take some time or it
may fail. Are you sure you want to do it that way?" Then the user may have
two other alternatives to select from: either feed the bounding box by
hand or use the extents that are stored to SDO.GEOM_METADATA by using
query SELECT DIMINFO FROM ALL_SDO_GEOM_METADATA WHERE
TABLE_NAME='MY_SPATIAL_TABLE'

With best regards,

-Jukka Rahkonen-

-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job
easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Jukka_Rahkonen · April 20, 2006, 9:57am

Hi Bart,

I understand now the non spatial tables, I haven't just been thinking of those yet. I will for sure have some use for them later once I have learned enough about the spatial ones. You may be right with the automating as well. Some advice from the management tool might have guided me to my first success with Oracle couple of days earlier, but now once I know better what to do creating new feature types is a very fast process. On the other hand, it was just the automation that the configuration application offered that lead me to use Geoserver. Before that I was totally lost when trying to use my own data through deegree-WFS.

Regards,

-Jukka-