[Geoserver-users] Getting started with Oracle

Mark_Hammond · November 25, 2011, 10:06am

I’ve set up the Oracle Connector plugin and correctly configured it.

I have published two tables that have spatial fields in them.

When previewing the first layer, I just get a blank map

When previewing the second layer, I get a blank map with an error: “code=”internalError” Rendering process failed / by zero

Any ideas what’s going on?

Details of first layer:

Coordinate Reference Systems

Native SRS

UNKNOWN Longitude / Latitude (WGS 84)…

Declared SRS

EPSG:900913 EPSG:WGS84 / Google Mercator…

SRS handling

Force Declared

Bounding Boxes

Native Bounding Box

Min X -11.112

Min Y 48.744

Max X 2.513

Max Y 61.581

Lat/Lon Bounding Box

Min X -12.15

Min Y 47.85

Max X 3.15

Max Y 63.15

Feature Type Details

Property Type Nillable Min/Max Occurences

GRIDREF String false 1/1

SYSTEM String false 1/1

PARENT String true 0/1

TYPE BigDecimal false 1/1

BOUNDARY Geometry true 0/1

Details of second layer:

Coordinate Reference Systems

Native SRS

UNKNOWN Longitude / Latitude (WGS 84)…

Declared SRS

EPSG:900913 EPSG:WGS84 / Google Mercator…

SRS handling

Force Declared

Bounding Boxes

Native Bounding Box

Min X -33.407

Min Y 25.682

Max X 45.343

Max Y 71.032

Lat/Lon Bounding Box

Min X -33

Min Y 25

Max X 46

Max Y 72

Feature Type Details

Property Type Nillable Min/Max Occurences

REGION_CODE String false 1/1

REGION_NAME String false 1/1

ADDED_DATE Date false 1/1

LAST_EDITED Date false 1/1

BOUNDARY Geometry true 0/1

LAST_EDIT_BY String true 0/1

EDITABLE String false 1/1

Error from error log:

2011-11-25 09:54:24,678 ERROR [geoserver.ows] -

org.geoserver.platform.ServiceException: Rendering process failed

at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:491)

at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:228)

at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:122)

at org.geoserver.wms.GetMap.run(GetMap.java:377)

at org.geoserver.wms.GetMap.run(GetMap.java:107)

at org.geoserver.wms.DefaultWebMapService.getMap(DefaultWebMapService.java:352)

at sun.reflect.GeneratedMethodAccessor1340.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)

at org.geoserver.gwc.wms.CacheSeedingWebMapService.invoke(CacheSeedingWebMapService.java:60)

at org.geoserver.gwc.wms.CacheSeedingWebMapService.invoke(CacheSeedingWebMapService.java:34)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

at org.geoserver.gwc.wms.CachingWebMapService.invoke(CachingWebMapService.java:56)

at org.geoserver.gwc.wms.CachingWebMapService.invoke(CachingWebMapService.java:38)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

at org.geoserver.ows.util.RequestObjectLogger.invoke(RequestObjectLogger.java:51)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)

at $Proxy363.getMap(Unknown Source)

at sun.reflect.GeneratedMethodAccessor1339.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:630)

at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:234)

at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)

at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:735)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)

at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1534)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:23)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:74)

at org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:45)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:49)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:394)

at org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)

at org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)

at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:406)

at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHttp(ExceptionTranslationFilter.java:101)

at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)

at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:406)

at org.springframework.security.providers.anonymous.AnonymousProcessingFilter.doFilterHttp(AnonymousProcessingFilter.java:105)

at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)

at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:406)

at org.springframework.security.ui.basicauth.BasicProcessingFilter.doFilterHttp(BasicProcessingFilter.java:174)

at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)

at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:406)

at org.springframework.security.context.HttpSessionContextIntegrationFilter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235)

at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)

at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:406)

at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:185)

at org.springframework.security.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:99)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:71)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:183)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:41)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:46)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:215)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)

at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)

at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:98)

at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:91)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:162)

at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:326)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:227)

at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:170)

at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:822)

at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:719)

at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1013)

at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:225)

at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)

at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)

at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)

at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)

at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)

at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)

at com.sun.grizzly.ContextTask.run(ContextTask.java:71)

at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)

at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)

at java.lang.Thread.run(Thread.java:662)

Caused by: java.lang.ArithmeticException: / by zero

at org.geotools.data.oracle.sdo.SDO.coordinates(SDO.java:2075)

at org.geotools.data.oracle.sdo.SDO.create(SDO.java:2320)

at org.geotools.data.oracle.sdo.GeometryConverter.asGeometry(GeometryConverter.java:124)

at org.geotools.data.oracle.OracleDialect.readGeometry(OracleDialect.java:438)

at org.geotools.data.oracle.OracleDialect.readGeometry(OracleDialect.java:424)

at org.geotools.data.oracle.OracleDialect.decodeGeometryValue(OracleDialect.java:387)

at org.geotools.jdbc.JDBCFeatureReader.next(JDBCFeatureReader.java:321)

at org.geotools.jdbc.JDBCFeatureReader.next(JDBCFeatureReader.java:85)

at org.geotools.data.store.ContentFeatureCollection$WrappingFeatureIterator.next(ContentFeatureCollection.java:190)

at org.geotools.data.store.ContentFeatureCollection$WrappingFeatureIterator.next(ContentFeatureCollection.java:170)

at org.geotools.data.crs.ForceCoordinateSystemIterator.next(ForceCoordinateSystemIterator.java:125)

at org.geotools.data.crs.ForceCoordinateSystemIterator.next(ForceCoordinateSystemIterator.java:66)

at org.geotools.renderer.lite.StreamingRenderer.drawPlain(StreamingRenderer.java:2019)

at org.geotools.renderer.lite.StreamingRenderer.processStylers(StreamingRenderer.java:1940)

at org.geotools.renderer.lite.StreamingRenderer.paint(StreamingRenderer.java:772)

at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:465)

… 106 more

2011-11-25 09:54:29,135 ERROR [geotools.rendering] - / by zero

java.lang.ArithmeticException: / by zero

at org.geotools.data.oracle.sdo.SDO.coordinates(SDO.java:2075)

at org.geotools.data.oracle.sdo.SDO.create(SDO.java:2320)

at org.geotools.data.oracle.sdo.GeometryConverter.asGeometry(GeometryConverter.java:124)

at org.geotools.data.oracle.OracleDialect.readGeometry(OracleDialect.java:438)

at org.geotools.data.oracle.OracleDialect.readGeometry(OracleDialect.java:424)

at org.geotools.data.oracle.OracleDialect.decodeGeometryValue(OracleDialect.java:387)

at org.geotools.jdbc.JDBCFeatureReader.next(JDBCFeatureReader.java:321)

at org.geotools.jdbc.JDBCFeatureReader.next(JDBCFeatureReader.java:85)

at org.geotools.data.store.ContentFeatureCollection$WrappingFeatureIterator.next(ContentFeatureCollection.java:190)

ianturton · November 25, 2011, 10:28am

On 25 November 2011 10:06, Mark Hammond <mark.hammond@anonymised.com> wrote:

I’ve set up the Oracle Connector plugin and correctly configured it.

I have published two tables that have spatial fields in them.

Looks like your data is in 4326 not 909913.

Ian

Mark_Hammond · November 29, 2011, 9:35am

Thanks for the heads up.
Have republished the tables using 4326, and while it correctly calculated the bounds this time, the results on preview layer were the same - blank map for one table, and the divide by zero error with the other.

Both these tables' spatial fields can be previewed OK using the SQL Developer Map view.

M

-----Original Message-----
From: Ian Turton [mailto:ijturton@anonymised.com]
Sent: 25 November 2011 10:28
To: Mark Hammond
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Getting started with Oracle

On 25 November 2011 10:06, Mark Hammond <mark.hammond@anonymised.com> wrote:

I’ve set up the Oracle Connector plugin and correctly configured it.

I have published two tables that have spatial fields in them.

Looks like your data is in 4326 not 909913.

Ian

Andrea_Aime4 · November 29, 2011, 1:30pm

On Tue, Nov 29, 2011 at 10:35 AM, Mark Hammond <mark.hammond@anonymised.com> wrote:

Thanks for the heads up.
Have republished the tables using 4326, and while it correctly calculated the bounds this time, the results on preview layer were the same - blank map for one table, and the divide by zero error with the other.

Both these tables’ spatial fields can be previewed OK using the SQL Developer Map view.

A division by zero is likely to indicate corrupted data on your end. The SQL Developer Map viewer is
probably more tolerant to it.
I believe Oracle has functions that allow to locate and fix corrupt gometries.

Cheers
Andrea

–

Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

Mark_Hammond · November 30, 2011, 11:15am

Wasn’t able to find this tool you mention, so have asked on the Oracle forum about it.

While I was there, I did find an article discussing the differences between SRID 8307 (traditional used by Oracle) and SRID 4326. They are supposed to be equivalent, but could this be a source of the trouble?

https://forums.oracle.com/forums/thread.jspa?threadID=2302685&tstart=0

Mark

From: andrea.aime@anonymised.com… [mailto:andrea.aime@anonymised.com] On Behalf Of Andrea Aime
Sent: 29 November 2011 13:30
To: Mark Hammond
Cc: Ian Turton; geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Getting started with Oracle

On Tue, Nov 29, 2011 at 10:35 AM, Mark Hammond <mark.hammond@anonymised.com> wrote:

Thanks for the heads up.
Have republished the tables using 4326, and while it correctly calculated the bounds this time, the results on preview layer were the same - blank map for one table, and the divide by zero error with the other.

Both these tables’ spatial fields can be previewed OK using the SQL Developer Map view.

A division by zero is likely to indicate corrupted data on your end. The SQL Developer Map viewer is

probably more tolerant to it.

I believe Oracle has functions that allow to locate and fix corrupt gometries.

Cheers

Andrea

–

Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

Andrea_Aime4 · November 30, 2011, 1:19pm

On Wed, Nov 30, 2011 at 12:15 PM, Mark Hammond <mark.hammond@anonymised.com> wrote:

Wasn’t able to find this tool you mention, so have asked on the Oracle forum
about it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_util.htm#BJEICGGA
http://docs.oracle.com/html/A88805_01/sdo_objg.htm

While I was there, I did find an article discussing the differences between
SRID 8307 (traditional used by Oracle) and SRID 4326. They are supposed to
be equivalent, but could this be a source of the trouble?

It should not.

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

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

Mark_Hammond · December 1, 2011, 9:21am

Thanks for your help.

With the clues you offered, I was able to pinpoint the problem down to the
original Oracle tool that created populated the database table from a shape
file. This had created spatial objects of an older version than current and
neither the oracle SDO_UTIL.RECTIFY_GEOMETRY nor GeoServer could do anything
with them.

For anyone else attempting this, run the SDO_MIGRATE.TO_CURRENT function on
your loaded spatial data to bring it up to date, then you can use a
combination of SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function to check for
problems, and SDO_UTIL.RECTIFY_GEOMETRY to fix them.

Now onto the second part of my original question:
I'd like to be able to pull back features onto my map from GeoServer based
on an SQL (or SQL-like) query. The trouble is there are two or three tables
that I want to include in the query of which only one of them contains the
spatial data. Can such a thing be achieved with GeoServer, or am I going to
need create a list of features direct from Oracle first, and then query
GeoServer with that list (could be quite a long list, so I'd like to avoid
that if possible). Are there any other solutions to this kind of problem?

-----Original Message-----
From: andrea.aime@anonymised.com [mailto:andrea.aime@anonymised.com] On Behalf Of
Andrea Aime
Sent: 30 November 2011 13:19
To: Mark Hammond
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Getting started with Oracle

On Wed, Nov 30, 2011 at 12:15 PM, Mark Hammond <mark.hammond@anonymised.com> wrote:

Wasnt able to find this tool you mention, so have asked on the Oracle
forum about it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_util.htm#BJEICGGA
http://docs.oracle.com/html/A88805_01/sdo_objg.htm

While I was there, I did find an article discussing the differences
between SRID 8307 (traditional used by Oracle) and SRID 4326. They are
supposed to be equivalent, but could this be a source of the trouble?

It should not.

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

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

Jukka_Rahkonen · December 1, 2011, 9:48am

Hi,

Perhaps you can handle the situation by creating a view into Oracle and publishing it with Geoserver. It is similar process than publishing tables. Remember to add a line into geometry columns table and often it is also good to make a primary key for the view. That is possible with Oracle. Primary key columns are used for generating fids. With standard settings the PK columns are not included as attributes. If you need them there are a few alternatives. Myself I am doing it by picking those attributes two times with different names into a view. For example if fids in the view must contain IDs from table_1 and table_2 to be unique you can do something like

create or replase view geoserver_view as
(select first.ID PK_1, first.ID ID_1, second.ID PK_2, second.ID ID_2
...
from first_table first, second_table second);

Then create primary key for geoserver_view as PK_1 and PK_2. Fids will contain both PK_1 and PK_2 and in addition the original table IDs are published in WFS as ID_1 and ID_2 attributes.

-Jukka Rahkonen-

Mark Hammond wrote:

Thanks for your help.

With the clues you offered, I was able to pinpoint the
problem down to the
original Oracle tool that created populated the database
table from a shape
file. This had created spatial objects of an older version
than current and
neither the oracle SDO_UTIL.RECTIFY_GEOMETRY nor GeoServer
could do anything
with them.

For anyone else attempting this, run the
SDO_MIGRATE.TO_CURRENT function on
your loaded spatial data to bring it up to date, then you can use a
combination of SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT
function to check for
problems, and SDO_UTIL.RECTIFY_GEOMETRY to fix them.

Now onto the second part of my original question:
I'd like to be able to pull back features onto my map from
GeoServer based
on an SQL (or SQL-like) query. The trouble is there are two
or three tables
that I want to include in the query of which only one of them
contains the
spatial data. Can such a thing be achieved with GeoServer, or
am I going to
need create a list of features direct from Oracle first, and
then query
GeoServer with that list (could be quite a long list, so I'd
like to avoid
that if possible). Are there any other solutions to this kind
of problem?

-----Original Message-----
From: andrea.aime@anonymised.com [mailto:andrea.aime@anonymised.com] On
Behalf Of
Andrea Aime
Sent: 30 November 2011 13:19
To: Mark Hammond
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Getting started with Oracle

On Wed, Nov 30, 2011 at 12:15 PM, Mark Hammond
<mark.hammond@anonymised.com> wrote:
> Wasn't able to find this tool you mention, so have asked on
the Oracle
> forum about it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_util

.htm#BJEICGGA

http://docs.oracle.com/html/A88805_01/sdo_objg.htm

>
> While I was there, I did find an article discussing the differences
> between SRID 8307 (traditional used by Oracle) and SRID
4326. They are
> supposed to be equivalent, but could this be a source of
the trouble?

It should not.

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

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

--------------------------------------------------------------
----------------
All the data continuously generated in your IT infrastructure
contains a definitive record of customers, application performance,
security threats, fraudulent activity, and more. Splunk takes this
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Mark_Hammond · December 1, 2011, 5:35pm

Rahkonen,

This is the approach I thought may be necessary. Apparently a carefully
managed materialised view can minimise the amount of work Oracle has to do
to maintain a view, since the spatial data itself won't be changing.

The only trouble at the moment is when creating the view: The query involves
a spatial join which just locks up the server and never completes (I left it
8 hours just to make sure it wasn't just a slow query)

I've posted some details on that problem to the Oracle forum in the hope of
some sensible advice.

-----Original Message-----
From: Rahkonen Jukka [mailto:Jukka.Rahkonen@anonymised.com]
Sent: 01 December 2011 09:48
To: 'Mark Hammond'; 'geoserver-users@lists.sourceforge.net'
Subject: Re: [Geoserver-users] Getting started with Oracle

Hi,

Perhaps you can handle the situation by creating a view into Oracle and
publishing it with Geoserver. It is similar process than publishing tables.
Remember to add a line into geometry columns table and often it is also good
to make a primary key for the view. That is possible with Oracle. Primary
key columns are used for generating fids. With standard settings the PK
columns are not included as attributes. If you need them there are a few
alternatives. Myself I am doing it by picking those attributes two times
with different names into a view. For example if fids in the view must
contain IDs from table_1 and table_2 to be unique you can do something like

create or replase view geoserver_view as (select first.ID PK_1, first.ID
ID_1, second.ID PK_2, second.ID ID_2 ...
from first_table first, second_table second);

Then create primary key for geoserver_view as PK_1 and PK_2. Fids will
contain both PK_1 and PK_2 and in addition the original table IDs are
published in WFS as ID_1 and ID_2 attributes.

-Jukka Rahkonen-

Mark Hammond wrote:

Thanks for your help.

With the clues you offered, I was able to pinpoint the problem down to
the original Oracle tool that created populated the database table
from a shape file. This had created spatial objects of an older
version than current and neither the oracle SDO_UTIL.RECTIFY_GEOMETRY
nor GeoServer could do anything with them.

For anyone else attempting this, run the SDO_MIGRATE.TO_CURRENT
function on your loaded spatial data to bring it up to date, then you
can use a combination of SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT
function to check for
problems, and SDO_UTIL.RECTIFY_GEOMETRY to fix them.

Now onto the second part of my original question:
I'd like to be able to pull back features onto my map from GeoServer
based on an SQL (or SQL-like) query. The trouble is there are two or
three tables that I want to include in the query of which only one of
them contains the spatial data. Can such a thing be achieved with
GeoServer, or am I going to need create a list of features direct from
Oracle first, and then query GeoServer with that list (could be quite
a long list, so I'd like to avoid that if possible). Are there any
other solutions to this kind of problem?

-----Original Message-----
From: andrea.aime@anonymised.com [mailto:andrea.aime@anonymised.com] On Behalf
Of Andrea Aime
Sent: 30 November 2011 13:19
To: Mark Hammond
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Getting started with Oracle

On Wed, Nov 30, 2011 at 12:15 PM, Mark Hammond <mark.hammond@anonymised.com>
wrote:
> Wasn't able to find this tool you mention, so have asked on
the Oracle
> forum about it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_util

.htm#BJEICGGA

http://docs.oracle.com/html/A88805_01/sdo_objg.htm

>
> While I was there, I did find an article discussing the differences
> between SRID 8307 (traditional used by Oracle) and SRID
4326. They are
> supposed to be equivalent, but could this be a source of
the trouble?

It should not.

Cheers
Andrea

--
-------------------------------------------------------
Ing. Andrea Aime
GeoSolutions S.A.S.
Tech lead

Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584 962313
fax: +39 0584 962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.youtube.com/user/GeoSolutionsIT
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

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

--------------------------------------------------------------
----------------
All the data continuously generated in your IT infrastructure contains
a definitive record of customers, application performance, security
threats, fraudulent activity, and more. Splunk takes this data and
makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users