I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column.
Options for the GEOMETRY column datatype include:
- ESRI's proprietary implementation of ST_GEOMETRY (user-defined datatype; is an 'object' datatype)
- Oracle's SDO_GEOMETRY datatype
To start, I can successfully create a fast refresh MV without a GEOMETRY column:
create materialized view log on maximo.workorder with primary key; --remote table
grant select maximo.mlog$_workorder to schema_for_dblink; --I've given the dblink access to everything in this schema
create materialized view my_gis_schema.wo_mv
build immediate
refresh fast
start with sysdate next sysdate + (15/(60*60*24))
as
select
cast(workorderid as number(38,0)) as objectid,
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 GEOMETRY column in the MV (right now, the coordinates are stored in number columns, not a geometry column).
Unfortunately, my options for the GEOMETRY column in an MV seem pretty limited:
Oracle doesn’t seem to support ESRI's ST_GEOMETRY datatype in MVs (more info here and here).
- The SQL would be:
sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape
- The SQL would be:
Additionally, Oracle doesn't seem to support SDO_GEOMETRY in MVs with the fast refresh option on a remote table:
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
- The SQL would be:
Question:
Is there a way to include a GEOMETRY column in a materialized view on a remote table, using the fast refresh option?