[postgis-users] Need better strategy for a query

[Paul Ramsey]

No? http://postgis.net/docs/ST_Subdivide.html <http://postgis.net/docs/ST_Subdivide.html>

P

> On 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/ee138619/attachment.html>