topic Re: View geometry not visible using sd_geom_sdo_buffer in Data Management Questions

View geometry not visible using sd_geom_sdo_buffer

BryanBurgemeestre — Wed, 06 Aug 2014 11:42:21 GMT

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

)

SELECT c.KDU_ID

.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

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?

Re: View geometry not visible using sd_geom_sdo_buffer

TravisVal — Wed, 06 Aug 2014 18:24:34 GMT

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

Re: View geometry not visible using sd_geom_sdo_buffer

BryanBurgemeestre — Thu, 07 Aug 2014 07:49:49 GMT

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

Re: View geometry not visible using sd_geom_sdo_buffer

VinceAngelo — Thu, 07 Aug 2014 14:30:51 GMT

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

Re: View geometry not visible using sd_geom_sdo_buffer

BryanBurgemeestre — Mon, 11 Aug 2014 12:42:13 GMT

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

Re: View geometry not visible using sd_geom_sdo_buffer

VinceAngelo — Tue, 12 Aug 2014 14:25:13 GMT

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

Re: View geometry not visible using sd_geom_sdo_buffer

BryanBurgemeestre — Wed, 20 Aug 2014 06:49:02 GMT

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