Hi Florence,
I am following your idea by using VIEW. Actually this will be useful in later
time.
Firstly, I re-create a small data table,
DROP TABLE SCOTT.BOREHOLE4 CASCADE CONSTRAINTS;
CREATE TABLE SCOTT.BOREHOLE4
(
SHAPENR NUMBER(5) NOT NULL,
BHOLE_NO VARCHAR2(254 BYTE),
LENGTH NUMBER(19,11),
ANGLE NUMBER(19,11),
DRILL_METHOD VARCHAR2(20 BYTE),
ELEVATION NUMBER(7,3),
X NUMBER,
Y NUMBER
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Then, insert data
1 002-11 132.6 45 unknown 137.6 178600 150650
2 002-2 103.6 45 unknown 125.6 178600 150700
3 002-3 79.9 45 unknown 184.5 178150 150950
4 006-01 504.4 90 unknown 168.2 181200 209800
5 014-02 162.5 90 unknown 146.5 146700 180100
6 014-03 115.5 90 unknown 178.5 142920 181680
7 014-04 89.6 90 unknown 129.5 143050 181700
8 014-06 78 90 unknown 158.3 143600 180120
9 014-07 258.2 90 unknown 135.2 141880 176400
10 014-08 145.5 90 unknown 167.3 140400 175900
Then I create a view
create view scott.viewBorehole4 as
select B.SHAPENR,B.BHOLE_NO,B.LENGTH,B.ANGLE,B.DRILL_METHOD,B.ELEVATION,
SDO_GEOMETRY(2001,7405,SDO_POINT_TYPE(X, Y, NULL), NULL, NULL) SSHAPE,
SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(X, Y,
X, Y)) GEOM_SHAPE
from SCOTT.BOREHOLE4 b;
Lastly I add one record the metadata table
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
'VIEWBOREHOLE4',
'GEOM_SHAPE',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('longitude', 0, 1000000, 0.000001),
MDSYS.SDO_DIM_ELEMENT('latitude', 0, 1000000, 0.000001)
),
7405);
I don't change the mapping file, and the result is the same.
Thank you very much that you can try my case.
I have one extra questions:
In USER_SDO_GEOM_METADATA table, should I use VIEWBOREHOLE4 or
SCOTT.VIEWBOREHOLE4????
Best wishes,
Ying
> From: [email protected]
> To: [email protected]
> Date: Thu, 28 Oct 2010 17:08:32 +0800
> Subject: Re: [Geoserver-users] [ExternalEmail] Re: About gsml:Borehole again
>
> Hi Ying,
>
> What Victor mentioned here is with the case if you have spatial index.
>
> Hmm.. I never create table with spatial column... I'd used your script to
> create the table and insert a test record, update the spatial column with
> your update sql. Strange enough, both the geometry column doesn't get
> encoded at all... I don't have an answer of whether what you did is right or
> wrong. Will try again when I have more time.
>
> Why not try to create a table without geometry column and create a view from
> that table with extra geometry column base on the existing columns. E.g.
>
> CREATE VIEW "TESTVIEW" AS
> SELECT ....,
> ....,
> ....,
> SDO_GEOMETRY(2001,7405,SDO_POINT_TYPE(X, Y, NULL), NULL, NULL) SSHAPE,
> SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(X,
> Y, X, Y)) GEOM_SHAPE
> FROM BOREHOLE3
>
> Try that and see what's the result.
>
>
>
> -----Original Message-----
> From: v...@csiro [mailto:[email protected]]
> Sent: Thursday, 28 October 2010 4:15 PM
> To: [email protected]
> Subject: [ExternalEmail] Re: [Geoserver-users] About gsml:Borehole again
>
>
> Hi Ying,
>
> Not sure if this is resolved :) but you will need to run the index again :)
>
>
>
>
> yw wrote:
> >
> >
> > Hi Florence,
> >
> > Good to hear you again. Thank you very much for your help again.
> >
> > What I have done is as following,
> >
> > 1. Data
> >
> > In Oracle, I have one table under SCOTT schema
> >
> > CREATE TABLE SCOTT.BOREHOLE3
> > (
> > SHAPENR NUMBER(5) NOT NULL,
> > X NUMBER,
> > Y NUMBER,
> > MEASURE NUMBER,
> > BHOLE_NO VARCHAR2(254 BYTE),
> > COUNTY VARCHAR2(254 BYTE),
> > COMP_BHOLE VARCHAR2(254 BYTE),
> > ANGLE NUMBER(19,11),
> > AZIMUTH NUMBER(19,11),
> > LENGTH NUMBER(19,11),
> > ELEVATION NUMBER(7,3),
> > DRILL_METHOD VARCHAR2(20 BYTE),
> > SSHAPE MDSYS.SDO_GEOMETRY,
> > GEOM_SHAPE MDSYS.SDO_GEOMETRY
> > )
> >
> > So SSHAPE is for gsml:location and GEOM_SHAPE is for sa:shape.
> >
> > I have 10 records in the table, here are two example,
> >
> > 1 178600 150650 0 002-11 LI 20985 0
> > 45 0 132.6 0 4.1 20985 2 137.6
> > unknown (2001, 7405, (178600, 150650, ), , ) (2002, , , (1, 2, 1, ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , ), (178600, 150650, 178600,
> > 150650, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , ))
> > 2 178600 150700 0 002-2 LI 20984 0
> > 45 0 103.6 0 2.4 20984 3 125.6
> > unknown (2001, 7405, (178600, 150700, ), , ) (2002, , , (1, 2, 1, ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , ), (178600, 150700, 178600,
> > 150700, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
> > , , , , , , , , , , , , , , , , , , , , , , , , , ))
> >
> > I set the values in columns SSHAPE and GEOM_SHAPE as
> >
> > *update scott.borehole3 set
> > SSHAPE=SDO_GEOMETRY(2001,7405,SDO_POINT_TYPE(X,
> > Y, NULL), NULL, NULL)
> > *update scott.borehole3 set
> > GEOM_SHAPE=SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(X,
> > Y, X, Y))
> >
> > I also tried
> >
> > *update scott.borehole3 set
> > SSHAPE=SDO_GEOMETRY(2001,7405,SDO_POINT_TYPE(145.8057083 -39.8385806,
> > NULL), NULL, NULL)
> > *update scott.borehole3 set
> > GEOM_SHAPE=SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(145.8057083
> > -39.8385806, 145.8057083 -39.8385806))
> >
> >
> > Then I add only one record in USER_SDO_GEOM_METADATA table (about the
> > table name, I've tried BOREHOLE3 and SCOTT.BOREHOLE3),
> >
> > INSERT INTO
> > USER_SDO_GEOM_METADATA VALUES (
> >
> > 'BOREHOLE3',
> > 'GEOM_SHAPE',
> >
> > MDSYS.SDO_DIM_ARRAY(
> >
> > MDSYS.SDO_DIM_ELEMENT('longitude', 0, 1000000,
> > 0.000001),
> >
> > MDSYS.SDO_DIM_ELEMENT('latitude', 0, 1000000, 0.000001)
> >
> > ),
> > 7405);
> >
> >
> > 2. mapping file
> >
> > In mapping file, I define
> >
> > <AttributeMapping>
> >
> > <targetAttribute>gsml:collarLocation/gsml:BoreholeCollar/gsml:location</targetAttribute>
> > <sourceExpression>
> > <OCQL>SSHAPE</OCQL>
> > </sourceExpression>
> > </AttributeMapping>
> >
> > <AttributeMapping>
> >
> > <targetAttribute>gsml:Borehole/sa:shape</targetAttribute>
> > <sourceExpression>
> > <OCQL>GEOM_SHAPE</OCQL>
> > </sourceExpression>
> >
> > <targetAttributeNode>gml:LineStringType</targetAttributeNode>
> > </AttributeMapping>
> >
> >
> > The version of GeoServer is latest one, which I download from
> > http://gridlock.opengeo.org/geoserver/trunk/. I use tomcat plus WAR.
> >
> > It's strange, isn't it?
> >
> > Best wishes,
> >
> > Ying
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > From: [email protected]
> > To: [email protected]; [email protected]
> > Date: Wed, 27 Oct 2010 09:26:18 +0800
> > Subject: RE: [Geoserver-users] About gsml:Borehole again
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Hi Ying,
> >
> > Hmm... not sure what went wrong in your
> > setting...
> >
> > Can you provide more info of what you had tried.
> >
> > Regards,
> > Florence
> >
> >
> >
> > From: ssss [mailto:[email protected]]
> >
> > Sent: Tuesday, 26 October 2010 7:07 PM
> > To: Tan, Florence
> > (CESRE, Kensington); [email protected]
> > Subject:
> > RE: [Geoserver-users] About gsml:Borehole again
> >
> >
> >
r-users
------------------------------------------------------------------------------
Nokia and AT&T present the 2010 Calling All Innovators-North America contest
Create new apps & games for the Nokia N8 for consumers in U.S. and Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
http://p.sf.net/sfu/nokia-dev2dev
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
