Sorting and merging geometries in Oracle Spatial

Consider a requirement where an unsorted set of geometries needs to be merged into a single geometry. Take for instance the following set of geometries where:

  • a Red dot indicates the startpoint
  • The arrow indicates the drawing direction
  • The label “1-50″ indicates the order of drawing and an identifier for the geometry
Sample set 1

Sample set 1

Sample set 2

Sample set 2

Both of these geometries should be merged into the following:

Result set 1

Result set 1

Oracle lets you create your own aggregate function enabling a nice flexible solution, let’s try to create an aggregate function SDE_MERGE enabling us to create a 3002-gtype geometry or in G/Technology terms a ‘CompositePolylineGeometry’, (G3E_COMPONENT.G3E_TYPE=4). Our aggregate function must be able to:

    • Change the order of geometries. The order in which the geometries are drawn may not be the order in which the geometries need to be merged to create the resulting geometry.
    • Change direction of a geometry. Geometries may have different drawing directions, but when appending geometries to each other to create a resulting geometry, the direction needs to be the same.
    • Detect not supported situations:
      1. A closed set of Geometries
      2. Geometries with multiple connections
      3. A set of disjoint geometries

The picture below shows a closed set of Geometries, this set of geometry cannot be merged into a 3002-gtype geometry so when this situation is encountered SDE_MERGE should return null:

Closed set

Closed set

Also, if there are more then 2 geometries connecting to each other, no 3002-gtype geometry can be created:

Multiple connections

Multiple connections

When a set of geometries is not connected/disjoint, null should be returned:

Disjoint set

Disjoint set

Here’s the header of the SDO_MERGE function:

create or replace type SDE_MERGE_i as object
(
  geom 	mdsys.sdo_geometry,

  STATIC FUNCTION ODCIAggregateInitialize
  ( sctx IN OUT SDE_MERGE_i
  ) RETURN number,

  MEMBER FUNCTION ODCIAggregateIterate
  (   self IN OUT SDE_MERGE_i
    , item IN mdsys.sdo_geometry
  )	RETURN number,

  MEMBER FUNCTION ODCIAggregateTerminate
  (   self IN 	  SDE_MERGE_i
    , ret  OUT    mdsys.sdo_geometry
    , flgs IN     number
  ) RETURN number,

  MEMBER FUNCTION ODCIAggregateMerge
  (   self IN OUT SDE_MERGE_i
    , ctx2 IN     SDE_MERGE_i
  ) RETURN number
);

Our SDE_MERGE function has a single member geom, a geometry which we will use to store all geometries to be merged and return the resulting geometry. When SDE_MERGE is called, Oracle first calls 'ODCIAggregateInitialize', this is were the initial geometry is initialized. Then, for each geometry in the applied order, 'ODCIAggregateIterate' is called and when all geometries have been entered, Oracle will call 'ODCIAggregateTerminate'. Sorting and merging geometries can only take place after all geometries are known so this is where the actual sorting and merging of geometries is done. Before this is called, we need some kind of data structure to store all geometries to be processed later. This is were a 3006-gtype geometry comes in, we will store all temporary geometries in it and enumerate this 3006-geometry when we need to do the sorting and merging.

This is all we need in 'ODCIAggregateIterate':

MEMBER FUNCTION ODCIAggregateIterate
(
  self IN OUT SDO_MERGE_i
, item IN 	mdsys.sdo_geometry
) return number
IS
BEGIN
  if( self.geom.SDO_ORDINATES is null)
  then
	SELF.geom := item;
	return( ODCIConst.Success);
  end if;

  SELF.geom := sdo_util.append ( SELF.geom, item);
  return( ODCIConst.Success);
END;

When all items are encountered Oracle calls the ‘ODCIAggregateTerminate’-function where the temporary 3006-gtype/multi-line geometry is enumerated, all 3002-gtype geometries in it are sorted and merged into a 3002-gtype geometry.

Implementation

The real work of this solution is done in the 'ODCIAggregateTerminate'-function where all the geometries in the temporary 3006-gtype geometry are enumerated to see if they can be merged into a single geometry. If they can, the order and direction of all the geometries is changed where needed, and then merged into a single geometry. The complete solution can be downloaded from here.

Testing

While the actual target data is G/Technology driven, I will use a table DB_ORIGIN_PATHS for testing purposes :

CREATE TABLE DB_ORIGIN_PATHS
(
  ID          NUMBER,
  PATH_FID    NUMBER(10),
  HASH        INTEGER,
  GEOM        MDSYS.SDO_GEOMETRY,
  GEOM2D      MDSYS.SDO_GEOMETRY
);

The geometries are rendered using Geoserver/Openlayers so I can quickly inspect results without the hassle of publishing data with G/Technology. Each geometry inserted is converted to a 2D-geometry because Geoserver has problems rendering 3D-arcs, this 2D-geometry then is used to inspect the results:

CREATE OR REPLACE TRIGGER DB_ORIGIN_PATHS_AfterIU BEFORE INSERT or UPDATE ON DB_ORIGIN_PATHS
for each row
begin
  :new.geom2d := SDO_CS.MAKE_2D( :new.GEOM);
end;
/

Furthermore, because I don’t have a reference to a fid of the applied geometries inside my 'SDE_MERGE'-implementation, I need some way of identifying the geometries. That’s why I introduced a ‘Hash’-value for each one :

create or replace function GETHASHCODE( theGeom mdsys.sdo_geometry)
	return int
as
    p1 		mdsys.sdo_geometry;
    p2 		mdsys.sdo_geometry;
    hash	int;
begin
  p1 := SDO_LRS.GEOM_SEGMENT_START_PT( theGeom);	
  p2 := SDO_LRS.GEOM_SEGMENT_END_PT  ( theGeom);
    
  hash := p1.sdo_ordinates( 1) + p1.sdo_ordinates( 2) + p2.sdo_ordinates( 1) + p2.sdo_ordinates( 2);
  return( hash);    
end;

Now onto our first testset, 3 basic lines :

BEGIN
delete from DB_ORIGIN_PATHS;

insert into DB_ORIGIN_PATHS( id, path_fid, geom) 
  select 1, 1, SDO_GEOMETRY( 3002, NULL, NULL, 
    SDO_ELEM_INFO_ARRAY( 1,2,1), 
    SDO_ORDINATE_ARRAY( 10,10,0, 20,10,0 ))
from dual;            

insert into DB_ORIGIN_PATHS( id, path_fid, geom) 
  select 2, 2, SDO_GEOMETRY( 3002, NULL, NULL, 
    SDO_ELEM_INFO_ARRAY( 1,2,1), 
    SDO_ORDINATE_ARRAY( 20,10,0, 30,10,0))
from dual;

insert into DB_ORIGIN_PATHS( id, path_fid, geom) 
  select 3, 3, SDO_GEOMETRY( 3002, NULL, NULL, 
    SDO_ELEM_INFO_ARRAY( 1,2,1), 
    SDO_ORDINATE_ARRAY( 30,10,0, 40,10,0)) 
from dual;

update DB_ORIGIN_PATHS set hash = GETHASHCODE( geom);    
commit;
END;

This renders into this :

Test set 1

Test set 1

Merging the geometries and deleting the old ones:

BEGIN   
  insert into DB_ORIGIN_PATHS( id, path_fid, geom) 
    select 100, 100, SDE_MERGE( geom) from rr.DB_ORIGIN_PATHS;

  update DB_ORIGIN_PATHS set hash = GETHASHCODE( geom);
  delete from DB_ORIGIN_PATHS where id < 100; 
  commit;
END;

Renders into:

Result set 1

Result set 1

Our second test set :

BEGIN
  delete from DB_ORIGIN_PATHS;

  insert into DB_ORIGIN_PATHS( id, path_fid, geom) 
    select 1, 1, SDO_GEOMETRY( 3002, NULL, NULL, 
      SDO_ELEM_INFO_ARRAY( 1,2,1), 
      SDO_ORDINATE_ARRAY( 10,10,0, 20,10,0 )) 
  from dual;

  insert into DB_ORIGIN_PATHS( id, path_fid, geom) 
    select 2, 2, SDO_GEOMETRY( 3002, NULL, NULL, 
      SDO_ELEM_INFO_ARRAY( 1,2,1), 
      SDO_ORDINATE_ARRAY( 30,10,0, 20,10,0)) 
  from dual;

  insert into DB_ORIGIN_PATHS( id, path_fid, geom) 
    select 3, 3, SDO_GEOMETRY( 3002, NULL, NULL, 
      SDO_ELEM_INFO_ARRAY( 1,2,1), 
      SDO_ORDINATE_ARRAY( 30,10,0, 40,10,0)) 
  from dual;		

  update DB_ORIGIN_PATHS set hash = GETHASHCODE( geom);
  commit;
END;

renders into this :

Test set 2

Test set 2

Again, merging the geometries and deleting the old ones:

BEGIN   
  insert into DB_ORIGIN_PATHS( id, path_fid, geom) select 100, 100, SDE_MERGE( geom) from rr.DB_ORIGIN_PATHS;	            
  update DB_ORIGIN_PATHS set hash = GETHASHCODE( geom);
  delete from DB_ORIGIN_PATHS where id < 100;    
  commit;
END;

Renders into:

Result set 2

Result set 2

I have repeated this for a couple of geometries where the set is listed on the left and merged result on the right :

Test set Result
sample_set3

Sample set 3

result_set3

Result set 3

sample_set4

Sample set 4

result_set4

Result set 4

sample_set5

Sample set 5

result_set5

Result set 5

sample_set6

Sample set 6

result_set6

Result set 6

sample_set7

Sample set 7

result_set7

Result set 7

Background

KPN Netherlands has ‘Direct Buried Cables’, fiber cables lying in the ground without any protection. Direct buried cables are registered as Copper Cables contained by a Path Feature (fno=3500), so these cables themselves have no geometry. If you want to create real Fiber Cable-features with a geometry, you need to fetch and merge the geometries from the containing paths.

Direct Buried cables are identified by it’s ID attribute, the following Ad-Hoc query shows the paths containing a Direct Buried cable with id ‘407329’ :

select	GC_CONTAIN.g3e_ownerfno g3e_fno
	,	GC_CONTAIN.g3e_ownerfid g3e_fid
	,	3510 g3e_cno
	,	1 g3e_cid
	,	GC_CONTAIN.g3e_ownerfno
	,	GC_CONTAIN.g3e_fid cable_fid
	,	GC_CBL.total_size
	,	GC_CBL.id
	from GC_CONTAIN
	inner join GC_CBL on GC_CBL.g3e_fid = GC_CONTAIN.g3e_fid
	  and GC_CONTAIN.g3e_ownerfno = 3500
  where GC_CBL.id = '407329'

Running this query results in the following resultset:

Direct Buried cable in G/Technology

Direct Buried cable in G/Technology

The result shows the geometries of the Paths containing the cables, the actual Direct Buried cables themselves have no geometry. The first approach I took to merge these Path-geometries into a new Fiber Cable geometry was using the 'SDO_AGGR_UNION'-operator: like the following code snippet illustrates:

INSERT INTO B$GC_FCBL_L ( G3E_ID, G3E_FNO, G3E_FID, G3E_CNO, G3E_CID, G3E_GEOMETRY )
SELECT gc_fcbl_l_seq.nextval, 7200, 64169619, 7210, 1, geom
FROM
(
  select SDO_AGGR_UNION( SDOAGGRTYPE( B$GC_PATH_L.g3e_geometry, cTOLERANCE)) geom from B$GC_PATH_L
  WHERE g3e_fid IN (
  SELECT DISTINCT gc_contain.g3e_ownerfid
    FROM gc_cbl
      INNER JOIN gc_contain ON gc_contain.g3e_fid = gc_cbl.g3e_fid
	  AND gc_contain.g3e_ownerfno = 3500
      WHERE gc_cbl.id           = ‘407329’
	  AND gc_cbl.total_size  = 8
	  AND gc_cbl.composition = 'FIBER' )
);

This approach fails because the geometries may not be merged in the correct order, and for these cables the following error is generated when publishing data:

Started publish at 28/06/2017 14:11:02
Successful Connection GTFIBER

Full Publish VGC_FCBL_L
Querying component view at 28/06/2017 14:11:07
Populating DDC layer at 28/06/2017 14:11:13
6 number of rows in VGC_FCBL_L
Successfully published VGC_FCBL_L in C:\Mapfiles\FOWProd\Data\FiberCable.ddc at 28/06/2017 14:11:13
VGC_FCBL_L has 3 geometry errors. These follow:
1) Invalid geometry for component view VGC_FCBL_L, FID 64169619, CID 1, and LTT_ID 0 - No geometry was produced, probably because of a mismatch of G3E_COMPONENT.G3E_GEOMETRYTYPE and the actual geometry, or because of an un-supported SDO_GType.
2) Invalid geometry for component view VGC_FCBL_L, FID 64169620, CID 1, and LTT_ID 0 - No geometry was produced, probably because of a mismatch of G3E_COMPONENT.G3E_GEOMETRYTYPE and the actual geometry, or because of an un-supported SDO_GType.
3) Invalid geometry for component view VGC_FCBL_L, FID 64169622, CID 1, and LTT_ID 0 - No geometry was produced, probably because of a mismatch of G3E_COMPONENT.G3E_GEOMETRYTYPE and the actual geometry, or because of an un-supported SDO_GType.

The actual data in Oracle looks like this :

Oracle data

Oracle data

The 3006-types fail when publishing, the 3002-types are OK. For this migration to work, a geometry-sorting algorithm is needed and that’s why I created the ‘SDE_MERGE’-function.

The SDE_MERGE-code has been executed on actual production data, and from a total of 1874 Direct Buried cables, 1660 could be created. The failures were caused by missing gaps in segments and data needs to be corrected.

hope this helps, Stephan

Posted in G/Technology, Oracle, Oracle Spatial, PL/SQL | Tagged , , , , , | 4 Comments

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT 3D bug workaround

The SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function fails for 3D Compound line strings geometries as discussed over here. This is illustrated by the following sample:

2d:

with t1 as
(
select 1 id, SDO_GEOMETRY( 2002,NULL,NULL, 
         SDO_ELEM_INFO_ARRAY(1,4,2, 1,2,1, 3,2,2), 
         SDO_ORDINATE_ARRAY (       10,10, 10,14,6,10,14,10)) geom 
from dual
)
select 	id
      , CASE g.geom.st_isvalid()
          when 1 then 'valid'
          when 0 then 'invalid'
        END validness
      ,	sdo_geom.validate_geometry_with_context( theGeometry => g.geom, tolerance => 0.001) reason 
from t1 g;

produces:

ID VALIDNESS REASON
1  valid     TRUE

While the same geometry in 3d is considered invalid:

with t1 as
(
select 1 id, SDO_GEOMETRY( 3002,NULL,NULL, 
         SDO_ELEM_INFO_ARRAY(1,4,2, 1,2,1,   4,2,2), 
         SDO_ORDINATE_ARRAY(        10,10,0, 10,14,0,  6,10,0,  14,10,0)) geom 
from dual 
)
select 	id
      , CASE g.geom.st_isvalid()
        when 1 then 'valid'
        when 0 then 'invalid'
        END validness
     , sdo_geom.validate_geometry_with_context( theGeometry => g.geom, tolerance => 0.001) reason 
from t1 g;
ID VALIDNESS REASON
1  invalid   54530 Point:0,Edge:1,

A Quick workaround for this is to convert the geometry to 2d:

with t1 as
(
select 1 id, SDO_GEOMETRY( 3002,NULL,NULL, 
         SDO_ELEM_INFO_ARRAY(1,4,2, 1,2,1,   4,2,2), 
         SDO_ORDINATE_ARRAY(        10,10,0, 10,14,0,  6,10,0,  14,10,0)) geom 
from dual 
)
select 	id
      , CASE SDO_CS.MAKE_2D( geom).st_isvalid()
        when 1 then 'valid'
        when 0 then 'invalid'
        END validness
    ,	sdo_geom.validate_geometry_with_context( theGeometry => SDO_CS.MAKE_2D( g.geom), tolerance => 0.001) reason 
from t1 g;

result:

ID VALIDNESS REASON
1  valid     TRUE

hope this helps, Stephan

Posted in Oracle, Oracle Spatial | 1 Comment

Invalid Fiber Couplers

Fiber Couplers are used to connect Fiber Inner Ducts in a Fiber Branch Enclosure. You can connect Fiber Inner Ducts using the Fiber Feature Editor by selecting the two Fiber Inner Ducts and choosing the ‘Couple’-icon :

Connecting Fiber Inner Ducts

Connecting Fiber Inner Ducts

This will connect the 2 selected Fiber Inner ducts using a Fiber Coupler. After coupling the Fiber Inner Ducts the picture looks like this :

Connected Inner Ducts

Connected Inner Ducts

The two selected Fiber Inner Ducts are now connected via a Fiber Coupler which you can see in Feature Explorer if you go to the connected features of one of the Fiber Inner Ducts :

Explorer feature in Fiber Feature Editor

Explore feature in Fiber Feature Editor

Explored feature in Feature Editor

Explored feature in Feature Editor

Fiber Couplers are only allowed to be connected to Fiber Inner Ducts and they can only exist in the system when they have a connection. The following Fiber Couplers are invalid :

  • Fiber Couplers with more then 2 connections
  • Fiber Couplers with a single connection
  • Fiber Couplers without a connection
  • Fiber Couplers connected to other features then Fiber Inner Ducts

The following AdHoc-query will display all Fiber Couplers with invalid connections :

with T as
(      
select 	4300 		g3e_fno
	,	a.g3e_fid
	,	4301 		g3e_cno
	,	1 			g3e_cid	
	,	count(*) 	cnt	 
	from gc_ne_connect a
		inner join gc_ne_connect b on ( (b.node1_id = a.node1_id) or ( b.node2_id = a.node1_id) )
			and b.g3e_fno != 4300
	where a.g3e_fno = 4300 
		and a.node1_id != 0 
		and a.node2_id != 0
		group by a.g3e_fid
UNION
select 4300	g3e_fno
	,	g3e_fid
    ,	4301 g3e_cno
    ,	1	g3e_cid
    , 0 cnd
    from gc_ne_connect
    where g3e_fno = 4300 
    and node1_id = 0 and node2_id = 0
)select g3e_fno
	,	g3e_fid
    ,	g3e_cno
    ,	g3e_cid
    ,	cnt from t 
    where cnt != 2
    order by cnt desc

The result looks like this :

invalid Fiber Couplers

invalid Fiber Couplers

Hope this helps, Stephan

Posted in G/Technology, Intergraph | Leave a comment

AdHoc Queries

Introduction

G/Technology provides functionality to run dynamic queries, the so called ‘Ad-Hoc queries’. A Large telecom provider in the Netherlands is moving Fiber Cables from CRAMER to FOW and AdHoc-queries provide a great tool to analyze and visualize migrated data

At this provider Fiber Cables are contained by Fiber Inner Ducts who themselves are contained by Fiber Ducts. A fiber Duct has a Tag marked on it a.k.a. B217982 so workers can identify a Duct. Fiber Ducts go from Fiber Branch Enclosure to Fiber Branch Enclosure, Fiber Inner Ducts also do. Fiber Cables go from Fiber Splice Enclosures to Fiber Splice Enclosures. Fiber Branch Enclosures contain Fiber Splice Enclosures. This is illustrated by the following figure:

Fiber Cables

Fiber Cables

Click here for the legend.

This network is registered in 2 systems, CRAMER NIM (CRAMER Network Inventory Management) and GEOS FOW (GTechnology Fiber Optic Works), but Fiber Cables are registered only in NIM. This is illustrated by the next figures:

NIM-GEOS

NIM-GEOS

Fiber Cables are only registred in NIM, not in GEOS FOW. To enable better management of Fiber assets, registration of Fiber Cables need to be moved from NIM to GEOS FOW. Fiber Branch- and Fiber Splice Enclosures have an asset-id which is a number like ‘1001’ Fiber Cables have a name like ‘Hd-Hd 1’. This name is also maintained with Fiber Inner Ducts in NIM and is used to put Fiber Cables from NIM into the right Fiber Inner Duct in GEOS FOW. In the current GEOS FOW system where there are no Fiber Cables (yet) present, this looks like this.

The Fiber Inner Ducts with the Fiber Cable name on it are indicated in red and are contained in a Duct. You can also see that the ‘Contains’-node of the Fiber Inner Ducts do not have a value, but this is where migrated Fiber Cables will appear.

When all these assets are registered correctly, Fiber Cables can be migrated from NIM to FOW. Both systems NIM and GEOS FOW are running for several years so mismatches between the 2 systems exists and not all Fiber Cables can be migrated correctly. To analyze and visualize the quality of migration, Ad-Hoc queries play an important role.

Fiber Inner Ducts without cables

One of the queries giving insight in the data migration has the following requirements: Show all Fiber Ducts with a Fiber Inner Duct in it who don’t have a cable inside it. If the ‘CABLE_NAME’ attribute of these ducts had been set, then they should have a cable inside it and are not reserved stock aka. reserved for future use. In FOW Fiber Inner Ducts (fno=4100) have a ‘Contained By’ relation with Fiber Ducts (fno=4000), and Fiber Cables (fno=7200) also have a ‘Contained By’ relation with Fiber Inner Ducts. Using this relation, the following Ad-Hoc query lists all Fiber Ducts with Fiber Inner Ducts in it without a cable :

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
		and rownum < 100
	and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)

You can run this query using the ‘Ad Hoc’-query wizard (click here for a video) and after some scrolling the output may look like this :

Basic AdHoc Query

Basic AdHoc Query

The Query Name is added to the ‘Queries’-node in the Legend and the extends of the Netherlands is shown. The query has run successful, and there are many Fiber Inner Ducts without a Fiber Cable in it but we cannot really see them without turning off all the items in the legend and then zooming in into one of the items of the resultset.

Notes:

  • Before creating your first Ad-Hoc query, you need to define an ‘Area Of Interest’
  • We are limiting the output to 100 rows
  • Ad-Hoc queries always need to output at least the following attributes : G3E_FNO, g3E_FID, G3E_CNO & G3E_CID. These attributes enable selection of separate features from the result set and should point to a graphical components for selection in a Map window
  • AdHoc-queries are saved in a Workspace, when creating a new Workspace previous Ad-Hoc are not available
  • When creating AdHoc-queries the feature selected is just a placeholder, but the Wizard only continues to the second screen if you select a feature. Once you have done that you can query any feature
  • You can change the appearance of your result set even after running it using Display Control
  • Save and test your query outside G/Technology for easy development

Avoid joints

While the query used does its work, table GC_FDUCT_L is joined to get the Graphic Lines in the system representing Fiber Ducts. Since the Feature- & Component numbers for this feature don’t change and are required, non-repeating components we can rewrite the query as follows:

select  4000 g3e_fno
	, a.g3e_ownerfid g3e_fid
	, 4010 g3e_cno
	, 1 g3e_cid
	, a.g3e_fid	fid_inner_duct
	, gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
		and rownum < 100
	and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)

The necessary keys g3e_fno, g3e_cno & g3e_cid are still present but are now created using hardcoded value instead of SQL-joins, only the value for g3e_fid is variable but is now fetched from GC_CONTAIN. In this approach, table GC_FDUCT_L is no longer required.

Output results to a DataTable

This first queries showed we have some problems with our data migration, but the results are not really in a readable format. If you have a query with multiple results like this one, it may be convenient to output the results to a Datatable.

Output to datatable

If you output results to a datatable you can easily do the following:

  • Fit a feature
  • Select a feature in Feature Explorer
  • Export results

Note: If your result set is very large, it is better to export your results using a dedicated SQL-tool.

Custom ‘Areas Of Interest’

When creating an AdHoc-query, you can limit the results to be contained in a predefined area, the so called ‘Areas Of Interest’ (AOI). If you do so, G/Technology will first run a dedicated query to fetch features from your active AOI and then add some dedicated code after your query. If we take our first query:

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
	and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)

will be changed to:

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
	and not exists (select g3e_fid from gc_contain b , AOIQUERYRESULT AQR WHERE (GC_FDUCT.G3E_FID = AQR.G3E_FID AND AQR.G3E_USERNAME='GTFIBER' AND AQR.G3E_QUERYNAME='aa') AND (b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid))

This generates an Oracle error. Creating an Ad-Hoc query using an AOI can be hard to get working, but you can use Oracle Spatial to create your own ‘Areas Of Interest’. This feature is called ‘CLLI Boundary’ is used to create Areas of Interest, but we can also use it in Oracle Spatial to limit our query to only process a given area called ‘Hd-C’:

select  gc_fduct_l.g3e_fno
	,	gc_fduct_l.g3e_fid
	,	gc_fduct_l.g3e_cno
	,	gc_fduct_l.g3e_cid
	,	a.g3e_fid	fid_inner_duct
	,	gc_netelem.cable_name	
	from gc_contain a
		inner join gc_netelem on gc_netelem.g3e_fid = a.g3e_fid
		inner join gc_fduct_l on gc_fduct_l.g3e_fid = a.g3e_ownerfid
		, gc_bnd_p	
	where a.g3e_fno = 4100
		and a.g3e_ownerfno = 4000
		and gc_netelem.cable_name is not null
		and gc_netelem.g3e_fno = 4100
		and gc_bnd_p.feature_type = 'CLLI'		
		and gc_bnd_p.wc_clli = 'Hd-C'
		and not exists (select g3e_fid from gc_contain b where b.g3e_fno = 7200 and b.g3e_ownerfid = a.g3e_fid)
		and SDO_GEOM.RELATE( gc_bnd_p.g3e_geometry, 'ANYINTERACT', gc_fduct_l.g3e_geometry, 0.1) = 'TRUE'

The SDO_GEOM.RELATE Oracle Spatial operator is used in the where clause to get all the Fiber Ducts contained in area ‘Hd-C’.

Finding features with carriage returns in Attribute values

When users are entering attribute values using Feature Explorer they may be expecting G/Technology saving values when entering ‘Enter’. GTech will not do this, but will instead store a Carriage return/Line Feed with the attribute value. This will lead to pollution of attribute values in the database and give mismatches between tag-id’s from ducts coming from NIM and ducts in GTech. During the migration we might for example be looking for two ducts with Tag-id ‘B217982’, but what we find I ‘B217982’ and ‘B217982Chr(10)Chr(13)’. The following Ad-Hoc query will show all Fiber Duct features (fno=4000) who’s cable_name attribute has the ‘CHR(13)CHR(10)’ character combination (CHR(13)=Carriage Return, CHR(10)=Line Feed) :

select g3e_fno
     , g3e_fid
	 , g3e_cno
	 , g3e_cid
	 , cable_name
from gc_netelem	
where gc_netelem.g3e_fno = 4000
and regexp_like( trim(cable_name), CHR (13) || CHR(10) )

Click here for a Video of creating and running this Query.

Hope this Helps. Stephan

Posted in G/Technology, Intergraph | Tagged , , | Leave a comment