Ray Chilcote, PG, GISP NCDOT IT/GIS

Overview  NCDOT data  Changes/growth going to ROME (R&H)

 Producing Road Characteristics  Old method  New method  Use of views  Dynamic labeling (measures)  Storage, speed

Dynamic

 Bonus round

Speed Automation

NCDOT Data – before migration  86,000 individual routes  70+ linear event tables  Overnight process to merge Routes and Events

NCDOT Data – after migration  146,000 individual routes  Added local, non-system

 ~50 linear event tables  Condensed (example: Addition, Addition Date)

 Implement history  Closer to real time

Data Output Considerations

 Database architecture  Speed

 Maintenance  Change management  Documentation  QC

Edit environment output

Original Challenge Intersect several event tables (~70) into one. Measures are not always coincident.

Duc Tran Author: Duc-Tran date-written: Jul-10-08 select * from s_medtyp order by 1,2; FTSEG FRM_PCT_QTY TO_PCT_QTY MEDTYP ---------- ----------- ---------- ---------1 0 .1 1 1 .1 .2 2 1 .3 1 8 select * from s_medwidth order by 1,2; FTSEG FRM_PCT_QTY TO_PCT_QTY MEDWITH ---------- ----------- ---------- ---------1 0 .2 20 1 .2 .5 30 1 .5 .6 20 1 .6 1 70 select * from s_speed order by 1,2; FTSEG FRM_PCT_QTY TO_PCT_QTY SPEED ---------- ----------- ---------- ---------1 0 .2 25 1 .4 1 70 2 0 1 75

DECLARE list_fld VARCHAR2(32767); schema_nm VARCHAR2(30); list_tbl_fld VARCHAR2(32767); sel_dstnct_tbl VARCHAR2(32767); union_blk VARCHAR2(32767); last_tbl VARCHAR2(30); where_outer_blk VARCHAR2(32767); between_blk VARCHAR2(32767); plsql_blk CLOB; high_g1 number; low_g1 number;

PLSQL writing PLSQL (Oracle) (Oracle)

CURSOR get_all_recs IS select ATRBT_TBL_NM, RC_COL_NM, ATRBT_ID from gis_arid.fpl_atrbt_metadata a, all_tables b where a.ATRBT_TBL_NM = b.TABLE_NAME and a.ATRBT_TBL_OWNR_NM = b.OWNER and a.IS_EVNT_IND_NM = 'YES'; BEGIN schema_nm := 'GIS_ARID'; FOR rec IN get_all_recs LOOP list_fld := list_fld || rec.RC_COL_NM || ', '; list_tbl_fld := list_tbl_fld || ' b' || rec.ATRBT_ID || '.' || rec.RC_COL_NM || ', '; sel_dstnct_tbl := sel_dstnct_tbl || '(SELECT DISTINCT G1_FTSEG_ID, FRM_EVNT_PCT, TO_EVNT_PCT, ' || rec.RC_COL_NM || ' FROM ' || schema_nm || '.' || rec.ATRBT_TBL_NM || ') b' || rec.ATRBT_ID || ','; union_blk := union_blk || 'SELECT DISTINCT G1_FTSEG_ID, TO_EVNT_PCT FROM ' || schema_nm || '.' ||rec.ATRBT_TBL_NM || ' UNION '; union_blk := union_blk || 'SELECT DISTINCT G1_FTSEG_ID, FRM_EVNT_PCT FROM ' || schema_nm || '.' ||rec.ATRBT_TBL_NM || ' UNION '; last_tbl := schema_nm || '.' ||rec.ATRBT_TBL_NM; where_outer_blk := where_outer_blk || 'a.G1_FTSEG_ID = b' || rec.ATRBT_ID || '.G1_FTSEG_ID(+) and '; between_blk := between_blk || 'a.frm + .0000005 between b' || rec.ATRBT_ID || '.FRM_EVNT_PCT(+) and b' || rec.ATRBT_ID || '.TO_EVNT_PCT(+) and '; END LOOP; --Cleanup unwanted TEXT at end of strings list_fld := RTRIM(list_fld, ', '); list_tbl_fld := RTRIM(list_tbl_fld, ', '); between_blk := RTRIM(between_blk, 'and ');

-- BUILD STATEMENT plsql_blk := 'insert into plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk || plsql_blk := plsql_blk ||

rlchilcote.fpl_rd_char (g1_ftseg_id, frm_evnt_pct, to_evnt_pct, ' || list_fld || ')'; 'select distinct a.G1_FTSEG_ID,a.frm fpc,a.two tpc,'; list_tbl_fld || ' FROM '; sel_dstnct_tbl; '(select distinct a.G1_FTSEG_ID,a.frm,b.two from '; '(select distinct G1_FTSEG_ID,TO_EVNT_PCT frm,rownum n1 from ('; union_blk || ' SELECT G1_FTSEG_ID, min(FRM_EVNT_PCT) mini FROM ' || last_tbl || ' GROUP BY G1_FTSEG_ID)) a, '; '(select distinct G1_FTSEG_ID,TO_EVNT_PCT two,rownum n2 from ('; union_blk || ' SELECT G1_FTSEG_ID, min(FRM_EVNT_PCT) mini FROM ' || last_tbl || ' GROUP BY G1_FTSEG_ID)) b '; 'where a.G1_FTSEG_ID=b.G1_FTSEG_ID and a.n1+1=b.n2) a '; 'where ' || where_outer_blk; 'a.G1_FTSEG_ID BETWEEN 150000 AND 200000 AND '; between_blk || '; ';

plsql_blk := plsql_blk || 'COMMIT;';

EXECUTE IMMEDIATE plsql_blk; COMMIT; END;

…

Aggregation Query

Python

(SQL Server)

Formatted, produces a single, 4500 line long sql statement. 45 pages. Front and back.

Results 1 Single semi-dynamic SQL query Produces 1 table Time required: 40 minutes (priceless)

~ 95 event fields

• • •

… 566k rows (unique Route Event Characteristic sections, original edit sections dissolved if same value)

Challenge: Show locked routes • • • •

ArcMap Query Layer Spatial View in Database Dynamic Where Clause Other • Custom GP (created each time?)

ArcMap Query Layer

Honors ArcSDE Versions!

Does NOT work with RCE

View in Database

Actually, quite nice and simple. Automagically shows up as a feature class in SDE

Make Uber-Dynamic Where Clause You are here

MilePoint_ID in (select distinct RouteID from GdbRomeEdit.Rome.Lrs_Locks) And (FromDate is null or FromDateCURRENT_TIMESTAMP)

Uber simple. Nothing to create in Database. Same query. Directly created into a service.

Show (Label) Measure Length

Show Measure Length

Storage, Speed

• Measure value is multiplied by the Resolution to create the stored integer • Several issues: • Number in not number out • (old feature creep?) • Database size impacted • Speed impacted

Storage, Speed

How issue came into focus Show of hands, who has had issues with gaps and overlaps? Found that Beginning/End Measure in the shape was not the same value as the FromMeasure and ToMeasure in the Roads and Highways Event data.

NHS Before Fix (6 decimal) NHS Before fix (15 decimal)

NHS After fix (15 decimal)

Spatially, verticies contiguous. So decided to fix Resolution, then recalc all FromMeasure/ToMeasure/Measure fields

Before

QA Gray is GOOD!

• Python script written to order Route/Measure (field). • Then find gaps with a tolerance. • When gap found, list the first record.

After

Storage Gains 14.7 million verticies, each with a measure. Solely changing the database M resolution…

FromMeasure and ToMeasure field values now equal Shape measures.

In Summary: Don’t Forget…    

Gather requirements first (good requirements) Attribute indexing View or query/create subset? (balance ROI) Issue Analysis (execution plan, database tracing, Perfmon)  Too often, system is fast enough. Slowing system. Generally, the problem is not server

horsepower.  Query order matters. A lot.  Are NULLs causing issues? Reconciles, statistics, etc.

 Good database design  Utilize database strengths  Separate editing and publication requirements  Good to have editing look like publication, but not at the sacrifice of editing woes.

Bonus Round  Speed  Indexes  

Utilize! Nulls are bad  Database constraints, if possible  If Sql Server, look up Filtered Index

 Compress  If possible in your workflow, compress. Often.  Reconcile often  Gather statistics

 Metadata  Say what is this doing here?

Versioning Artwork

Questions? Contact info: [email protected]

Balance ROI Going too far

Example:  Challenge: Place Road Characteristics (events) on network (route)  Problem: Road Characteristics do not begin/end on route (network) nodes  Attempt to find measure on route where there is no node (interpolate) in SQL  Worked SQL query to return route, measure, X, Y, elevation for nodes  Interpolated between previous/next record with LEAD/LAG  13.7 million nodes. Need to store. t = time requirement to calc/update

 Why do all this? Make Features Route tool does this activity in < 5

minutes for the entire state. Natively exports in a few more minutes.

Too far SELECT b.OBJECTID ,a.milepoint_id RouteID ,a.shape.STPointN(b.objectid).M Measure ,a.shape.STPointN(b.objectid).STX X ,a.shape.STPointN(b.objectid).STY Y ,a.shape.STPointN(b.objectid).Z Z ,geometry::STGeomFromText('POINT (' + CONVERT(varchar(20), CONVERT(varchar(20), CONVERT(varchar(20), CONVERT(varchar(20), , 2264 ) AS g FROM gdbgisustage.rome.lrsn_milepoint AS a, gdbgisustage.rome.lrsn_milepoint AS b WHERE b.objectid BETWEEN 1 AND a.shape.STNumPoints() AND a.milepoint_id = '20000052029'

a.shape.STPointN(b.objectid).STX) a.shape.STPointN(b.objectid).STY) a.shape.STPointN(b.objectid).Z) + a.shape.STPointN(b.objectid).M) +

+ ' ' + + ' ' + ' ' + ')'

Geodatabase Architecture