[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] ST_Clip - Different results between PostGIS 2.0.1 and 2.3.1
From: Shira Bezalel <shira () sfei ! org>
Date: 2017-01-19 20:01:33
Message-ID: CAE0KEwHyFrm2SKqsnWkt_trX7DsPXpGUGowmreh6c4Oj6MR21Q () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Thanks Regina. That does help.
Shira
On Thu, Jan 19, 2017 at 12:06 AM, Regina Obe <lr@pcorp.us> 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-bounces@lists.osgeo.org] *On
> Behalf Of *Shira Bezalel
> *Sent:* Wednesday, January 18, 2017 2:20 PM
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> *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 <shira@sfei.org> 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
[Attachment #5 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:tahoma,sans-serif">Thanks Regina. That does help.</div><div \
class="gmail_default" style="font-family:tahoma,sans-serif"><br></div><div \
class="gmail_default" style="font-family:tahoma,sans-serif">Shira</div><div \
class="gmail_extra"><br><div class="gmail_quote">On Thu, Jan 19, 2017 at 12:06 AM, \
Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" \
target="_blank">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
lang="EN-US" link="blue" vlink="purple"><div \
class="m_5013792272304710312WordSection1"><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Shira,<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">No \
clue and haven't looked at the code to see the \
difference.<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">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)<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">And \
in 2.3, maybe it includes it if it's partially \
covered.<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">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.<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Hope \
that helps,<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> \
<u></u></span></p><p class="MsoNormal" style="margin-left:.5in"><b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users \
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" \
target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>On Behalf Of \
</b>Shira Bezalel<br><b>Sent:</b> Wednesday, January 18, 2017 2:20 PM<br><b>To:</b> \
PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><wbr>><br><b>Subject:</b> Re: \
[postgis-users] ST_Clip - Different results between PostGIS 2.0.1 and \
2.3.1<u></u><u></u></span></p><div><div class="im trimless-h5 trimless-content"><p \
class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p><div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><div><p class="MsoNormal" style="margin-left:.5in">On \
Mon, Jan 9, 2017 at 10:18 AM, Shira Bezalel <<a href="mailto:shira@sfei.org" \
target="_blank">shira@sfei.org</a>> wrote:<u></u><u></u></p><blockquote \
style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in \
6.0pt;margin-left:4.8pt;margin-right:0in"><div><div><p class="MsoNormal" \
style="margin-left:.5in"><span style="font-family:"Tahoma",sans-serif">Hi \
there. I'm testing an upgraded database and seeing different results in a query \
that's using the ST_Clip function. <u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">old database: PostgreSQL 9.1.14 \
with PostGIS 2.0.1<u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span style="font-family:"Tahoma",sans-serif">new \
database: PostgreSQL: 9.6.1 with PostGIS 2.3.1<u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">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. <u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">Query:<u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">SELECT (pvc).value, \
SUM((pvc).count) AS total <u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span style="font-family:"Tahoma",sans-serif"> \
FROM ( <u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span style="font-family:"Tahoma",sans-serif"> \
SELECT ST_ValueCount(st_clip(rast, c.the_geom),1) AS pvc \
<u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span style="font-family:"Tahoma",sans-serif"> \
FROM nlcdcal20_2011, counties c<u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"> WHERE \
st_intersects(rast, c.the_geom) and <u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"> c.NAME = \
'Alameda' <u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span style="font-family:"Tahoma",sans-serif"> \
) AS foo <u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span style="font-family:"Tahoma",sans-serif"> \
GROUP BY (pvc).value <u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"> order by \
(pvc).value<u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">If I remove the clip, the results \
are identical.<u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">In terms of the actual difference, \
here's the sum total of all pixels found by this \
query:<u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">Total pixels in 2.0.1 = \
2,362,444<u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">Total pixels in 2.3.1 = \
<u></u><u></u></span></p><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u><u></u></span></p></div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">2,418,017</span><u></u><u></u></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><u></u> <u></u></p></div><div><p \
class="MsoNormal" style="margin-left:.5in">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.<u></u><u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in">Thank you for any insight you can \
provide.<u></u><u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif;color:#888888">Shira<u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">p.s. The faster performance of the \
new ST_Clip is awesome! \
<u></u><u></u></span></p></div></div></div></blockquote><div><p class="MsoNormal" \
style="margin-left:.5in"> <u></u><u></u></p></div></div><p class="MsoNormal" \
style="margin-left:.5in"><u></u> <u></u></p><div><div><div><div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">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?<u></u><u></u></span></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif"><u></u> \
<u></u></span></p></div><div><p class="MsoNormal" style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">Thank \
you,<u></u><u></u></span></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"><span \
style="font-family:"Tahoma",sans-serif">Shira<u></u><u></u></span></p></d \
iv></div></div></div></div></div></div></div></div></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br><br \
clear="all"><div><br></div>-- <br><div class="gmail_signature" \
data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div \
dir="ltr"><div><div><font color="#666666" face="tahoma, sans-serif" size="2">Shira \
Bezalel </font></div><div><font color="#666666" face="tahoma, sans-serif" \
size="2">Database Administrator & Desktop Support </font><span \
style="color:rgb(102,102,102);font-family:tahoma,sans-serif;font-size:small">Manager</span></div><div><font \
color="#666666" face="tahoma, sans-serif" size="2">San Francisco Estuary \
Institute</font></div><div><font color="#666666" face="tahoma, sans-serif" \
size="2"><a href="http://www.sfei.org" \
target="_blank">www.sfei.org</a></font></div><div><font color="#666666" face="tahoma, \
sans-serif" size="2">Ph: 510-746-7304</font></div></div><div \
style="font-weight:bold;font-style:normal;font-variant:normal;line-height:20px;margin:0px"><br \
style="color:rgb(0,0,0);font-family:Tahoma;font-size:13px;font-weight:normal;line-height:normal"></div>
<div style="padding-top:8px">
</div></div></div></div></div></div></div>
</div></div>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic