AnsweredAssumed Answered

View geometry not visible using sd_geom_sdo_buffer

Question asked by bburgemeestre on Aug 6, 2014
Latest reply on Aug 19, 2014 by bburgemeestre

Hello,

 

I attempted a view where a polyline is converted to a point using a buffer around the polyline, it then calculates the centroid of the buffer to set a point.

 

The result is a working view, the table is complete and the items are selectable. However the geometry is not visible! I am able to select and export the geometry to a shapefile, the shapefile does display the geometry.

 

The steps I took:

 

  

CREATE

 

 

OR REPLACE FORCE VIEW INTWIS2.

KDU_MIDPOINT_VW

 

 

(

  

KDU_ID

 

 

 

,

 

 

  

KDUIDENT

 

 

 

,

 

 

 

GEOMETRIE

 

)

 

AS

  

 

 

SELECT c.KDU_ID

 

,

 

 

  

c

 

.KDUIDENT

 

,

 

 

  

SDO_GEOM

 

.sdo_centroid

 

(

 

 

  

SDO_GEOM

 

.sdo_buffer (SDO_GEOM.sdo_mbr (geometrie), 1, 1

 

),

 

 

  

 

 

0.01

 

)

 

 

 

GEOMETRIE

 

 

 

FROM gw_kdu c, user_sdo_geom_metadata

 

m

 

 

  

 

 

WHERE m.table_name = 'GW_KDU' AND m.column_name = 'GEOMETRIE'

 

;

 

 

  

CREATE

 

 

OR REPLACE PUBLIC SYNONYM KDU_MIDPOINT_VW FOR INTWIS2.KDU_MIDPOINT_VW

 

;

GRANT

 

 

 

 

SELECT ON INTWIS2.KDU_MIDPOINT_VW TO GEOWEB3

 

;

GRANT

 

 

 

 

SELECT ON INTWIS2.KDU_MIDPOINT_VW TO INTWIS_BASIS

 

;

GRANT

 

 

 

 

SELECT ON INTWIS2.KDU_MIDPOINT_VW TO INTWIS_LEZEN

 

;

GRANT

 

 

 

 

SELECT ON INTWIS2.KDU_MIDPOINT_VW TO SDE WITH GRANT

 

OPTION;

 

 

 

I then registered the view using sdelayer:

 

 

sdelayer –o register –l KDU_MIDPOINT_VW,GEOMETRIE –e p -t SDO_GEOMETRY –C KDU_ID,USER –k SDO_GEOMETRY –P HIGH –x 0,0,5000,0.02 –G 28992

 

 

USER_SDO_GEOM_METADATA is filled:

 

  

INSERT

 

 

INTO USER_SDO_GEOM_METADATA (TABLE_NAME

 

,

 

 

  

COLUMN_NAME

 

 

 

,

 

 

  

DIMINFO

 

 

 

,

 

 

  

SRID

 

 

 

)

 

 

  

 

 

VALUES ('KDU_MIDPOINT_VW'

 

,

 

 

  

 

 

'GEOMETRIE'

 

,

 

 

  

MSDYS

 

.SDO_DIM_ARRAY (MDSYS.SDO_DIM_ELEMENT ('X', 70000, 180000, 0.01

 

),

 

 

  

MDSYS

 

.SDO_DIM_ELEMENT ('Y', 480000, 580000, 0.01)), 28992

 

);

 

 

 

 

Thats it.

 

I got a materialized view working using pretty  much the same statements, however I want the view to update live if new records are added to the base table.

 

Any ideas?

Outcomes