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.rahko...@mmmtike.fi] 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.a...@gmail.com [mailto:andrea.a...@gmail.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.hamm...@bto.org> > 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 > ------------------------------------------------------------------------------ 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
