[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    [postgis-users] ST_Union() / Linestrings / Other ideas
From:       Florian Lohoff <f () zz ! de>
Date:       2020-07-21 7:59:31
Message-ID: 20200721075930.6y5l4meah7swrbcg () pax ! zz ! de
[Download RAW message or body]

[Attachment #2 (multipart/signed)]


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@zz.de
        UTF-8 Test: The 🐈 ran after a 🐁, but the 🐁 ran away


["signature.asc" (application/pgp-signature)]
[Attachment #6 (text/plain)]

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread]