How to derive Basic Height from ADAS_LINK_GEOMETRY
Symptoms
--------
Customers may experience one or more of the following:
The BASIC_HEIGHT table is no longer available in the database schema.
Existing workflows or reports require Basic Height information for a link.
Queries that previously depended on BASIC_HEIGHT no longer return results.
Users need minimum, maximum, average, reference-node, or non-reference-node elevation values for ADAS links.
Migration activities require reproducing historical Basic Height values from current data sources.
Answer
------
If the BASIC_HEIGHT table is no longer available, the required height-related values can be derived directly from the ADAS_LINK_GEOMETRY table. The calculation uses the Z_COORD values stored for each link geometry point and returns the reference node height, non-reference node height, minimum height, maximum height, and average height for each link.
Applicable Product
------------------
ADAS data stored in Oracle Database
ADAS_LINK_GEOMETRY table
Root Cause
----------
The BASIC_HEIGHT table was discontinued as part of a schema evolution. Height information continues to exist within the ADAS_LINK_GEOMETRY dataset and can be calculated from the geometry records instead of being retrieved from a dedicated table.
Impact
------
Applications, analytics processes, migration scripts, and customer reports that previously queried BASIC_HEIGHT may require an alternative method to obtain equivalent height information. Without recalculating these values, dependent processes may fail or return incomplete results.
Recommended Action
------------------
Use the following Oracle SQL query to derive the height statistics from ADAS_LINK_GEOMETRY:
WITH REF_NODE_HEIGHT AS (
SELECT LINK_ID,
Z_COORD AS REF_NODE_ZCOORD
FROM ADAS_LINK_GEOMETRY
WHERE SEQ_NUM = 0
),
NON_REF_HEIGHT AS (
SELECT LINK_ID,
Z_COORD AS NREF_NODE_ZCOORD
FROM ADAS_LINK_GEOMETRY
WHERE SEQ_NUM = 999999
),
LINK_STATS AS (
SELECT LINK_ID,
MIN(Z_COORD) AS LINK_MINIMUM_HEIGHT,
MAX(Z_COORD) AS LINK_MAXIMUM_HEIGHT,
ROUND(AVG(Z_COORD), 0) AS LINK_AVERAGE_HEIGHT
FROM ADAS_LINK_GEOMETRY LG
WHERE NOT EXISTS (
SELECT LINK_ID
FROM ADAS_LINK_GEOMETRY LG2
WHERE LG2.LINK_ID = LG.LINK_ID
AND LG2.Z_COORD IS NULL
)
GROUP BY LINK_ID
)
SELECT L.LINK_ID,
R.REF_NODE_ZCOORD,
N.NREF_NODE_ZCOORD,
L.LINK_MINIMUM_HEIGHT,
L.LINK_MAXIMUM_HEIGHT,
L.LINK_AVERAGE_HEIGHT
FROM REF_NODE_HEIGHT R,
NON_REF_HEIGHT N,
LINK_STATS L
WHERE L.LINK_ID = R.LINK_ID
AND L.LINK_ID = N.LINK_ID;
Expected Behavior
-----------------
Height-related values are derived from ADAS_LINK_GEOMETRY.
* The query returns one result set per LINK_ID containing:
+ Reference node height
+ Non-reference node height
+ Minimum link height
+ Maximum link height
+ Average link height
Unexpected Behavior
-------------------
* Missing results for links with incomplete geometry records.
* NULL Z_COORD values causing links to be excluded from the calculated statistics.
* Differences from historical outputs if previous custom calculations were used instead of the standard derivation logic.
Keywords / Tags
---------------
ADAS_LINK_GEOMETRY, BASIC_HEIGHT, Basic Height, Z_COORD, LINK_ID, Oracle SQL, ADAS database, elevation data, reference node height, non-reference node height, link minimum height, link maximum height, link average height, schema migration, height calculation
Updated 3 days ago