[postgis-tickets] [PostGIS] #4898: ST_SetZ seems to hang indefinitely if a vertex is not in raster

[PostGIS]

#4898: ST_SetZ seems to hang indefinitely if a vertex is not in raster
----------------------+---------------------------
  Reporter:  robe     |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  blocker  |  Milestone:  PostGIS 3.2.0
 Component:  postgis  |    Version:  master
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 I was thinking about what should be done in the event as geometry is not
 fully covered by a tile.

 1) Allow user to have a fill in value for the vertex

 or

 2) Clip the geometry by the raster and only return that portion of the
 geometry that is covered by the raster.

 The #2 is more useful to me.

 I'm thinking about 2 in particular cause the use case I want to use this
 with is tiled-rasters
 so doing ST_Clip/ST_Union dance to use this function is actually slower
 than the old way of just use ST_Value, ST_DumpPoints like so:

 Reference data:
 http://gis.ess.washington.edu/data/raster/tenmeter/hawaii/kauai.zip

 {{{
 raster2pgsql -s 26904 -Y -I -C -M kauai/*.bil -t 200x200 ch12.kauai | psql
 -d postgis_in_action


 }}}


 e.g. old way 10 ms

 {{{
 SELECT
     ST_AsText(
         ST_MakeLine( -- <1>
             ST_Translate( -- <2>
                 ST_Force3D((gd).geom),  -- <3>
                 0,0,
                 COALESCE(ST_Value(rast,(gd).geom),0) -- <4>
             )
         )
     ) As line_3dwkt
 FROM
     (
         SELECT ST_DumpPoints(
             ST_GeomFromText(
                 'LINESTRING(
                     444210 2438785,434125 2448785,
                     466666 2449780,466670 2449781
                 )',
                 26904
             )
         ) As gd
     ) As t -- <5>
     LEFT JOIN
     ch12.kauai
     ON ST_Intersects(rast,(t.gd).geom); -- <6>
 }}}



 -- 256ms using ST_SetZ


 {{{
 SELECT ST_AsText(
       ST_SetZ(k.rast, geom) -- <1>
   )
 FROM (SELECT ST_GeomFromText(
                 'LINESTRING(
                     444210 2438785,434125 2448785,
                     466666 2449780,466670 2449781
                 )',
                 26904
         ) AS geom ) AS t -- <2>
         CROSS JOIN LATERAL
         (
           SELECT ST_Union( -- <3>
             ST_Clip(rast,
               ST_Expand(t.geom,10)  -- <4>
             )
           ) AS rast
              FROM ch12.kauai AS k
             WHERE ST_Intersects(k.rast, t.geom)
       ) AS k;
 }}}


 I haven't tested the timing of clipping the geometry and then unioning the
 geometry slivers to see if that is faster. I suspect it would be

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4898#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.