1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| SELECT /*ORDERED*/
S.GID,
S.ID_EMPRISE,
S.NOM_EMPRISE,
S.LG,
S.MAX_LG,
R_VOIE.CODCOM,
R_VOIE.CODRUE
FROM ( (SELECT T.GID,
E.GID AS ID_EMPRISE,
E."nom" AS NOM_EMPRISE,
E."commune" AS COMMUNE_EMPRISE,
SDO_GEOM.
SDO_LENGTH (
SDO_GEOM.
SDO_INTERSECTION (E.GEOMETRY, T.GEOMETRY, 0.1),
0.1)
AS LG,
MAX (
SDO_GEOM.
SDO_LENGTH (
SDO_GEOM.
SDO_INTERSECTION (E.GEOMETRY, T.GEOMETRY, 0.1),
0.1))
OVER (PARTITION BY E.GID)
AS MAX_LG
FROM E_EMPCTR E, R_TRONCO T
WHERE SDO_ANYINTERACT (E.GEOMETRY, T.GEOMETRY) = 'TRUE'
AND SDO_RELATE (
E.Geometry,
T.Geometry,
'mask=covers+OVERLAPBDYDISJOINT+CONTAINS') =
'TRUE'-- AND E.GID = 1239
) S
INNER JOIN
R_TRONCO_RELATION
ON S.GID = R_TRONCO_RELATION.GID_D)
INNER JOIN
R_VOIE
ON ( R_TRONCO_RELATION.GID_A = R_VOIE.GID
AND R_TRONCO_RELATION.CLASS = 'R_VOIE'
AND R_TRONCO_RELATION."NAME" = 'referent')
WHERE LG = MAX_LG |
Partager