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:
- The dynamic SDO_GEOMETRY column performs surprisingly well (almost indistinguishable from a static field in a table).
- 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):
- Convert the shape column to binary (or even WKT)
- Store the data in the MV using a datatype that MVs support
- Add a SHAPE column to the MV after-the-fact
- Add a spatial index to the SHAPE column
- Create a trigger that un-converts the binary back to a SHAPE value and store it in the SHAPE column.
