<div dir="ltr"><div><div><div><div>Hum you can do a simple test, <br></div>for the table you are using, <br></div>look at the pgpoint column type number (called pcid)<br><br></div>You can know this like this<br><br> SELECT pc_astext(pt)<br> FROM xyz_patches, pc_explode(pa) as pt <br> LIMIT 1<br><br></div><div>Know look into public.pointcloud_formats, the xml schema corresponding to the pcid you found.<br></div><div>You should see if it is compressed.<br><br></div><div>Another method is to take a patch and compare it with the uncompressed version.<br><br></div><div>SELECT block_id, pa, pc_uncompress(pa)<br></div><div>FROM patches<br></div><div>LIMIT 1 <br></div><div><br></div><div><br></div><div>Here is the correct way to write your querry anyway, you should see a great speed up.<br><br></div><div>--recent postgres<br>SELECT block_id , pc_get(pt, 'X') as x, pc_get(pt, 'Y') as y, pc_get(pt, 'Z') as z<br>FROM xyz_patches, pc_explode(pa) as pt<br><br></div><div>--old postgres<br></div><div>WITH points AS (<br>SELECT block_id, pc_explode(pa) as pt<br>FROM xyz_patches, pc_explode(pa) as pt<br>)<br></div><div>SELECT block_id , pc_get(pt, 'X') as x, pc_get(pt, 'Y') as y, pc_get(pt, 'Z') as z<br></div><div>FROM points<br><br></div><div>Cheers,<br></div><div>Rémi-C<br></div><div><span style="font-size:10pt;font-family:"Arial","sans-serif";color:rgb(31,73,125)"><br></span></div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-03-26 19:09 GMT+01:00 Jonathan Moules <span dir="ltr"><<a href="mailto:J.Moules@hrwallingford.com" target="_blank">J.Moules@hrwallingford.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div link="blue" vlink="purple" lang="EN-GB">
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Hi</span>
<span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Rémi,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Thanks for the reply and information.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">I’m afraid I inherited this database and don’t know much about it (or pointcloud).<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Looking into though, I doubt it’s compressed, we extract the data with:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">select block_id , pc_get(pc_explode(pa), 'X') as x, pc_get(pc_explode(pa), 'Y') as y, pc_get(pc_explode(pa), 'Z') as z from xyz_patches<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">so using pc_get rather than pc_uncompress.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">On the other hand, there are 6 entries in pointcloud_formats which all have the “<Metadata name="compression">dimensional</Metadata>” line set. But I can’t decipher
the documentation well enough to know if the data actually uses it.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Cheers,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Jonathan<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US"> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Rémi Cura<br>
<b>Sent:</b> Thursday, March 26, 2015 5:33 PM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] 2.1.6 Released<u></u><u></u></span></p><div><div class="h5">
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Hey,<u></u><u></u></p>
</div>
<p class="MsoNormal">if you use the compression of patch and sensible xml schema,
<br>
you should already have a 1:2 to 1:4 compression of the point size.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">(compared to binary ply in my case)<u></u><u></u></p>
</div>
<p class="MsoNormal">Cheers,<u></u><u></u></p>
</div>
<p class="MsoNormal">Rémi-C<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">2015-03-26 18:05 GMT+01:00 Jonathan Moules <<a href="mailto:J.Moules@hrwallingford.com" target="_blank">J.Moules@hrwallingford.com</a>>:<u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Hi Paul,</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">I suspect I know the answer to this (“no”), but to confirm, is the disk saving for points going to
apply to points stored using the pointcloud extension in a postgis database?</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Thanks,</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d">Jonathan</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#1f497d"> </span><u></u><u></u></p>
<div>
<div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US">
<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Paul Ramsey<br>
<b>Sent:</b> Friday, March 20, 2015 5:45 PM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> [postgis-users] 2.1.6 Released</span><u></u><u></u></p>
</div>
</div>
<div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<p style="margin:0cm;margin-bottom:.0001pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:11.0pt;font-family:"Helvetica","sans-serif";color:#222222">The 2.1.6 release of PostGIS is <a href="http://download.osgeo.org/postgis/source/postgis-2.1.6.tar.gz" target="_blank"><span style="font-family:"inherit","serif";color:#0066cc;border:none windowtext 1.0pt;padding:0cm">now
available</span></a>.</span><u></u><u></u></p>
<p style="margin-bottom:10.8pt;line-height:16.5pt;vertical-align:baseline;font-stretch:inherit">
<span style="font-size:11.0pt;font-family:"Helvetica","sans-serif";color:#222222">The PostGIS development team is happy to release patch for PostGIS 2.1, the 2.1.6 release. As befits a patch release, the focus is on bugs, breakages, and performance issues.
Users with large tables of points will want to priorize this patch, for substantial (~50%) disk space savings.</span><u></u><u></u></p>
<p style="margin:0cm;margin-bottom:.0001pt;line-height:16.5pt;vertical-align:baseline;font-stretch:inherit">
<span style="font-size:11.0pt;font-family:"Helvetica","sans-serif";color:#222222"><a href="http://download.osgeo.org/postgis/source/postgis-2.1.6.tar.gz" target="_blank"><span style="font-family:"inherit","serif";color:#0066cc;border:none windowtext 1.0pt;padding:0cm">http://download.osgeo.org/postgis/source/postgis-2.1.6.tar.gz</span></a></span><u></u><u></u></p>
<p style="margin-bottom:10.8pt;line-height:16.5pt;vertical-align:baseline;font-stretch:inherit">
<strong><span style="font-size:11.0pt;font-family:"Helvetica","sans-serif";color:#222222">Enhancements</span></strong><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3000, Ensure edge splitting and healing algorithms use indexes</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3048, Speed up geometry simplification (J.Santana @ CartoDB)</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3050, Speep up geometry type reading (J.Santana @ CartoDB)</span><u></u><u></u></p>
<p style="margin-bottom:10.8pt;line-height:16.5pt;vertical-align:baseline;font-stretch:inherit">
<strong><span style="font-size:11.0pt;font-family:"Helvetica","sans-serif";color:#222222">Bug Fixes</span></strong><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#2941, allow geography columns with SRID other than 4326</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3069, small objects getting inappropriately fluffed up w/ boxes</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3068, Have postgis_typmod_dims return NULL for unconstrained dims</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3061, Allow duplicate points in JSON, GML, GML ST_GeomFrom* functions</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3058, Fix ND-GiST picksplit method to split on the best plane</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3052, Make operators <-> and <#> available for PostgreSQL < 9.1</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3045, Fix dimensionality confusion in &&& operator</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3016, Allow unregistering layers of corrupted topologies</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3015, Avoid exceptions from TopologySummary</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3020, ST_AddBand out-db bug where height using width value</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-right:18.0pt;line-height:16.5pt;vertical-align:baseline">
<span style="font-size:10.0pt;font-family:Symbol;color:#222222">·</span><span style="font-size:7.0pt;color:#222222">
</span><span style="font-size:11.0pt;font-family:"inherit","serif";color:#222222">#3031, Allow restore of Geometry(Point) tables dumped with empties in them</span><u></u><u></u></p>
<p style="margin:0cm;margin-bottom:.0001pt;line-height:16.5pt;vertical-align:baseline;font-stretch:inherit">
<span style="font-size:11.0pt;font-family:"Helvetica","sans-serif";color:#222222">View all <a href="http://trac.osgeo.org/postgis/query?status=closed&groupdesc=1&group=priority&milestone=PostGIS+2.1.6&order=priority" target="_blank"><span style="font-family:"inherit","serif";color:#0066cc;border:none windowtext 1.0pt;padding:0cm">closed
tickets</span></a>.</span><u></u><u></u></p>
</div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif""> </span><u></u><u></u></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif""> </span><u></u><u></u></p>
</div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">-- <br>
Paul Ramsey<br>
<a href="http://cleverelephant.ca" target="_blank">http://cleverelephant.ca</a></span><u></u><u></u></p>
<div>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif""><a href="http://postgis.net" target="_blank">http://postgis.net</a>
</span><u></u><u></u></p>
</div>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif""> </span><u></u><u></u></p>
</div>
</div>
<p style="text-align:center" align="center"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif";background:white">This message has been scanned for viruses by
</span><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif""><a href="http://www.mailcontrol.com/" target="_blank"><span style="background:white">MailControl</span></a><span style="background:white">, a service from BlackSpider Technology</span></span><u></u><u></u></p>
<p><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif"">Click <a href="https://www.mailcontrol.com/sr/MZbqvYs5QwJvpeaetUwhCQ==" target="_blank">
here</a> to report this email as spam.</span><u></u><u></u></p>
<p style="text-align:center" align="center"><span style="font-size:10.0pt;font-family:"Helvetica","sans-serif""> </span><u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div class="MsoNormal" style="text-align:center" align="center">
<hr align="center" size="2" width="100%">
</div>
<p><b><span style="font-size:8.0pt;font-family:"Arial","sans-serif"">HR Wallingford and its subsidiaries</span></b><span style="font-size:8.0pt;font-family:"Arial","sans-serif""> uses faxes and emails for confidential and legally privileged business communications.
They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
<br>
If you have received this message in error please advise us immediately and destroy all copies of it.
<br>
<br>
HR Wallingford Limited<br>
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom<br>
Registered in England No. 02562099<u></u><u></u></span></p>
<div class="MsoNormal" style="text-align:center" align="center">
<hr align="center" size="2" width="100%">
</div>
</div>
<p class="MsoNormal"><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div></div></div><div><div class="h5">
<br>
<hr>
<p style="font-family:arial;font-size:8pt"><b>HR Wallingford and its subsidiaries</b> uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties
other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
<br>
If you have received this message in error please advise us immediately and destroy all copies of it.
<br>
<br>
HR Wallingford Limited<br>
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom<br>
Registered in England No. 02562099<br>
</p>
<p></p>
<hr>
<p></p>
</div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>