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:

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:

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 :

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.

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