-
Describe the differences between Route/Measure and Series/Station.
A Series is a segment of pipeline having continuous engineering stationing.
This is how a pipeline is represented on pipeline alignment sheets.
A Route is a collection of sequence series’ with continuous
cumulative stationing. This is used to eliminate pipeline equations
from footage calculations.
-
Where are regulators stored in PODS?
There is no table to explicitly store regulators
-
What does specification mean in the Valve table?I know it is tied
to a code lookup but we are at the point of needing to just understand
what this field means.
This is a manufacturer specification published by a standards organization
(i.e. ANSI – American National Standards Institute, ASTM –
American Society for Testing and Materials, etc.). It relates directly
to the range of operating pressures for which the valve is rated.
Please see below for sample domain codes:
ANSI 150
ANSI 300
ANSI 400
ANSI 600
ANSI 900
-
We see where to store the leak survey and history information. We
aren’t finding a spot to store the result of the leak i.e. Rupture,
Evacuation, etc.
The cause of a leak is stored in the LEAK.PRIMARY_CAUSE_CL column.
There is a foreign key relationship to the LEAK_CAUSE_CL table. Please
see below for sample domain codes:
| CODE |
DESCRIPTION |
| COR-EXTERNAL |
Corrosion External |
| COR-INTERNAL |
Corrosion Internal |
| EARTHMOVEMENT |
Natural Forces - Earth Movement |
| EQ-MALFUNCTION |
Equipment - Malfunction of Control/Relief Equipment |
| EQ-SEALFAIL |
Equipment - Seal Failure |
| EQ-STRP_BRKNPIPE |
Equipment- Threads Stripped, Broken Pipe Coupling |
| EXCAV-OPERATOR |
Excavation damage - Operator |
| EXCAV-THIRDPARTY |
Third-party damage |
| HIGHWINDS |
Natural Forces - Temperature - Other |
| HRF |
Heavy Rains or Flood |
| IO |
Incorrect Operation |
| LIGHTNING |
Natural Forces - Lightning |
| MF-BODYOFPIPE |
Material Failure - Body of Pipe (Dent, Gouge, Bend, Arc Bend,
Other) |
| MF-COMPONENT |
Material Failure - Component (Valve, Fitting, Vessel, Extruded
Outlet, Other) |
| MF-JOINT |
Material Failure - Joint (Gasket, O-Ring, Threads, Other) |
| MISC |
Miscellaneous |
| OFD-FIRE_EXPL |
Other Outside Force Damage - Fire or Explosion as primary
cause |
| RPD |
Other Outside Force Damage - Rupture of Previously Damaged
Pipe |
| TEMPERATURE |
Natural Forces - Temperature |
| UNKNOWN |
Unknown |
| VANDALISM |
Other Outside Force Damage - Vandalism |
| VEHICLE_DAMAGE |
Other Outside Force Damage - Vehicle related damage |
| WF-BUTT |
Weld Failure - Butt (Pipe, Fabrication, Other) |
| WF-FILLET |
Weld Failure - Fillet (Branch, Hot Tap, Fitting, Repair Sleeve,
Other) |
| WF-PIPESEAM |
Weld Failure - Pipe Seam (LF ERW, DSAW, Seamless, Flash Weld,
HF ERW) |
-
Will the ddl files work on other databases – particularly free
downloads?
The ddl files support SQLServer2000 and Oracle. A separate ddl is issued
for each RDBMS platform.
-
How many miles of pipeline is in my PODS database?
Use the following script to calculate the number of miles.
SELECT
CAST(SUM(ABS(SER.STATION_BEGIN - SER.STATION_END)) / 5280
AS NUMERIC(8,2))
FROM SERIES SER
INNER JOIN ROUTE RTE ON SER.ROUTE_ID = RTE.ROUTE_ID
INNER JOIN LINE LN ON LN.LINE_ID = RTE.ROUTE_ID
WHERE SER.CURRENT_INDICATOR_LF = 'Y'
-
How do I find orphaned records in the EVENT_RANGE table for the whole
database?
DECLARE @FID AS VARCHAR(16)
DECLARE @SQL AS VARCHAR(800)
DECLARE @FT AS VARCHAR(80)
DECLARE @ROWS AS INTEGER
DECLARE @LPCNT AS INTEGER
CREATE TABLE #TMPORPHAN
(
FEATURE_ID VARCHAR(16),
FEATURE_TABLE_NAME VARCHAR(80)
)
SET @LPCNT = 1
DECLARE FC CURSOR STATIC FOR
SELECT FEATURE_ID, TABLE_NAME FROM FEATURE_TABLE
OPEN FC
SET @ROWS = @@CURSOR_ROWS
WHILE @LPCNT <= @ROWS
BEGIN
FETCH NEXT FROM FC INTO @FID, @FT
SET @SQL =
'INSERT INTO #TMPORPHAN SELECT FEATURE_ID, EVENT_ID
FROM EVENT_RANGE
WHERE FEATURE_ID = ' + CHAR(39) + @FID +
CHAR(39) + ' AND EVENT_ID NOT IN
(SELECT EVENT_ID FROM ' + @FT + ')'
EXEC(@SQL)
SET @LPCNT = @LPCNT + 1
END
SELECT * FROM #TMPORPHAN
DROP TABLE #TMPORPHAN
CLOSE FC
DEALLOCATE FC
-
How can I look for orphaned records between the Event_Range table and the corresponding feature table?
This SQL code creates SQL code to check only features with data in them. The output is a list of SQL
statements that can be run in a SQL command window. The statement was designed to run on a Solaris running Oracle.
SELECT
'SELECT event_id FROM event_range a WHERE a.feature_id= ''' ||feature_id||
''' AND NOT EXISTS (SELECT 1 FROM '
||object_name|| ' b WHERE a.event_id=b.event_id);'
FROM all_objects e, feature_table c
WHERE e.owner= 'SCHEMA_OWNER'
AND e.object_type= 'TABLE'
AND e.object_name=c.table_name
AND (object_name IN (SELECT table_name FROM feature_table WHERE feature_id IN
(SELECT distinct feature_id FROM event_range)))
The text in blue never changes.
The text in red are variables that gets updated.
The text in black is the SQL code used to limit and select table_names.
OUTPUT IS A LIST OF SQL STATEMENTS
- SELECT event_id FROM event_range a WHERE a.feature_id=BRC AND NOT EXISTS (SELECT 1 FROM BRANCH_CONNECT b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=CAS AND NOT EXISTS (SELECT 1 FROM CASING b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=CBND AND NOT EXISTS (SELECT 1 FROM COUNTY_BOUNDARY b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=ANC AND NOT EXISTS (SELECT 1 FROM CP_ANC_TBL b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=HCAI AND NOT EXISTS (SELECT 1 FROM CP_HCA_INTERSECTION b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=DCOV AND NOT EXISTS (SELECT 1 FROM DEPTH_OF_COVER b WHERE a.event_id=b.event_id);
-
Explain route/measure, series/station, and station equation as defined using the diagrams below from
this document:
When field personnel replace pipe, they often add or remove pipe which changes the total length of the pipe. This change needs to be represented on the alignment sheets. In the old days, alignment sheets were difficult to change because they were done manually; therefore, changes were made with station equations.
The first example shows “B1000/A900” between S1 and S2. This means that 100 units (feet or meters) of pipe were added to the line which changed the total length of the line from 3500 units to 3600 units. Another 100 units of pipe were added at the second point between S2 and S3, changing the total length to 3700 units.
These changes require all the alignment sheets to be changed from the first S1/S2 point forward. This was difficult to do manually so the drafter used a station equation. At the point S1/S2, 100 units were added. The Station Equation is defined as B1000/A900 and is translated as “Back 1000/Ahead 900.” From the beginning of the pipe to the point S1/S2, the station remains the same (0 to 1000). From point S1/S2 to point S2/S3, the stationing changes. Previously it went from 1000 to 2000. With the addition of 100 units, it now goes from 900 to 2000. A station equation also defines the point S2/S3.
By adding the extra 200 units of pipe, the route increased from 3500 to 3700 units. The illustration has 3 series with stationing that goes from 0 to 1000, 900 to 2000, and 1900 to 3500. The natural consequence of the series is that you have overlapping numbers in the series but the route stationing is unique. Common vocabulary for the series is “Engineering Stationing” and the route is “Cumulative Stationing.”
To further explain: Series S1 contains stationing 900-1000 and Series S2 also contains stationing 900-1000. If you were to put a valve in at 950, you would have to know whether you were in S1 or S2. To show a valve at 950 in the first series, you would say the valve is at 950-S1 or 950-Route. If the valve is in the second series, the stationing would be 950-S2 or 1050-Route.
Some companies choose not to use Engineering Stationing so they only use the series stationing or route. Most companies still use the combination of engineering and route stationing because they have used it for so long.
How to make SQL code that creates SQL code. This particular statement was used by an operator for integrity checking to look for orphaned records between the event_range table and the corresponding feature table. This allowed GIS Analyst (James Bond 007) to check only feature tables with data in them. The output is a list of SQL statements that can be run in a SQL command window. This particular statement was run on a Solaris box running Oracle; potential results on a SQL server are unknown.
SELECT
'SELECT event_id FROM event_range a WHERE a.feature_id= ''' ||feature_id||
''' AND NOT EXISTS (SELECT 1 FROM '
||object_name|| ' b WHERE a.event_id=b.event_id);'
FROM all_objects e, feature_table c
WHERE e.owner= 'SCHEMA_OWNER'
AND e.object_type= 'TABLE'
AND e.object_name=c.table_name
AND (object_name IN (SELECT table_name FROM feature_table WHERE feature_id IN
(SELECT distinct feature_id FROM event_range)))
The text in blue never changes.
The text in red are variables that gets updated.
The text in black is the SQL code used to limit and select table_names.
OUTPUT IS A LIST OF SQL STATEMENTS
- SELECT event_id FROM event_range a WHERE a.feature_id=BRC AND NOT EXISTS (SELECT 1 FROM BRANCH_CONNECT b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=CAS AND NOT EXISTS (SELECT 1 FROM CASING b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=CBND AND NOT EXISTS (SELECT 1 FROM COUNTY_BOUNDARY b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=ANC AND NOT EXISTS (SELECT 1 FROM CP_ANC_TBL b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=HCAI AND NOT EXISTS (SELECT 1 FROM CP_HCA_INTERSECTION b WHERE a.event_id=b.event_id);
- SELECT event_id FROM event_range a WHERE a.feature_id=DCOV AND NOT EXISTS (SELECT 1 FROM DEPTH_OF_COVER b WHERE a.event_id=b.event_id);
