Latest Contributions by RobertKrisher

Branch Version Sql Views The following queries show examples of how to create materialized views on branch versioned utility network for various DBMS. For additional information about the reasoning behind this approach, please refer to the Branch Versioning and SQL Esri blog post. PostgreSQL Create Materialized Views   DROP MATERIALIZED VIEW IF EXISTS unadmin.device_bvw; CREATE MATERIALIZED VIEW unadmin.device_bvw AS WITH t0_ AS (SELECT objectid, globalid, assetgroup, assettype, assetid, shape FROM (SELECT objectid, globalid, assetgroup, assettype, assetid, shape, Row_number() over (PARTITION BY objectid ORDER BY gdb_from_date DESC) rn_, gdb_is_delete FROM unadmin.electricdevice WHERE (gdb_branch_id = 0)) a WHERE rn_ = 1 AND gdb_is_delete = 0 ) SELECT objectid, globalid, assetgroup, assettype, assetid, shape FROM t0_ WITH DATA; CREATE UNIQUE INDEX IF NOT EXISTS device_bvw_uuid ON unadmin.device_bvw USING btree (globalid COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default; CREATE INDEX IF NOT EXISTS device_bvw_spat_idx ON unadmin.device_bvw USING gist (shape) TABLESPACE pg_default;   Refresh Views Once you’ve created the materialized view(s), you should create and schedule a function in your database to refresh them periodically. How often you call this function and refresh your views depend on the volume of edits you make and how long it takes to refresh the views. A common practice is to refresh the views once or twice a day, but for very large datasets or datasets that require a lot of additional processing you may only refresh the views once a week.   CREATE OR REPLACE FUNCTION unadmin.refresh_bvw() RETURNS VOID LANGUAGE 'plpgsql' AS $$ DECLARE BEGIN REFRESH MATERIALIZED VIEW unadmin.assembly_bvw; REFRESH MATERIALIZED VIEW unadmin.device_bvw; REFRESH MATERIALIZED VIEW unadmin.junction_bvw; REFRESH MATERIALIZED VIEW unadmin.line_bvw; REFRESH MATERIALIZED VIEW unadmin.structureboundary_bvw; REFRESH MATERIALIZED VIEW unadmin.structurejunction_bvw; REFRESH MATERIALIZED VIEW unadmin.structureline_bvw; REFRESH MATERIALIZED VIEW unadmin.associations_bvw; REFRESH MATERIALIZED VIEW unadmin.subnetworks_bvw; END;$$ ALTER FUNCTION unadmin.refresh_bvw() OWNER TO unadmin;   Oracle Create Materialized Views You should consult with a DBA before specifying any parameters in your create index statement, otherwise the defaults for the database are used. If you include a geometry in your view you need to specify the spatial grid size and spatial reference id (SRID) of the base table when creating the spatial index on the shape field. In the example below I have included a statement that you can use to query this information.   GRANT CREATE MATERIALIZED VIEW TO UNADMIN; CREATE MATERIALIZED VIEW DEVICE_BVW BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT OBJECTID, GLOBALID, ASSETGROUP, ASSETTYPE, ASSETID, SHAPE FROM UNADMIN.ELECTRICDEVICE WHERE GDB_ARCHIVE_OID IN (SELECT GDB_ARCHIVE_OID FROM (SELECT GDB_ARCHIVE_OID, ROW_NUMBER() OVER(PARTITION BY objectid ORDER BY gdb_from_date DESC) rn, gdb_is_delete FROM UNADMIN.ELECTRICDEVICE WHERE (gdb_branch_id = 0)) br__ WHERE br__.rn = 1 AND br__.gdb_is_delete = 0); CREATE UNIQUE INDEX DEVICE_BVW_UUID ON DEVICE_BVW (GLOBALID) ; --Get the underlying grid size and SRID from the base table SELECT A.GRID.GRID1, A.GRID.GRID2, A.GRID.GRID3,A.SRID FROM SDE.ST_GEOMETRY_INDEX WHERE OWNER = 'UNADMIN' AND TABLE_NAME = 'ELECTRICDEVICE'; --Create a spatial index CREATE INDEX DEVICE_BVW_SA_IDX ON UNADMIN.ELECTRICDEVICE (“SHAPE”) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_grids=<Grid Sizes Here> st_srid=<Your ID Here>');   Refresh Views To keep your views up to date, you should create a materialized view refresh group for these views and set it to execute on a regular schedule to ensure the data is refreshed often enough to meet your reporting requirements but not so often it places a burden on your system. You can learn more about refresh groups by reading Oracle’s online help for DBMS Refresh. Oracle 21C and later have methods for automatically refreshing materialized views. However, if you are using an older release of oracle you will want to schedule a refresh group that handles refreshing your materialized views.   BEGIN DBMS_REFRESH.make( name => 'UNADMIN.BVW_REFRESH', list => '', next_date => SYSDATE, interval => '/*12:Hours*/ SYSDATE + 12/24', implicit_destroy => FALSE, lax => FALSE, job => 0, rollback_seg => NULL, push_deferred_rpc => TRUE, refresh_after_errors => TRUE, purge_option => NULL, parallelism => NULL, heap_size => NULL); END; / BEGIN DBMS_REFRESH.add( name => 'UNADMIN.BVW_REFRESH', list => 'UNADMIN.ASSEMBLY_BVW,UNADMIN.DEVICE_BVW,UNADMIN.JUNCTION_BVW,UNADMIN.STRUCTUREBOUNDARY_BVW,UNADMIN.STRUCTUREJUNCTION_BVWUNADMIN.STRUCTURELINE_BVW', lax => TRUE); END; /   Sql Server Create Reporting Table Even though SQL Server does have an implementation for materialized views (called indexed views) it is recommended that you do not use indexed views to materialize the results of queries. You can read more about these restrictions in Microsoft’s online help. Instead, you should write a stored procedure that will recreate tables and indices for your queries. The following statement to create a stored procedure that will create or refresh a reporting table for electric devices in the utility network for a SQL server database. Because we are doing this all in a single stored procedure, we also include the statements for creating the indices.   CREATE OR ALTER PROCEDURE [unadmin].[CreateDevice_BVW] AS BEGIN BEGIN TRY BEGIN TRANSACTION /* DECLARE PROCEDURE VARIABLES */ DECLARE @minx NUMERIC(38,8); DECLARE @miny NUMERIC(38,8); DECLARE @maxx NUMERIC(38,8); DECLARE @maxy NUMERIC(38,8); DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT, @ErrorProcedure NVARCHAR(200); /* DROP THE TABLE IF ALREADY EXISTS */ DROP TABLE IF EXISTS [unadmin].[Device_BVW]; /* CREATE THE TABLE BASED FROM DEFAULT RECORDS OF THE BRANCH VERSIONED TABLE */ SELECT OBJECTID, GlobalID, Shape INTO [unadmin].[Device_BVW] FROM [unadmin].[Device] WHERE GDB_ARCHIVE_OID IN ( SELECT GDB_ARCHIVE_OID FROM ( SELECT GDB_ARCHIVE_OID, ROW_NUMBER() OVER(PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) rn, GDB_IS_DELETE FROM [unadmin].[ Device] WHERE (gdb_branch_id = 0)) br__ WHERE br__.rn = 1 AND br__.GDB_IS_DELETE = 0); /* ADD INDEXES */ ALTER TABLE [unadmin].[Device_BVW] ADD CONSTRAINT [Device_BVW_pk] PRIMARY KEY CLUSTERED ([OBJECTID] ASC); CREATE UNIQUE NONCLUSTERED INDEX [DEVICE_BVW_UUID] ON [unadmin].[Device_BVW] ([GLOBALID] ASC); --You only need to do this if your resulting table needs a shape column. --This will construct a spatial index using the extent of the original table WITH envelope AS (SELECT shape.STEnvelope() AS envelope FROM [unadmin].[ Device]), corners AS (SELECT envelope.STPointN(1) AS point FROM envelope UNION ALL SELECT envelope.STPointN(3) FROM envelope) SELECT @minx=min(point.STX), @miny=min(point.STY), @maxx=max(point.STX), @maxy=max(point.STY) FROM corners; /* GENERATE & EXECUTE THE SQL FOR CREATING THE SPATIAL INDEX */ DECLARE @sql NVARCHAR(2000) = 'CREATE SPATIAL INDEX [Device_BVW_spidx] ON [unadmin].[ Device_BVW] (Shape) USING GEOMETRY_AUTO_GRID WITH (BOUNDING_BOX =('+CAST(@minx AS NVARCHAR(20))+',' +CAST(@miny AS NVARCHAR(20))+',' +CAST(@maxx AS NVARCHAR(20))+',' +CAST(@maxy AS NVARCHAR(20))+'))'; EXECUTE (@sql); COMMIT TRANSACTION END TRY BEGIN CATCH /* IF ERROR THEN ROLLBACK TRANSACTION AND RETURN ERROR */ ROLLBACK TRANSACTION SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure: %s, Line: %d, ' + 'Message: '+ ERROR_MESSAGE(); RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine); END CATCH END /* RUN THE STORED PROCEDURE EXECUTE gisowner.CreateTAX_BVW */   Refresh Views To refresh the tables, you should execute the stored procedure(s) created on a regular schedule to ensure the data is refreshed often enough to meet your reporting requirements but not so often it places a burden on your system. Below you can find an example script.   exec UNADMIN.CreateAssembly_BVW; exec UNADMIN.CreateDevice_BVW; exec UNADMIN.CreateJunction_BVW; exec UNADMIN.CreateLine_BVW; exec UNADMIN.CreateStructureBoundary_BVW; exec UNADMIN.CreateStructureJunction_BVW; exec UNADMIN.CreateStructureLine_BVW; exec UNADMIN.CreateAssociations_BVW; exec UNADMIN.CreateSubnetworks_BVW;   SAP HANA Create Reporting Table Because SAP Hana doesn’t have materialized views the recommended approach is to use a stored procedure to create a column table that stores the current state of the data in default. The following stored procedure will persist the current state of the table from the default version, then create indices on the global id and object id columns.   /*CREATE STORED PROCEDURE*/ CREATE OR REPLACE PROCEDURE UNADMIN.CREATE_DEVICE_BVW (OUT sql_code INT, OUT sql_msg NVARCHAR(512)) LANGUAGE SQLSCRIPT AS BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN sql_code = ::SQL_ERROR_CODE; sql_msg = ::SQL_ERROR_MESSAGE; RESIGNAL; END; sql_code = 0; sql_msg = null; IF EXISTS (SELECT 1 FROM M_TABLES WHERE SCHEMA_NAME = 'UNADMIN' AND TABLE_NAME='DEVICE_BVW') THEN DROP TABLE UNADMIN.DEVICE_BVW; END IF; CREATE COLUMN TABLE UNADMIN.DEVICE_BVW AS ( WITH T0_ AS (SELECT OBJECTID, GLOBALID, ASSETGROUP, ASSETTYPE, ASSETID, SHAPE FROM (SELECT OBJECTID, GLOBALID, ASSETGROUP, ASSETTYPE, ASSETID, SHAPE, ROW_NUMBER() OVER (PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) RN_, GDB_IS_DELETE FROM UNADMIN.Device WHERE (GDB_BRANCH_ID = 0)) A WHERE RN_ = 1 AND GDB_IS_DELETE = 0 ) SELECT OBJECTID, GLOBALID, SHAPE FROM T0_ ); CREATE UNIQUE INDEX DEVICE_BVW_UUID ON UNADMIN.DEVICE_BVW ( GLOBALID ASC); CREATE INDEX DEVICE_BVW_PK ON UNADMIN.DEVICE_BVW ( OBJECTID ASC ); END;   Refresh Reporting Table To refresh the table, you just call the stored procedure associated with any tables you want to refresh.   CALL UNADMIN.CREATE_Assembly_BVW(? ,?); CALL UNADMIN.CREATE_Device_BVW(? ,?); CALL UNADMIN.CREATE_Junction_BVW(? ,?); CALL UNADMIN.CREATE_Line_BVW(? ,?); CALL UNADMIN.CREATE_StructureBundary_BVW(? ,?); CALL UNADMIN.CREATE_StructureJunction_BVW(? ,?); CALL UNADMIN.CREATE_StructureLine_BVW(? ,?); CALL UNADMIN.CREATE_Associations_BVW(? ,?); CALL UNADMIN.CREATE_Subnetwork_BVW(? ,?);     ... View more

Based upon feedback from users of the ArcGIS Utility Network, a new Add-In has been released to enable users to rapidly select features by association in the Attribute Pane.  Details are provided in this blog. You can download a copy of this add-in below.   ****  Note: Revised Add-in 03/30/2023 ... View more