17

I wonder, how it's possible to store huge sets of laser scanned point cloud data in PostGIS, with the time-aspect for processing it in mind. I know, there exists a geometry-object Point in PostGIS. But as far as I know it saves each point in a new tupel, which can make searching for any certain point a very slow process, if a few millions or more of them are stored.

I found a paper from HSR Universtiy of Applied Sciences Rapperswill, discussing this topic. It suggests three ways to store such data: Whole data in one tupel, Each point in one tupel or Splitting Data into Blocks which are referenced by info-tables, holding the extends of each block. As the third way seems the most useful for locating stored points, I wonder if anyone already has made some experiences with it?

The paper can be found here: http://wiki.hsr.ch/Datenbanken/files/pgsql_point_cloud.pdf

Last but not least, I stumpled across a project on github, which seems to deal with point cloud manners in PostgeSQL. Unfortunately not much information about it around the net. So the same question here: Has someone already made some experiences with it? Is it usable for such purposes?

Project can be found here: https://github.com/pramsey/pointcloud

I would also be glad to hear about other suggestions, ideas or experiences, if there are any. But i must admit, that non-commercial solutions are prefered.

Improve this question
17
  • 1
    Could you give a rough idea of what you mean by huge, and what kind of information from the point cloud do you need? I.e. only XYZ and intensity, which could e.g. be stored in blocked MultipointZM or also other attribute data which probably requires Point to get unique values for each separate point measurement?
    – Torsti
    Apr 3, 2013 at 9:39
  • 1
    i store lidar in 10x10 meters multipoints by classification. We use only ground Z values
    – simpleuser001
    Apr 3, 2013 at 10:41
  • 1
    @AndreSilva The aim is, to generate roads surface profiles out of the data. For now we transformed points into DEM-grids and used PostGIS to store them as rasterblocks and SAGA to create finally the profiles from it. It runs for testing purposes, but it also means a loss in accuracy through rastering the data before db import. Also the export of the grid-cells, cutted by the given profile lines goes very slowly in PostGIS(thanks to ST_Union). Would be nice if you could recommend tools for similar tasks.
    – knutella
    Apr 3, 2013 at 12:38
  • 1
    @til_b: Well, this is exactly what I was talking about... Good find :)
    – knutella
    Apr 7, 2013 at 10:55
  • 1
    I asked myself the same question, and put some pieces together to get a working prototype. So far it works great, with no scalability problems from several millions up to hundreds millions of points with around 20 attributes each. With this many points, finding points inside an area takes a few hundred millis. It takes about the same time to filter by timestamp (precise time of acquisition for me). On the overall the perf are same or better than in "LiDAR Data Management Pipeline; from Spatial Database Population to Web-Application Visualization" Data is compressed into DB (about 1:2
    – user19797
    Jul 5, 2013 at 18:04

2 Answers 2

Reset to default
7

There is a lot in your question. The short answer is yes, it is completely possible to store huge point cloud data in PostGIS and use it for processing. We've built such a full system that does this.

This video is a little out of date with it's numbers but we had TBs of mobile/terrestrial and aerial data in postgis accessible through python for processing in the back end and with a web front end allowing 3D viewing and downloading of the data. https://vimeo.com/39053196

It really comes down to how you choose to store the data in PostGIS and how you are going to be accessing it. A good solution for aerial data might well be to grid the data in some way and use multipoints for efficiency. However, if you are working with mobile or terrestrial data where the point density can be between 500-30000+ points per metre squared this approach doesn't work. Then it comes down to looking at your hardware and the number of concurrent users you expect. Details about this can be found in some of our papers http://www.mendeley.com/profiles/conor-mc-elhinney/

Improve this answer
2
  • Hi, thanks for so many detailed information. The ides/tests offered in your papers seem really useful! It will take me some time to see it all through but as I saw on a first read, they already provide whole workarounds. Thanks a lot for the adding! Also the video and your browser-based pc-viewer is quite interesting and seem to work very well and smooth! Unfortunately I got my hands short-termed on other stuff. I hope to continue with pc-data shortly though.
    – knutella
    Apr 10, 2013 at 10:00
  • The Glimpse project has a really cool demo here: ncg.nuim.ie/glimpse/auth/login.php
    – Kozuch
    Oct 26, 2016 at 13:32
7

(The answer is based on my and others' comments above; haven't really tested it)

Store the points as MultiPointZM. The best grid size would probably be dependent on access patterns and you need to do some testing on this. A regular grid with a spatial index should make queries quite fast. If 3d access is important then MultiPointZM could be 3D block based(1) instead of a 2D plane grid, then (if you have PostGIS >= 2.0) you would be able to use &&& for fast 3D queries.

You could also store the grid pattern in a separate table, which might be useful e.g. when updating the data and validating that the MultiPointZM blocks stay within their bounds after edits etc.

Storing timestamps or other data would only be possible for a block at a time, but some binary / category data could be stored by disaggregating each block by attribute if there are not too many categories and/or attributes.

If you end up having to store the data as separate PointZM, then a foreign key on the grid table + B-Tree index would make loading only the specific points (probably) a lot faster than just queyrying the table directly, even with a spatial index.

(1) If the range of Z-values is small (it's a road, after all), this probably does not make sense.

Improve this answer
1
  • I think your 'summary' hits pretty well in as a conclusion of the former discussed proposals. As you said, the 'right' way to load such data must be figured out within the needs and proposed solutions. It turned out, not to be impossible thanks to so many ideas. It gave me a lot of inspiration for my further work on this issue. Thanks a lot!
    – knutella
    Apr 3, 2013 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.