Re: [postgis-users] ST_Clip - Different results between PostGIS 2.0.1 and 2.3.1

[Shira Bezalel]

Thanks Regina. That does help.

Shira

On Thu, Jan 19, 2017 at 12:06 AM, Regina Obe  wrote:

> Shira,
>
>
>
> No clue and haven't looked at the code to see the difference.
>
>
>
> The only thing I would guess is maybe in 2.0.1 if a pixel was only
> partially covered by a geometry then it doesn't include the pixel (thus the
> pixels you may be missing in 2.0.1 are those only partially covered by the
> geometry)
>
> And in 2.3, maybe it includes it if it's partially covered.
>
>
>
> I think we had discussions about that and if we needed another argument to
> denote which behavior should be used.  I forget what was decided if
> anything.
>
>
>
> Hope that helps,
>
> Regina
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
> Behalf Of *Shira Bezalel
> *Sent:* Wednesday, January 18, 2017 2:20 PM
> *To:* PostGIS Users Discussion 
> *Subject:* Re: [postgis-users] ST_Clip - Different results between
> PostGIS 2.0.1 and 2.3.1
>
>
>
>
>
> On Mon, Jan 9, 2017 at 10:18 AM, Shira Bezalel  wrote:
>
> Hi there. I'm testing an upgraded database and seeing different results in
> a query that's using the ST_Clip function.
>
>
>
> old database: PostgreSQL 9.1.14 with PostGIS 2.0.1
>
> new database: PostgreSQL: 9.6.1 with PostGIS 2.3.1
>
>
>
> I know ST_Clip was rewritten in C in PostGIS 2.1. Could this be
> responsible for different results? Or did the clipping algorithm change? I
> consulted the docs, but didn't see anything noted to this effect.
>
>
>
> Query:
>
>
>
> SELECT (pvc).value, SUM((pvc).count) AS total
>
>FROM (
>
>SELECT ST_ValueCount(st_clip(rast, c.the_geom),1) AS pvc
>
>FROM nlcdcal20_2011, counties c
>
>WHERE st_intersects(rast, c.the_geom) and
>
>c.NAME = 'Alameda'
>
>) AS foo
>
>GROUP BY (pvc).value
>
>order by (pvc).value
>
>
>
> If I remove the clip, the results are identical.
>
>
>
> In terms of the actual difference, here's the sum total of all pixels
> found by this query:
>
>
>
> Total pixels in 2.0.1 = 2,362,444
>
> Total pixels in 2.3.1 =
>
> ​​
>
> 2,418,017
>
>
>
> It's not a huge difference, but enough to be curious about. We can live
> with it, but it would just be nice to know the cause.
>
>
>
> Thank you for any insight you can provide.
>
> Shira
>
>
>
> p.s. The faster performance of the new ST_Clip is awesome!
>
>
>
>
>
> ​Didn't hear back from the list on this, so thought I'd resend. Just
> looking to know if anyone has run into this, what might be the cause, and
> maybe most importantly (and ideally), if the new results would be
> considered more accurate?
>
>
>
> Thank you,
>
> Shira​
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Shira Bezalel
Database Administrator & Desktop Support Manager
San Francisco Estuary Institute
www.sfei.org
Ph: 510-746-7304
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Understanding 3d bbox filtering support

[Andrea Aime]

Ah, thanks Paul, that works

Cheers
Andrea

On Thu, Jan 19, 2017 at 4:32 PM, Paul Ramsey 
wrote:

> No, just construct a 3D geometry as your query filter, not a 3d box.
>
> select count(*) from "3dfloor" where geom &&& ST_Makeline(ST_MakePoint(0,0,
> 0), ST_MakePoint(100,100,1));
>
> I would imagine that should work fine and dodge this little nastiness.
>
> P
>
> On Thu, Jan 19, 2017 at 5:14 AM, Andrea Aime  > wrote:
>
>> On Wed, Jan 18, 2017 at 5:13 PM, Rémi Cura  wrote:
>>
>>> Hey,
>>> I can't test right now,
>>> but :
>>>  - you have the same srid for object and bbox right?
>>>
>>
>> Err... the box3d does not seem to have a srid, nor I could find a way to
>> assign one.
>>
>>
>>>  - I remember Sandro discussing this case
>>>
>>
>> I searched in postgis-users before sending and again now, but could not
>> find any.
>> I've now searched in postgis-devel and found a thread you started on the
>> topic instead:
>> http://osgeo-org.1560.x6.nabble.com/BOX3D-strange-behaviour-
>> td5251231.html
>>
>> So... it seems that the z gets dropped eh? I see a pull request to fix it
>> but it seems it has
>> been closed and the associated ticket marked as "wont' fix":
>> https://trac.osgeo.org/postgis/ticket/3466
>>
>> Soo... should I do 2d filtering using && and then do the z filtering
>> using ST_Zmin/ST_ZMax?
>>
>> Cheers
>> Andrea
>>
>> --
>> ==
>> GeoServer Professional Services from the experts! Visit
>> http://goo.gl/it488V for more information.
>> ==
>>
>> Ing. Andrea Aime
>> @geowolf
>> Technical Lead
>>
>> GeoSolutions S.A.S.
>> Via di Montramito 3/A
>> 55054  Massarosa (LU)
>> phone: +39 0584 962313 <+39%200584%20962313>
>> fax: +39 0584 1660272 <+39%200584%20166%200272>
>> mob: +39  339 8844549 <+39%20339%20884%204549>
>>
>> http://www.geo-solutions.it
>> http://twitter.com/geosolutions_it
>>
>> *AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*
>>
>> Le informazioni contenute in questo messaggio di posta elettronica e/o
>> nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
>> loro utilizzo è consentito esclusivamente al destinatario del messaggio,
>> per le finalità indicate nel messaggio stesso. Qualora riceviate questo
>> messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
>> darcene notizia via e-mail e di procedere alla distruzione del messaggio
>> stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
>> divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
>> utilizzarlo per finalità diverse, costituisce comportamento contrario ai
>> principi dettati dal D.Lgs. 196/2003.
>>
>>
>>
>> The information in this message and/or attachments, is intended solely
>> for the attention and use of the named addressee(s) and may be confidential
>> or proprietary in nature or covered by the provisions of privacy act
>> (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
>> Code).Any use not in accord with its purpose, any disclosure, reproduction,
>> copying, distribution, or either dissemination, either whole or partial, is
>> strictly forbidden except previous formal approval of the named
>> addressee(s). If you are not the intended recipient, please contact
>> immediately the sender by telephone, fax or e-mail and delete the
>> information in this message that has been received in error. The sender
>> does not give any warranty or accept liability as the content, accuracy or
>> completeness of sent messages and accepts no responsibility  for changes
>> made after they were sent or for other risks which arise as a result of
>> e-mail transmission, viruses, etc.
>>
>> ---
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
==

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054  Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39  339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio 

Re: [postgis-users] Understanding 3d bbox filtering support

[Paul Ramsey]

No, just construct a 3D geometry as your query filter, not a 3d box.

select count(*) from "3dfloor" where geom &&& ST_Makeline(ST_MakePoint(0,0,0),
ST_MakePoint(100,100,1));

I would imagine that should work fine and dodge this little nastiness.

P

On Thu, Jan 19, 2017 at 5:14 AM, Andrea Aime 
wrote:

> On Wed, Jan 18, 2017 at 5:13 PM, Rémi Cura  wrote:
>
>> Hey,
>> I can't test right now,
>> but :
>>  - you have the same srid for object and bbox right?
>>
>
> Err... the box3d does not seem to have a srid, nor I could find a way to
> assign one.
>
>
>>  - I remember Sandro discussing this case
>>
>
> I searched in postgis-users before sending and again now, but could not
> find any.
> I've now searched in postgis-devel and found a thread you started on the
> topic instead:
> http://osgeo-org.1560.x6.nabble.com/BOX3D-strange-behaviour-td5251231.html
>
> So... it seems that the z gets dropped eh? I see a pull request to fix it
> but it seems it has
> been closed and the associated ticket marked as "wont' fix":
> https://trac.osgeo.org/postgis/ticket/3466
>
> Soo... should I do 2d filtering using && and then do the z filtering using
> ST_Zmin/ST_ZMax?
>
> Cheers
> Andrea
>
> --
> ==
> GeoServer Professional Services from the experts! Visit
> http://goo.gl/it488V for more information.
> ==
>
> Ing. Andrea Aime
> @geowolf
> Technical Lead
>
> GeoSolutions S.A.S.
> Via di Montramito 3/A
> 55054  Massarosa (LU)
> phone: +39 0584 962313 <+39%200584%20962313>
> fax: +39 0584 1660272 <+39%200584%20166%200272>
> mob: +39  339 8844549 <+39%20339%20884%204549>
>
> http://www.geo-solutions.it
> http://twitter.com/geosolutions_it
>
> *AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*
>
> Le informazioni contenute in questo messaggio di posta elettronica e/o
> nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
> loro utilizzo è consentito esclusivamente al destinatario del messaggio,
> per le finalità indicate nel messaggio stesso. Qualora riceviate questo
> messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
> darcene notizia via e-mail e di procedere alla distruzione del messaggio
> stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
> divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
> utilizzarlo per finalità diverse, costituisce comportamento contrario ai
> principi dettati dal D.Lgs. 196/2003.
>
>
>
> The information in this message and/or attachments, is intended solely for
> the attention and use of the named addressee(s) and may be confidential or
> proprietary in nature or covered by the provisions of privacy act
> (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
> Code).Any use not in accord with its purpose, any disclosure, reproduction,
> copying, distribution, or either dissemination, either whole or partial, is
> strictly forbidden except previous formal approval of the named
> addressee(s). If you are not the intended recipient, please contact
> immediately the sender by telephone, fax or e-mail and delete the
> information in this message that has been received in error. The sender
> does not give any warranty or accept liability as the content, accuracy or
> completeness of sent messages and accepts no responsibility  for changes
> made after they were sent or for other risks which arise as a result of
> e-mail transmission, viruses, etc.
>
> ---
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improve 3D BB query on large set of lines

[Rémi Cura]

Hey,
This is an interesting problem for sure.
AS always in the "slow query" topic,
improvement can come from a number of places
 - hardware : are your tables on SSD, does your index fit in RAM
 - postgres tuning : you have customised postgresql.conf to fit your
hardware
 - query writing : your query is optimally written (looks fine here)
 - data model (detailed after)

IF you want fast results, you need a good indexing, and this is possible if
you exploit the sparsness of your data.
So where is the sparsness?




 * Scenario A.1: your usual slices affects a majority of trees
  - you don't really need an index on X,Y, but mainly on Z.
The idea is to separate your index into index(X,Y) + index(Z)
 Try adding an index on GIST(numrange(ST_Zmin,ST_ZMax)) (you could add
it anyway)
 and an index on GIST(geom) (aka only 2D).
 * Scenario A.2 : your usual slices affect few trees
  - you could easily perform a 2 part query, where the first part use an
index on tree bounding box, and the second perform the query on edge. This
require to create and maintain a tree_bounding_box table with appropriate
indexes, which is quite easy with triggers Ex (not optimised):
   WITH filtering_by_tree AS (
 SELECT distinct tree_id
 FROM my_sync_table_of_tree_bounding_boxes as tree
 WHERE tree.bbox &&& your_slice
   ) SELECT edge_id
 FROM treenode_edge  AS te
 WHERE te.geom &&& your_slice
  AND EXISTS (SELECT 1 FROM filtering_by_tree As ft WHERE ft.tree_id =
te.tree_id)
  - you could use pgpointcloud to similar effect by construction a patch
per tree.

Now something is very ambiguous in your data, and it could have major
consequences :
 * Are the edges between nodes that represent neuron __only logical__ (they
are straight lines, always the same Zmax-Zmin, and represent a conceptual
graph), or are they polylines that represent the __actual__ neurone path?

 * Scenario B.1.1 : edges are only logical and have a constant Zmax-Zmin,
i.e. nodes have quantified Z :
  - you don't need edges at all for indexing, you should work only on nodes
(i.e. index nodes IN Z + (X,Y), find nodes, then find relevant edges). The
great advantage in this case is that you can use the full power of
pgpointcloud to create meaningfull patches (of variables size for instance,
see this
, 3.1,
Adapt patch grouping rules ), which means scalling in the range of billions
of points possible.

  - you could try to think of using the dual of your current graph (edges
become nodes, nodes become edges).

 * Scenario B.1.2 : edges are only logical, but nodes don't have a constant
Zmax-Zmin. : you could insert (virtual) nodes so you are again in the same
case as scenario B.1.1

 * Scenario B.2 : edges are actual neurone path.
  - you could construct intermediary objects (groups of neurone) of
variable size (those within cubes), so as to have again a 2 steps query :
first look for groups of neuron, then for neurons inside

 * Scenario C : your data don't have obvious sparsity.
  - use the same strategy you currently use, but scale by partitionning
your data into numerous tables.
   You can see the partitionning entry of the postgres manual.

   This would be much easier to create and maintain if you could partition
on only Z dimension.
   Note that you will need to duplicate (and then de-duplicate) some edges.


It's hard to recommend anything without further information on your
data/your requirement/your ressources.

Cheers,
Rémi-C








2017-01-17 22:43 GMT+01:00 Tom Kazimiers :

> Hi all,
>
> I am using Postgres 9.6 with PostGIS 2.3 and want to improve the
> performance of a bounding box intersection query on a large set of lines. I
> asked the same questions two month ago on gis.stackexchange.com, but
> didn't get any reply. Therefore, please excuse me for cross-posting, but I
> figured this list would be a good place to ask. This is the original post:
>
>  https://gis.stackexchange.com/questions/215762
>
> Problem: We store about 6 million lines/edges in a 3D space and use an axis
> aligned bounding box to find all intersecting (and included) lines. This
> works
> well, but we want to improve the query time for larger point sets.
>
> Context: Tree-like 3D structures represent neurons, each node has a parent
> node
> or it is the root. At the moment we deal with about 15 million nodes,
> grouped
> into 15 trees (many > 1 nodes). I want to improve existing
> performance
> bottlenecks with bigger result sets plus I plan to scale this setup to
> 10-100x
> the nodes. Typical query bounding boxes are rather slices than boxes, i.e.
> they expand any range in XY, but only a short distance in Z, but it could
> in principal any dimension that is "flat".
>
> Setup: The table storing edges looks like this:
>
>  =>\d+ treenode_edge
>Tabelle 

Re: [postgis-users] ST_Clip - Different results between PostGIS 2.0.1 and 2.3.1

[Regina Obe]

Shira,

 

No clue and haven't looked at the code to see the difference.

 

The only thing I would guess is maybe in 2.0.1 if a pixel was only partially 
covered by a geometry then it doesn't include the pixel (thus the pixels you 
may be missing in 2.0.1 are those only partially covered by the geometry)

And in 2.3, maybe it includes it if it's partially covered.

 

I think we had discussions about that and if we needed another argument to 
denote which behavior should be used.  I forget what was decided if anything.

 

Hope that helps,

Regina

 

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Shira Bezalel
Sent: Wednesday, January 18, 2017 2:20 PM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] ST_Clip - Different results between PostGIS 2.0.1 
and 2.3.1

 

 

On Mon, Jan 9, 2017 at 10:18 AM, Shira Bezalel  > wrote:

Hi there. I'm testing an upgraded database and seeing different results in a 
query that's using the ST_Clip function. 

 

old database: PostgreSQL 9.1.14 with PostGIS 2.0.1

new database: PostgreSQL: 9.6.1 with PostGIS 2.3.1

 

I know ST_Clip was rewritten in C in PostGIS 2.1. Could this be responsible for 
different results? Or did the clipping algorithm change? I consulted the docs, 
but didn't see anything noted to this effect. 

 

Query:

 

SELECT (pvc).value, SUM((pvc).count) AS total  

   FROM ( 

   SELECT ST_ValueCount(st_clip(rast, c.the_geom),1) AS pvc  

   FROM nlcdcal20_2011, counties c

   WHERE st_intersects(rast, c.the_geom) and 

   c.NAME = 'Alameda'  

   ) AS foo  

   GROUP BY (pvc).value

   order by (pvc).value

 

If I remove the clip, the results are identical.

 

In terms of the actual difference, here's the sum total of all pixels found by 
this query:

 

Total pixels in 2.0.1 = 2,362,444

Total pixels in 2.3.1 = 

​​

2,418,017

 

It's not a huge difference, but enough to be curious about. We can live with 
it, but it would just be nice to know the cause.

 

Thank you for any insight you can provide.

Shira

 

p.s. The faster performance of the new ST_Clip is awesome! 

 

 

​Didn't hear back from the list on this, so thought I'd resend. Just looking to 
know if anyone has run into this, what might be the cause, and maybe most 
importantly (and ideally), if the new results would be considered more accurate?

 

Thank you,

Shira​

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users