[pdal] [PDAL] PGPointCloud: Updating point info

Wed May 11 12:53:35 PDT 2016

Merci beaucoup Rémi!

Thank you for the wonderful info and alternatives, this is exactly what we
needed and will keep us busy for a while. More than I expected!

Regards,
JF

On Wed, May 11, 2016 at 2:19 PM Rémi Cura <remi.cura at gmail.com> wrote:

> Hey,
> currently updating points inside patches is little bit of a pain,
> you need to explode the patch to points, convert the point to array,
> change the value for every point, then reform the patches.
> I'll suppose the classification attribute class_id is in position 7.
>
> WITH my_patch AS (-- spatial join. Should be very fast, both table indexed
>     SELECT patch_id, patch
>     FROM patch_table, polygon_table
>     WHERE ST_Intersects(patch::geometry,geom)=TRUE
> )
> , my_points AS ( --converting patch to point. Note that could have been
> done directly in the previous CTE
>     SELECT patch_id, point[1:6]|| my_new_class_id||point[8:n_dims] AS
> updated_array
>     FROM my_patch, pc_explode(patch) as pt, pc_get(pt) as point
> )
> , updated_patch AS (
>     SELECT patch_id, pc_patch(updated_array,XX) AS n_patch
>     FROM my_points
>     GROUP BY patch_id
> )
> UPDATE ...
>
> Actually, I would do this in python outside the database (if necessary
> using shapely).
> Why? Because it would be much faster (basically, when you use pc-explode
> you are screwed)
> .
> If doing this in base, I would probably embed the query in a plpgsql
> function to force the index use.
>
> We could discuss this more but there are many solutions and they strongly
> depend on your requirement.
> For instance Pgpointcloud would be easy to extend (in C) to update a whole
> dimension at a time (setting all point class_id for instance).
>
> In another direction, it may be a better idea to not store the
> classification per point, but per patch (all points in one patch would have
> the same class).
> To enforce this you would need to split/merge your patches when necessary,
> which is fairly easy.
> It would be much much faster, and would probably makes more sense for your
> application (especially if you plan to access your point by tree_id).
>
> If you need python ressources (like read write patches in binary, and
> examples):
> https://github.com/Remi-C/PPPP_utilities/tree/master/pointcloud
>
> You may be interested by my working paper on classification at patch level
> (http://arxiv.org/abs/1602.06920), although only a part of the paper is
> not about classification,
> and I'm affraid this paper is a pain to understand (working on this).
>
> Cheers,
>
> Rémi C
>
> 2016-05-11 18:43 GMT+02:00 Jean-Francois Prieur <jfprieur at gmail.com>:
>
>> Wow thank you for your quick reply.
>>
>> Yes, you have the gist of it, although we do classification outside of
>> the database (using random forest), so it is really just assigining the
>> crown id (from the polygon shapefile) to all the points in the patch(es)
>> that fall under it. That way, we can easily pull all the points from a
>> specific crown by ID and not spatial join. The part I am having an issue
>> with (the shapefiles and point cloud is already in PostGIS/pgPointCloud) is:
>>
>> "then write SQL to update the points inside the crown polygons."
>>
>> I am not seeing the SQL needed to do this, it is probably trivial.
>>
>> Thanks a lot for that ressource as well, it is similar to what we do and
>> seems to be full of examples. We are trying to identfy species at the
>> individual tree level using ALS and multispectral imagery or multispectral
>> lidar.
>>
>> Thanks,
>> JF
>>
>> On Wed, May 11, 2016 at 12:12 PM Howard Butler <howard at hobu.co> wrote:
>>
>>>
>>> > On May 11, 2016, at 11:03 AM, Jean-Francois Prieur <jfprieur at gmail.com>
>>> wrote:
>>> >
>>> > Hello,
>>> >
>>> > Newbie to PostGIS/PGPointCLoud, used to work with MySQL many moons ago
>>> so know just enough to be dangerous!
>>> >
>>> > I am working in remote sensing research , precision forestry to be
>>> specific.
>>> >
>>> > We use ALS data and tree crown segmentation on the resulting canopy
>>> height models to segment individual trees. We then use the shapefile to
>>> extract the points from the las files. This is done using Python, it works
>>> but it is a slow process and we have to redo it when we want to update the
>>> info on our points.
>>> >
>>> > We are looking into doing a lot of this in PostGIS and have
>>> succesfully imported our las files using the awesome documentation
>>> provided. So we have a working pipeline LAS->PDAL->pgPointCloud and can
>>> perform all kinds of queries on the data so that part works.
>>> >
>>> > We can do the spatial join between the shapefiles containing all our
>>> individual crowns (there can be thousands) and then extracting all the
>>> points under those. What we would like to do is update all the points
>>> selected under a crown with that crowns ID so we do not have to do these
>>> spatial joins all the time eg. we can just query the crown number and get
>>> the points that way. We can select all the points, we just cannot update
>>> the points with the proper info. The spatial join is quite long with
>>> thousands of crowns, we would like to only run it once, update the points
>>> with the proper crown ID and then work with the data that way.
>>> >
>>> > I believe part of the solution is the shema used in the pipeline
>>> process, I think we would have to add a field in a new schema for the
>>> crownid which could then be updated with the crown id. Just not quite sure
>>> how to get there.
>>> >
>>> > Not afraid to learn on my own and digging in, just in need of a push
>>> in the right direction!
>>>
>>> Ok, let me attempt to rephrase. You want to update some kind of
>>> classification or other attribute for each point in a pgpatch that is
>>> inside a crown polygon? I think what I would do is ingest the crown
>>> shapefiles into PostGIS, enable the pointcloud_postgis extension, and then
>>> write SQL to update the points inside the crown polygons. There is very
>>> likely a Classification attribute as part of your data, and I would just
>>> repurpose that to flag points as your tree crowns in some way. Is this on
>>> the right track?
>>>
>>> Also, make sure your read Stephen Mather's blog posts on pgpointcloud
>>> and PDAL. He's doing something very similar to your usage and there might
>>> be some pointers in there https://smathermather.wordpress.com/tag/pdal/
>>>
>>> Howard
>>
>>
>> _______________________________________________
>> pdal mailing list
>> pdal at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/pdal
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pdal/attachments/20160511/dda8459a/attachment.html>