[Geoserver-users] Oracle Data in Geoserver

GeoServer GeoServer User
1

Am a little bit in a bind. I have loaded the shape files in an SQL database
and am able to make in work with Geoserver with no issues under Layer
Preview. The problem arises when I have loaded the exact same shape files
into an Oracle database. Nothing seems to load and it comes up with a
rendering time out message. I increased the time frame to 90s, 120s and
240s and nothing seems to work. Do note that with the same default
settings, everything load OK with an SQL Server database.

Any input or direction is greatly appreciated!

Using Geoserver 2.0.3, SQL Server 2008 and Oracle 11gR2

Thanks in advance

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Oracle-Data-in-Geoserver-tp5089732.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

2

Hi,

sounds odd, very odd. I guess you have the gt-jdbc-oraclexxx.jar and the
ojdbc14.jar in the lib directories. I further assume the tables are
indexed, including a spatial index and analysed.

I would do two things first before doing anything with Geoserver. Download
QGis and try to display it with QGis or use SQL developer and try to do a
spatial query on the dataset. Only after assuring that the tables are o.k. I
would look into Geoserver.

And you are sure there is nothing in the log files, the datastore is o.k.
and the preview works o.k., i.e. displays all controls but a white canvas?

What happens with a direct WMS request?

Cheers

Christian

-----
____________________________

Dr Christian Maul
Project Manager

Information Services Branch
Department Environment and Primary Industries
Level13, Marland House, 570 Bourke Street
Melbourne 3000

PO Box 500, East Melbourne Vic 3002

Telephone: +61-3-8636 2325
Telefax: +61-3-8636 2813
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Oracle-Data-in-Geoserver-tp5089732p5089763.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

3

Thanks for the reply.

I have figured out the problem. It had to do with the creation of an entry
in the ORACLE USER_SDO_GEOM_METADATA table in order to create the Spatial
index.

I originally set it to the ff (copied this off the web as I was not able to
create spatial indexes without it):

INSERT INTO USER_SDO_GEOM_METADATA
  VALUES (
  'Geoserver_Cem000',
  'the_geom',
  MDSYS.SDO_DIM_ARRAY( -- 20X20 grid, virtually zero tolerance
    MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
     ),
  4326 -- SRID
);

and have changed it to

INSERT INTO USER_SDO_GEOM_METADATA
  VALUES (
  'GEOSERVER_CEMDATA',
  'THE_GEOM',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.005),
    MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.005)
     ),
  4326 -- SRID
);

I am very new to Oracle spatial and am learning as I go. I am not even sure
why the difference but it worked.

Thank you for the reply.

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Oracle-Data-in-Geoserver-tp5089732p5089877.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

4

On Tue, Nov 19, 2013 at 2:09 PM, rkliao <rkliao@anonymised.com>wrote:

Thanks for the reply.

I have figured out the problem. It had to do with the creation of an entry
in the ORACLE USER_SDO_GEOM_METADATA table in order to create the Spatial
index.

I originally set it to the ff (copied this off the web as I was not able to
create spatial indexes without it):

INSERT INTO USER_SDO_GEOM_METADATA
  VALUES (
  'Geoserver_Cem000',
  'the_geom',
  MDSYS.SDO_DIM_ARRAY( -- 20X20 grid, virtually zero tolerance
    MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
     ),
  4326 -- SRID
);

and have changed it to

INSERT INTO USER_SDO_GEOM_METADATA
  VALUES (
  'GEOSERVER_CEMDATA',
  'THE_GEOM',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.005),
    MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.005)
     ),
  4326 -- SRID
);

I am very new to Oracle spatial and am learning as I go. I am not even
sure
why the difference but it worked.

The issue is that Oracle cannot run the spatial filters used by GeoServer
(SDO_FILTER)
without a spatial index

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

5

Unquoted Oracle identifiers are converted to uppercase; this may be significant in USER_SDO_GEOM_METADATA values, as lowercase string values (table and column names) will not match.

Kind regards,
Ben.

On 19/11/13 21:09, rkliao wrote:

Thanks for the reply.

I have figured out the problem. It had to do with the creation of an entry
in the ORACLE USER_SDO_GEOM_METADATA table in order to create the Spatial
index.

I originally set it to the ff (copied this off the web as I was not able to
create spatial indexes without it):

INSERT INTO USER_SDO_GEOM_METADATA
   VALUES (
   'Geoserver_Cem000',
   'the_geom',
   MDSYS.SDO_DIM_ARRAY( -- 20X20 grid, virtually zero tolerance
     MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
     MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
      ),
   4326 -- SRID
);

and have changed it to

INSERT INTO USER_SDO_GEOM_METADATA
   VALUES (
   'GEOSERVER_CEMDATA',
   'THE_GEOM',
   MDSYS.SDO_DIM_ARRAY(
     MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.005),
     MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.005)
      ),
   4326 -- SRID
);

I am very new to Oracle spatial and am learning as I go. I am not even sure
why the difference but it worked.

Thank you for the reply.

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Oracle-Data-in-Geoserver-tp5089732p5089877.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Shape the Mobile Experience: Free Subscription
Software experts and developers: Be at the forefront of tech innovation.
Intel(R) Software Adrenaline delivers strategic insight and game-changing
conversations that shape the rapidly evolving mobile landscape. Sign up now.
http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

6

I had to do this again with another set of shapes and is ending up with
Geoserver timing out even if I had created the indexes. Is there something
else I might be missing?

I create the metadata and indexes as follows:

INSERT INTO USER_SDO_GEOM_METADATA
  VALUES (
  'GEOSERVER_CEMDATA',
  'THE_GEOM',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.005)
     ),
  26910 -- SRID
);

CREATE INDEX GEOSERVER_CEMDATA_idx ON GEOSERVER_CEMDATA (the_geom) INDEXTYPE
IS MDSYS.SPATIAL_INDEX;

The only different is the SRID is now 26910 instead of 4326.

Thanks.

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Oracle-Data-in-Geoserver-tp5089732p5132967.html
Sent from the GeoServer - User mailing list archive at Nabble.com.