Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
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 '13 at 9:39
    
I am not convinced that "just" some million points XYZ degrade PostGIS performance significantly and will only believe it if I see a benchmark. –  til_b Apr 3 '13 at 10:21
1  
i store lidar in 10x10 meters multipoints by classification. We use only ground Z values –  simplexio Apr 3 '13 at 10:41
    
@Torsti Well, I'm talking about several point clouds of street surfaces, each with an extend of about 5x100m². Density of points varies from 4 to 10mm. This gives for every point file an amount of around 9million points. For now it is only xyz and i, but timestamps of each point should be propably added within the point files too. But as far as i know, there is no unique value planned. I will give a further try on your suggestion, thanks. –  knutella Apr 3 '13 at 10:58
1  
@til_b: Well, this is exactly what I was talking about... Good find :) –  knutella Apr 7 '13 at 10:55
show 12 more comments

2 Answers

up vote 2 down vote accepted

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/

share|improve this answer
    
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 '13 at 10:00
add comment

(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.

share|improve this answer
    
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 '13 at 13:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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