Create FeatureType on Oracle not picking up certain column types, including ST_GEOMETRY

[FrankMcLean]

Hi,

After a bit of fiddling, I've managed to get a connection to our Oracle DB setup.  The only trouble is that when I use the Create New FeatureType option, it does not pick up all of the fields.  Looking at the log, I see 'Unknown SQL Type: NVARCHAR2' and 'Unknown SQL Type: ST_GEOMETRY' - the latter is obviously the most annoying.  And slightly baffling, as I thought this was the standard name.

I'm using the thin Oracle client on port 1521, with the ojdbc14.jar.  I've tried going to the Confluence page mentioned in the Oracle plugin zip, but this doesn't appear to be current.

Can someone point a newbie in the right direction?  I think I'm pretty close.

Thanks in advance,

Frank

[Bart van den Eijnden]

AFAIK ST_GEOMETRY is the ESRI geometry type for Oracle, so it's not
strange that Geoserver does not know this type. Maybe if you connect as
ArcSDE it might work.

The standard in Oracle Spatial is SDO_GEOMETRY.

Best regards,
Bart

FrankMcLean wrote:

> Hi,
>
> After a bit of fiddling, I've managed to get a connection to our Oracle DB
> setup.  The only trouble is that when I use the Create New FeatureType
> option, it does not pick up all of the fields.  Looking at the log, I see
> 'Unknown SQL Type: NVARCHAR2' and 'Unknown SQL Type: ST_GEOMETRY' - the
> latter is obviously the most annoying.  And slightly baffling, as I thought
> this was the standard name.
>
> I'm using the thin Oracle client on port 1521, with the ojdbc14.jar.  I've
> tried going to the Confluence page mentioned in the Oracle plugin zip, but
> this doesn't appear to be current.
>
> Can someone point a newbie in the right direction?  I think I'm pretty
> close.
>
> Thanks in advance,
>
> Frank
>  

--
Bart van den Eijnden
OSGIS, Open Source GIS
bartvde@...
http://www.osgis.nl


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@...
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[FrankMcLean]

Hi Bart,

Thanks for the reply.  The ST_GEOMETRY we're using is 'qualified' (?) as SDE.ST_GEOMETRY, though I note that there is also an MDSYS.ST_GEOMETRY type, which I suspect is the Oracle standard - from poking around in the defs, if looks like a wrapper for SDO_GEOMETRY.

From what you say, it sounds like the Oracle option in GeoServer should be read as 'Use Oracle spatial types' rather than just 'connect to Oracle', as I was reading it.  It expects Oracle to be using the SDO_GEOMETRY type, rather than the SQL/MM standard, which makes sense.  Is this all ultimately a GeoTOOLS issue?

Of course, that doesn't explain the NVARCHAR2 issue, but it falls within the same area, I guess.

Thanks, Bart.

Frank

Bart van den Eijnden wrote:

AFAIK ST_GEOMETRY is the ESRI geometry type for Oracle, so it's not
strange that Geoserver does not know this type. Maybe if you connect as
ArcSDE it might work.

The standard in Oracle Spatial is SDO_GEOMETRY.

Best regards,
Bart

FrankMcLean wrote:
> Hi,
>
> After a bit of fiddling, I've managed to get a connection to our Oracle DB
> setup.  The only trouble is that when I use the Create New FeatureType
> option, it does not pick up all of the fields.  Looking at the log, I see
> 'Unknown SQL Type: NVARCHAR2' and 'Unknown SQL Type: ST_GEOMETRY' - the
> latter is obviously the most annoying.  And slightly baffling, as I thought
> this was the standard name.
>
> I'm using the thin Oracle client on port 1521, with the ojdbc14.jar.  I've
> tried going to the Confluence page mentioned in the Oracle plugin zip, but
> this doesn't appear to be current.
>
> Can someone point a newbie in the right direction?  I think I'm pretty
> close.
>
> Thanks in advance,
>
> Frank
>  


--
Bart van den Eijnden
OSGIS, Open Source GIS
[e-mail]
http://www.osgis.nl


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-users mailing list
[e-mail]
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[aaime]

FrankMcLean ha scritto:

> Hi Bart,
>
> Thanks for the reply.  The ST_GEOMETRY we're using is 'qualified' (?) as
> SDE.ST_GEOMETRY, though I note that there is also an MDSYS.ST_GEOMETRY type,
> which I suspect is the Oracle standard - from poking around in the defs, if
> looks like a wrapper for SDO_GEOMETRY.
>
>>From what you say, it sounds like the Oracle option in GeoServer should be
> read as 'Use Oracle spatial types' rather than just 'connect to Oracle', as
> I was reading it.  It expects Oracle to be using the SDO_GEOMETRY type,
> rather than the SQL/MM standard, which makes sense.  Is this all ultimately
> a GeoTOOLS issue?
>
> Of course, that doesn't explain the NVARCHAR2 issue, but it falls within the
> same area, I guess.

Yep, both are GeoTools issues. The Oracle datastore code can only read
SDO_GEOMETRY and will only recognize that, to add support for a
different geometry encoding quite a big of code has to be created,
ranging from pure reading/writing to the way spatial filter functions
have to be encoded.

Supporting NVARCHAR2 is similar (some code needs to be written), but
seems a lot easier, provided it's ok to load all of the NVARCHAR2
attribute value in memory (I actually don't know if that type is a
very large char or just an odd variant of VARCHAR).

Cheers
Andrea

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@...
https://lists.sourceforge.net/lists/listinfo/geoserver-users