Search Results sys_il0000083003c00010




Overview
Explore in AI Chat

Overview

CSF.CSF_LF_ROADSEGMENTS is a spatial reference table within the Oracle E-Business Suite Location Foundation (CSF) schema. It stores the navigable road network used by Oracle's location-based and geographic information functionality. According to the ETRM definition, a Road Segment represents a navigable feature such as a Road, Ferry, or Walkway. Each road segment is bounded by two nodes and carries attributes that describe its geometry, length, and time zone context. The table conforms to GDF 3.0 (Geographic Data Files) semantics — the ROADSEGMENT_ID is explicitly documented as the GDF3.0 Edge ID, confirming the table's role as the canonical edge store for the road topology.

The object resides in the APPS_TS_MEDIA tablespace, which is consistent with its spatial (SDO_GEOMETRY) payload. It depends on MDSYS.SDO_GEOMETRY and the SYS.STANDARD package, confirming it is an Oracle Spatial-enabled table. From a Data Vault modeling perspective, the metadata's relationship classification identifies this table as a hub — it holds a stable, unique business key (ROADSEGMENT_ID) that is referenced by multiple downstream dependent tables. Treating it as a hub is a modeling suggestion, not a physical constraint enforced by EBS.

Key Information Stored

The table comprises five documented columns. The most important are:

  • ROADSEGMENT_ID (NUMBER(30)) — The permanent identifier for the road segment, equivalent to the GDF 3.0 Edge ID. This is the primary key, enforced by the constraint CSF_LF_ROADSEGMENTS_PK. It is the surrogate key and the join column used by every dependent table.
  • ROADSEGMENT_GEOMETRY (SDO_GEOMETRY) — The spatial representation of the segment, stored natively via Oracle Spatial. This column drives the LOB indexes SYS_IL0000083003C00010$$ and SYS_IL0000083003C00011$$ in APPS_TS_MEDIA.
  • ROADSEGMENT_LENGTH (NUMBER) — The measured length of the segment, used for routing distance and network-cost calculations.
  • TIME_ZONE (NUMBER) — The time zone context associated with the segment, relevant for temporal routing and scheduling.
  • SECURITY_GROUP_ID (NUMBER) — The identifier used when running in hosted (multi-tenant) mode. It is a foreign key to FND_SECURITY_GROUPS and enforces the MLS/hosted-mode data partitioning.

The two unique LOB indexes documented (SYS_IL0000083003C00010$$ and SYS_IL0000083003C00011$$) are system-generated indexes supporting the SDO_GEOMETRY storage and are not business-key candidates. The sole business-key candidate is ROADSEGMENT_ID.

Common Use Cases and Queries

Typical scenarios include routing and navigation computations, geocoding, and point-of-interest (POI) proximity analysis. A common query retrieves the geometry and length of a segment by its GDF edge ID:

  • SELECT roadsegment_id, roadsegment_length, time_zone, roadsegment_geometry FROM csf.csf_lf_roadsegments WHERE roadsegment_id = :id;
  • Spatial proximity queries using SDO_GEOMETRY operators (SDO_WITHIN_DISTANCE, SDO_RELATE) to find segments near a coordinate.
  • Network aggregation by time zone: SELECT time_zone, COUNT(*), SUM(roadsegment_length) FROM csf.csf_lf_roadsegments GROUP BY time_zone;
  • Joining to dependent tables such as CSF_LF_ROADSEGM_NAMES to resolve the human-readable name of a segment.

Because SECURITY_GROUP_ID participates in hosted-mode isolation, production queries should generally be executed through the CSF_LF_ROADSEGMENTS APPS synonym rather than the base table, allowing the application's security policy to apply.

Related Objects

CSF_LF_ROADSEGMENTS is referenced by several dependent tables, all joining on ROADSEGMENT_ID:

  • CSF_LF_BLOCKS — Blocks that reference a road segment.
  • CSF_LF_POIS — Points of interest located on a segment.
  • CSF_LF_ROADSEGM_NAMES — Names/labels for segments.
  • CSF_LF_ROADSEGM_PLACES — Place associations for segments.
  • CSF_LF_ROADSEGM_POSTS — Postal/address posts associated with segments.
  • FND_SECURITY_GROUPS — Referenced through SECURITY_GROUP_ID for hosted-mode security.
  • APPS.CSF_LF_ROADSEGMENTS — The synonym exposed to the application layer.
  • MDSYS.SDO_GEOMETRY — The Oracle Spatial type dependency underpinning ROADSEGMENT_GEOMETRY.

Together these objects form the spatial road network used by Oracle's location foundation services.