PC_Sort( pcpatch, dimension )

[strk]

Function to resort points inside a patch. The dimension parameter could be an array for multi-column sort. It could help with choosing optimal order for compression purpose.

[Remi-C]

Hey, great idea. I went into great trouble to find a good sorting criteria that creates levels of details. Sadly this kind of sorting is much more complex than multi-column sort. Also, it may be a bit of an overstep over the GHT compression principle

[strk]

It could refuse to sort anything that's not dimensionally-compressed (or simply refuse GHT). GHT doesn't work for me, btw (see #35)

[Remi-C]

Good idea. Up to now I was using a query like

SELECT pc_patch(pt ORDER BY pc_get(pt,'gps_time'))
FROM pc_explode(my_patch) as pt

to reorder. I also have functions that preserve the order while exploding here

[strk]

Here's a draft:

CREATE OR REPLACE FUNCTION PC_Sort(p pcpatch, fields text[])                    
RETURNS pcpatch AS                                                              
$$                                                                              
DECLARE                                                                         
  rec RECORD;                                                                   
  ret PCPATCH;                                                                  
  sql TEXT;                                                                     
  col TEXT;                                                                     
BEGIN                                                                           

  sql := 'SELECT PC_Patch(p ORDER BY';                                          
  col := '';                                                                    
  FOR rec IN SELECT unnest(fields) f LOOP                                       
    sql := sql || col || ' pc_get(p, ' || quote_literal(rec.f) || ')';          
    col := ',';                                                                 
  END LOOP;                                                                     
  sql := sql || ') FROM pc_explode($1) as p';                                   

  RAISE DEBUG '%', sql;                                                         

  EXECUTE sql USING p INTO ret;                                                 
  RETURN ret;                                                                   
END;                                                                            
$$                                                                              
LANGUAGE 'plpgsql' IMMUTABLE STRICT;

Useful to internally reorder patches to try at getting more RLE than ZLIB. For example: UPDATE mypatches SET p = PC_Sort(p, ARRAY['Classification','ScaleAngleRank']);

[Remi-C]

@mbredif : here is some food for thought

[mbredif]

I got a first implementation here for single dimension sorting: LI3DS/pointcloud#10 along with other pgpointcloud extensions. You can expect some pull requests soon ;)

[mbredif]

LI3DS/pointcloud#10 has been updated for multi'dimension sorting. I also developed a companion PC_IsSorted(pcpatch,text[],boolean) (LI3DS/pointcloud#9) which checks in linear time whether a patch is already sorted, with an optional parameter to specify if we mean strict ordering (no duplicates) or not. These have been implemented in C and are fully functional. See the issues to see which compressions are handled natively and which fall back to uncompression. Testing has been added by @pblottiere. If you are OK we can prepare a PR encompassing both functions.

[mbredif]

@strk, @Remi-C, could you please review this PR #102 ? Is that what you had in mind?

[mbredif]

@strk, @pramsey, what is your feeling for or against merging PR #102 ?

[pramsey]

Hey @mbredif are you on the pgpointcloud list? I'm in favour of starting to merge in all this work, but I'm not around enough to stick-handle if things break badly, so want to make sure you're on all the channels to catch things if they go awry. Sound OK?

[mbredif]

That sounds fine with me. @pblottiere and I are on the pgpointcloud list.