[postgis-users] Need better strategy for a query

[Stephen Woodbridge]

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