View geometry not visible using sd_geom_sdo_buffer

3036
6
08-06-2014 04:42 AM
BryanBurgemeestre
New Contributor

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?

Tags (3)
0 Kudos
6 Replies
TravisVal
New Contributor III

Hello Bryan,

What version of Oracle, ArcGIS Desktop, and SDE are you using?  Are there any messages in the SDE log files when you attempt to draw the layer?

Travis

0 Kudos
BryanBurgemeestre
New Contributor

Hello Travis,


ArcGIS Desktop: I tested the results in both 9.3.1 and 10.2.

Oracle version: Oracle 10g 10.2

SDE log files:

ST_Geometry Schema Owner: (SDE) Type Release: 1007
Instance initialized for ((intwis2)) . . .

[Thu Aug 07 09:45:54 2014] [505496] [wd12064] db_array_fetch_spix_recs OCI Fetch Error (13226)
[Thu Aug 07 09:45:54 2014] [505496] [wd12064] load_buffer error -321 on KDU_MIDPOINT_VW2, rowid -1

ST_Geometry Schema Owner: (SDE) Type Release: 1007
Instance initialized for ((intwis2)) . . .

[Thu Aug 07 09:46:02 2014] [505497] [wd12064] db_array_fetch_spix_recs OCI Fetch Error (13226)
[Thu Aug 07 09:46:02 2014] [505497] [wd12064] load_buffer error -321 on KDU_MIDPOINT_VW2, rowid -1
[Thu Aug 07 09:46:07 2014] [505497] [wd12064] db_array_fetch_spix_recs OCI Fetch Error (13226)
[Thu Aug 07 09:46:07 2014] [505497] [wd12064] load_buffer error -321 on KDU_MIDPOINT_VW2, rowid -1
[Thu Aug 07 09:46:14 2014] [505497] [wd12064] db_array_fetch_spix_recs OCI Fetch Error (13226)
[Thu Aug 07 09:46:14 2014] [505497] [wd12064] load_buffer error -321 on KDU_MIDPOINT_VW2, rowid -1

0 Kudos
VinceAngelo
Esri Esteemed Contributor

Those errors are generated by Oracle, and look like those generated frequently by the early Oracle 10.2.0.1 release that was never supported by Esri because it failed certification tests. 

ArcGIS 9.3.1 was placed in Retired support status with the new year, but even before that, Oracle 10.2.0.3 was the minimum supported 10gR2 release.  I'd recommend using non-retired Esri software, but if you're going to continue with 9.3.1, I'd suggest making sure that you're using the terminal service pack (SP2) with the final set of patches on the supported RDBMS infrastructure.

- V

0 Kudos
BryanBurgemeestre
New Contributor

we will integrate Oracle 11 by the end of august. So I hope that in combination with ArcGIS 10.2 the same statements will work correctly. I will have to be patient till then.


Thanks.

-Bryan

0 Kudos
VinceAngelo
Esri Esteemed Contributor

I don't have great hopes for the performance of this view.  The query is usuriously expensive as a one-time event (you should be able to generate a centroid ON the line without needing to buffer it first); I can't imagine that it will be better over time.  Increasing the frequency of materialized view recalculation would be far more efficient, as would restructuring the table to contain two geometry columns, with a trigger to automatically repopulate the centroid, and using two views to access the polygon and point aspects of this feature.

- V

0 Kudos
BryanBurgemeestre
New Contributor

I have yet to find another solution to generate a centroid on a line in Oracle. The SDO_GEOM module does not have an option to create a centre point of anything other than a polygon.

I like your suggestions, I'll look into this.

-Bryan

0 Kudos