[postgis-users] Need better strategy for a query

[Martin Davis]

Would it help to avoid processing lines which are already short?  (Hard to
know without knowing the actual data size distribution).

Also, ST_LineMerge seems like it might be expensive.  Is is possible to
avoid merging lines?  (This is creating longer lines, which seems like
opposite to the point of the exercise)

On Tue, Nov 19, 2019 at 1:33 PM Stephen Woodbridge <
stephenwoodbridge37 at gmail.com> wrote:

> Hi,
>
> I have a global dataset in SRS EPSG:4326 that are ocean depth contours.
> The problem is that these tend to be long and have huge bbox so spatial
> index does not help when trying to render them in mapserver.
>
> I plan was to chop these into shorter segments with a command like:
>
> bathymetry=# explain select depth,
>          feet,
>          st_linesubstring(geom, 0.5*n/length,
>          case when 0.5*(n+1)<length then 0.5*(n+1)/length else 1 end) as
> geom
>      from (
>          select a.depth,
>              -round(a.depth/0.3048) as feet,
>              st_linemerge(a.geom) as geom,
>              st_length(a.geom) as length
>          from c100 a
>          where st_length(a.geom)>0
>      ) as t
>      cross join generate_series(0,10000) as n
>      where n*0.5/length < 1;
>                                                  QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------
>   Nested Loop  (cost=0.00..549466002.55 rows=1345242667 width=1723)
>     Join Filter: (((((n.n)::numeric * 0.5))::double precision /
> st_length(a.geom)) < '1'::double precision)
>     ->  Seq Scan on c100 a  (cost=0.00..1279615.77 rows=4035728 width=1719)
>           Filter: (st_length(geom) > '0'::double precision)
>     ->  Function Scan on generate_series n  (cost=0.00..10.00 rows=1000
> width=4)
> (5 rows)
>
> but running a restricted test query on 10,000 lines took 1.5 hours and
> with about 12M lines in the source table I estimate about 75 days to
> compute :(
>
> The 0.5 in the query is for 1/2 degree (~35 mile) max length. I can
> adjust that to say 1 deg but I suspect that will only nominally impact
> the run time (running a test to see).
>
> So is there a faster way to do this?
>
> Would smoothing the lines first help or would the cost of smooth and
> then dividing be about the same?
>
> -Steve W
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20191119/ad57460d/attachment.html>