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

[Rahkonen J. <Juk...@mm...>]

Hello,
=20
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.
=20
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:
=20
- 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?
=20
- Check if the table has primary key. Perhaps it can be done by =
something like SELECT * FROM ALL_CONSTRAINTS WHERE =
TABLENAME=3D'MY_SPATIAL_TABLE' AND CONSTRAINT_TYPE=3D'P'
If primary key does not exists the user should be advised to create one.
=20
- Check if there is corresponding entry in ALL_SDO_GEOM_METADATA by =
SELECT * FROM ALL_SDO_GEOM_METADATA WHERE =
TABLE_NAME=3D'MY_SPATIAL_TABLE'
If the entry is missing user could be prompted to create one, perhaps =
with short instruction about how to do it.
=20
- Check if the table has spatial index: SELECT INDEX_NAME FROM =
ALL_INDEXES WHERE TABLE_NAME=3D'MY_SPATIAL_TABLE' AND =
ITYP_NAME=3D'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."
=20
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=3D'MY_SPATIAL_TABLE'
=20
With best regards,
=20
-Jukka Rahkonen-