[Oracle] Get single (non collection) geometry from ST_AGGR_UNION

[PlinioSantos]

Hi,

I have an oracle database (11g R2) which each register is a piece of one street. For instance:

SELECT ID_LOGRADOURO, SDE.ST_GEOMETRY_OPERATORS.ST_ASTEXT_F(SHAPE)
FROM GRF_TRECHO_LIN_24
ORDER BY ID_LOGRADOURO;

Where ID_LOGRADOURO identifies uniquely an street.

I'm trying to select a union of all pieces of the same streets in one geometry:

SELECT MAX(OBJECTID) OBJECTID, ID_LOGRADOURO, COUNT(OBJECTID) COUNT, SDE.ST_GEOMETRY_OPERATORS.ST_ASTEXT_F(SDE.ST_AGGR_UNION(SHAPE))
FROM GRF_TRECHO_LIN_24
GROUP BY ID_LOGRADOURO
ORDER BY ID_LOGRADOURO;

Realise that the aggregate function returns a multilinestring if the stree if composed by more than one piece.

I'm looking for a way to get a linestring regardless the street has one or more pieces. How can I do that?

[PlinioSantos · Accepted solution]

I figured out that I can get the MULTILINESTRING text and just:

1. replace the MULTILINESTRING for LINESTRING
2. do some adjustments
3. construct a linestring from text

This is how my query looks like:

SELECT MAX(OBJECTID) OBJECTID, ID_LOGRADOURO, COUNT(OBJECTID) COUNT_TRECHOS, SDE.ST_GEOMETRY_OPERATORS.ST_ASTEXT_F(SDE.ST_LINEFROMTEXT(replace(replace(replace(replace(SDE.ST_GEOMETRY_OPERATORS.ST_ASTEXT_F(SDE.ST_AGGR_UNION(SHAPE)),'((','('),'),(',','),'))',')'),'MULTILINESTRING','LINESTRING'), 587)) SHAPE
FROM GRF_TRECHO_LIN_24
GROUP BY ID_LOGRADOURO
ORDER BY ID_LOGRADOURO;