3

I want to create an Oracle materialized view with the fast refresh option on a remote table (in an enterprise GDB).

I can do this successfully without a SHAPE column:

create materialized view log on maximo.workorder with primary key;  --remote table
grant select maximo.mlog$_workorder to schema_for_dblink; --I've provided a dblink access to everything in a certain schema

create materialized view my_gis_schema.wo_mv --enterprise GDB
build immediate 
refresh fast
start with sysdate next sysdate + (15/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid, --workorderid is a fake primary key; it has a NOT NULL constraint and a unique index
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    maximo.workorder@my_dblink

The MV above works, but I want to store the XY coordinates from the remote table in a SHAPE column in the MV.

Unfortunately, my options seem pretty limited:

  1. Oracle doesn’t seem to support SDE.ST_GEOMETRY in MVs (more info here and here).

    • Although, I didn’t get that far — due to the fact that the schema I was using (SYSTEM) to create the MV doesn’t have the “inherit privileges” for the SDE owner (more info here and at the bottom here). I assumed, even if I sorted out the SDE privileges issue, I still wouldn't be able to proceed, due to the limitations in #1.
    • The SQL would be: sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape
  2. Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs on a remote table with the fast refresh option: ORA-12015: cannot create a fast refresh materialized view from a complex query

    • The SQL would be: sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape

Question:

Is there a way to include a SHAPE column in a materialized view in a remote table with the fast refresh option?

0
2

I have a few options. I can create the fast refresh MV on a remote table without the SHAPE column, but then add the SHAPE column afterwards.


A. Add SDO_GEOM column & trigger

Create an SDO_GEOMETRY column in the MV after-the-fact:

alter table my_gis_schema.wo_mv add shape mdsys.sdo_geometry;

And add a trigger in the MV to populate the column:

create or replace trigger my_gis_schema.wo_mv_sdo_geom_shape
    before insert or update
    on my_gis_schema.wo_mv
    for each row
declare
    v_longitudex number (16,2);
    v_latitudey  number (16,2);
    v_plussisgis number (1,0);
begin
    v_longitudex  := :new.longitudex;
    v_latitudey   := :new.latitudey;
    v_plussisgis  := :new.plussisgis;
    if v_plussisgis = 1 then
        :new.shape := mdsys.sdo_geometry(2001, 26917, mdsys.sdo_point_type(v_longitudex, v_latitudey, null), null,null);
    end if;
end;

Refresh the data in the MV so the that SHAPE column gets populated for existing rows (via the trigger).

exec dbms_mview.refresh('WO_MV', 'C');

The 'C' parameter stands for 'COMPLETE'.


Create a spatial index on the MV. This can be done with SQL or with the Add Spatial Index geoprocessing tool in ArcGIS Desktop.


Note: In my case, I also created a query layer on top of the MV so that I could exclude non-spatial records:

select
    *
from
    my_gis_schema.wo_mv
where
    plussisgis = 1

B. View on top of MV

Create a view on top of the materialized view that generates the SHAPE column on-the-fly:

create or replace view wo_mv_vw as (
select
    objectid
    wonum,
    status,
    --other fields
    longitudex,
    latitudey,
    sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape
from
    my_gis_schema.wo_mv
)

Notes:

  1. The dynamic SDO_GEOMETRY column performs surprisingly well (almost indistinguishable from a static field in a table).
  2. If I were to create a dynamic SDE.ST_GEOMETRY column instead, it wouldn't perform nearly as well (10x slower). I'm not sure why.

C. View with function-based spatial index

Create a view on the MV with a SHAPE column (just like with option B), but improve performance by using a function-based spatial index.


Create a deterministic function:

(edit: it turns it’s not necessary to create a custom function.)

create or replace function sdo_geom_from_xy(longitudex in number, latitudey in number)
return sdo_geometry deterministic is
begin
    --I'm not sure how to handle XYs that are NULL. The function-based index seems to error out when there are NULLs. For now, I'm returning zeros instead of nulls -- until I understand a bit more about what is going on.
    return sdo_geometry(2001, 26917, sdo_point_type(nvl(longitudex,0), nvl(latitudey,0), null),null, null);
end;

Update USER_SDO_GEOM_METADATA:

insert into user_sdo_geom_metadata values(
    'WO_MV',
    --no spaces allowed: https://docs.oracle.com/database/121/SPATL/geometry-metadata-views.htm#SPATL546
    'my_gis_schema.sdo_geom_from_xy(longitudex,latitudey)',
    sdo_dim_array(
        sdo_dim_element('X', 663795.93, 676674.94, 0.001),
        sdo_dim_element('Y', 4857659, 4879068.83, 0.001)), 26917);

Create a function-based spatial index:

create index wo_mv_idx on 
    wo_mv(my_gis_schema.sdo_geom_from_xy(longitudex,latitudey))
    indextype is mdsys.spatial_index;

Create a view that uses the the function-based spatial index:

create or replace view wo_mv_vw as (
select
    objectid
    wonum,
    status,
    --other fields
    longitudex,
    latitudey,
    my_gis_schema.sdo_geom_from_xy(longitudex,latitudey)) as shape
from
    my_gis_schema.wo_mv
)

Notes:

  • The performance will be better than a view without a function-based spatial index. (The performance should be as good as the MV that has a static SHAPE column.)
  • I haven’t tried creating a function-based index on a view with an SDE.ST_GEOMETRY column. But I assume that would work too.
  • The view must use the exact same syntax that was used in the function-based index. In this case, it's this: my_gis_schema.sdo_geom_from_xy(longitudex,latitudey)).
  • There is a related post here: Can I create an Oracle Spatial View from a non-spatial table? (with an excellent answer from @AlberGodfrind.

D. Additional local MV with SDO_GEOM column

As mentioned, it's not possible to have a SHAPE column in a fast MV on a remote table.

But we can create an additional MV that would reference a so-called "local table" -- aka the MV from option A. In the additional MV, we could add a SHAPE column.

That would allow us to avoid the fast refresh/remote-table/MV error: ORA-12015: cannot create a fast refresh materialized view from a complex query

E. Add SDE.ST_GEOMETRY column & trigger

Just like option A, where we added an SDO_GEOMETRY column to the MV, we could alternatively add an SDE.ST_GEOMETRY column:

alter table my_gis_schema.wo_mv drop column shape;
alter table my_gis_schema.wo_mv add shape sde.st_geometry

And add a trigger in the MV to populate the SDE.ST_GEOMETRY SHAPE column:

create or replace trigger wo_mv_st_geometry_shape
    before insert or update
    on my_gis_schema.wo_mv
    for each row
declare
    v_longitudex number (16,2);
    v_latitudey  number (16,2);
    v_plussisgis number (1,0);
begin
    v_longitudex  := :new.longitudex;
    v_latitudey   := :new.latitudey;
    v_plussisgis  := :new.plussisgis;
    if v_plussisgis = 1 then
        :new.shape := sde.st_geometry(v_longitudex,v_latitudey,null,null,26917);
    end if;
end;

Refresh the data in the MV so the that SHAPE column gets populated for existing rows (via the trigger).

exec dbms_mview.refresh('WO_MV', 'C');

Create a spatial index on the MV. This can be done with the Add Spatial Index geoprocessing tool in ArcGIS Desktop.

Note: SDE.ST_GEOMETRY performs slightly slower than SDO_GEOMETRY (points draw slightly slower in map).

F. Temporarily convert to binary (or WKT)

For scenarios where there is an existing ST_GEOMETRY or SDO_GEOMETRY SHAPE column in the source data:

It might be possible to do the following (untested):

  1. Convert the shape column to binary (or even WKT)
  2. Store the data in the MV using a datatype that MVs support
  3. Add a SHAPE column to the MV after-the-fact
  4. Add a spatial index to the SHAPE column
  5. Create a trigger that un-converts the binary back to a SHAPE value and store it in the SHAPE column.
0

A colleague has suggested that it's possible to solve this issue by:

"...creating a public synonym in the target DB on the top of DB link; the remote table will act like local table."

Supposedly, the synonym solves the issue of creating a materialized view with FAST refresh on a remote table with an SDO_GEOMETRY column.

CREATE public SYNONYM maximo.workorder FOR maximo.workorder@my_dblink;

-- ... then create the materialized view. 

My colleague says it has been tested and it works; it was tested using a fake primary key, which is consistent with my setup.

Unfortunately, I haven't had a chance to test it myself. I don't have CREATE MATERIALIZED VIEW privileges at the moment, so I haven't been able to verify if it works or not.

-1

Option-B in your answers i would suggest to go with. However I have another Option thinking of is to create a STORED PROCEDURE instead of Materialized View. The procedure which will update the ESRI Geodatabase Feature class table as ST Geometry. That should make the ArcGIS configuration convenient.

-1

Creation of MV including the shape column would cause issue if the table is having geometry column as ST_Geometry. This is a know issue https://support.esri.com/en/technical-article/000009568

-1

If the feature class is registered as versioned, creating the materialized view from the EVW view including the st_geometry shape column would be helpful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.