[postgis-users] ST_Union() / Linestrings / Other ideas

[Florian Lohoff]

Hi,
i am doing Route Quality analysis since 2013 for OSM Data. For this i am
calculating ~40K routes every hour in a small part of Germany and compare the
individual routes with their times and length of the last OSM dataset and if
something changes i get sent an email. Analysis what has broken is than manual
labour.

I am now thinking further and i thought by comparing the whole grid
of roads taken by the routes with the one an hour ago should show
geometric changes (And there are other ideas)

So i am now trying to ST_Union() all linestrings i have calculated
into a MULTILINESTRING which is non redundant for further processing.
The problem i have is that this takes ~4 hours:

Whereas selection of rows without the ST_Union is pretty fast:

routeqa=#         select  route,
                checkedlast
        into    temp table t
        from    route r
        where   r.checkedlast = ( select max(checkedlast) from route );
SELECT 39554
Time: 3141.356 ms

This is the try of tonight:

routeqa=# insert into grid ( geom, checkedlast ) (
routeqa(#         select  ST_Union(route) geom,
routeqa(#                 checkedlast
routeqa(#         from    route r
routeqa(#         where   r.checkedlast = ( select max(checkedlast) from route )
routeqa(#         group by checkedlast
routeqa(#         );
INSERT 0 1
Time: 12310472.278 ms

So yes - this is compute intensive ;) most likely because they basically all overlap at
some point. Any ideas? Splitting all linestrings down to more segments which are only
2 nodes each and simply do a distinct on that?

I still on an "ancient" Stretch with postgis 2.3

ii  postgresql-9.6-postgis-2.3             2.3.1+dfsg-2                       amd64        Geographic objects support for PostgreSQL 9.6

Flo
-- 
Florian Lohoff                                                 f at zz.de
        UTF-8 Test: The 🐈 ran after a 🐁, but the 🐁 ran away
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200721/7d1bde65/attachment.sig>