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

PlinioSantos — Sat, 11 Dec 2021 22:07:02 GMT

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?

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

PlinioSantos — Sat, 11 Dec 2021 22:07:05 GMT

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

replace the MULTILINESTRING for LINESTRING
do some adjustments
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;

topic Re: [Oracle] Get single (non collection) geometry from ST_AGGR_UNION in Developers Questions

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

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