[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Topology Issue Reported by Query but Not Identified with ST_IsValid
From: Michael Treglia <mtreglia () gmail ! com>
Date: 2016-05-27 14:03:08
Message-ID: CAPKp32s0ZXaXvztnnB02X1VJJamet=LXOxUegchAVgtJ8EAVrg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
That did the Trick - Thanks!
I do have a quick question on this though, largely out of curiousity - when
I did it with the ST_Collect, it ran for ~3 hours before throwing the
error, whereas when I changed it to ST_Union, it ran to completion in ~7
minutes. Any guess why it took so long to identify the topology issues?
(I'll note that when I have the SQL right, I'm always impressed at how fast
PostGIS is for pretty giant datasets).
Thanks again,
mike
On Fri, May 27, 2016 at 8:47 AM, Michael Treglia <mtreglia@gmail.com> wrote:
> Ah, I see - Thanks Regina! I was wondering if it was something like that,
> as the area identified it with the issue definitely had lots of adjacencies.
>
> I'll give that a try and report back,
> Best,
> Mike
>
>
> On Fri, May 27, 2016 at 12:47 AM, Regina Obe <lr@pcorp.us> wrote:
>
>> Try replacing your ST_Collect call with ST_Union.
>>
>>
>>
>> ST_Collect when used with polygons will create invalid multipolygons
>> since it just collects them up, and if they are adjacent, it's not a valid
>> multipolygon.
>>
>>
>>
>> Hope that helps,
>>
>> Regina
>>
>> http://www.postgis.us
>>
>> http://postgis.net
>>
>>
>>
>>
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] *On
>> Behalf Of *Michael Treglia
>> *Sent:* Thursday, May 26, 2016 10:34 PM
>> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
>> *Subject:* [postgis-users] Topology Issue Reported by Query but Not
>> Identified with ST_IsValid
>>
>>
>>
>> Hi All,
>>
>>
>>
>> I was running a query involving ST_Difference between two multipolygon
>> layers, and a while in, it threw this message:
>>
>> ERROR: GEOSDifference: TopologyException: side location conflict at
>> 919670.1768945494 140665.82566365649
>>
>>
>>
>> However, I previously ran ST_MakeValid, and ST_IsValid doesn't report any
>> objects with problems. Is there something I'm missing, or something else I
>> need to check? (Apologies is this is just a naive question - still learning
>> my way around PostGIS).
>>
>>
>>
>> And in case it helps, here's my SQL:
>>
>>
>>
>> CREATE TABLE SI_Unclaimed AS
>>
>> SELECT gid, COALESCE(ST_Difference(geom_2263, (SELECT
>> ST_Collect(b.geom_2263)
>>
>> FROM citywide_basedata.parcels15
>> b
>>
>> WHERE ST_Intersects(a.geom_2263,
>> b.geom_2263)
>>
>> )), a.geom_2263)
>>
>> FROM citywide_basedata.boroughs_nowater a where boroname like 'Staten
>> Island';
>>
>>
>>
>> Thanks!
>>
>> Mike
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
>
[Attachment #5 (text/html)]
<div dir="ltr">That did the Trick - Thanks! <div><br></div><div>I do have a quick \
question on this though, largely out of curiousity - when I did it with the \
ST_Collect, it ran for ~3 hours before throwing the error, whereas when I changed it \
to ST_Union, it ran to completion in ~7 minutes. Any guess why it took so long to \
identify the topology issues?</div><div><br></div><div>(I'll note that when I \
have the SQL right, I'm always impressed at how fast PostGIS is for pretty giant \
datasets).</div><div><br></div><div>Thanks again,</div><div>mike</div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Fri, May 27, 2016 at 8:47 AM, \
Michael Treglia <span dir="ltr"><<a href="mailto:mtreglia@gmail.com" \
target="_blank">mtreglia@gmail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr">Ah, I see - Thanks Regina! I was wondering if \
it was something like that, as the area identified it with the issue definitely had \
lots of adjacencies.<div><br></div><div>I'll give that a try and report \
back,</div><div>Best, </div><div>Mike<br><div><br></div></div></div><div \
class="gmail_extra"><br><div class="gmail_quote"><div><div class="h5">On Fri, May 27, \
2016 at 12:47 AM, Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" \
target="_blank">lr@pcorp.us</a>></span> wrote:<br></div></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div><div class="h5"><div lang="EN-US" link="#0563C1" \
vlink="#954F72"><div><p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Try \
replacing your ST_Collect call with ST_Union.<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">ST_Collect \
when used with polygons will create invalid multipolygons since it just collects them \
up, and if they are adjacent, it's not a valid \
multipolygon.<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"><a \
href="http://www.postgis.us" \
target="_blank">http://www.postgis.us</a><u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><a \
href="http://postgis.net" \
target="_blank">http://postgis.net</a><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@lists.osgeo.org</a>] <b>On Behalf Of \
</b>Michael Treglia<br><b>Sent:</b> Thursday, May 26, 2016 10:34 PM<br><b>To:</b> \
PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> \
[postgis-users] Topology Issue Reported by Query but Not Identified with \
ST_IsValid<u></u><u></u></span></p><div><div><p class="MsoNormal" \
style="margin-left:.5in"><u></u> <u></u></p><div><p class="MsoNormal" \
style="margin-left:.5in">Hi All,<u></u><u></u></p><div><p class="MsoNormal" \
style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in">I was running a query involving ST_Difference between two \
multipolygon layers, and a while in, it threw this message: \
<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in">ERROR: \
GEOSDifference: TopologyException: side location conflict at 919670.1768945494 \
140665.82566365649<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">However, I previously ran ST_MakeValid, and ST_IsValid \
doesn't report any objects with problems. Is there something I'm missing, or \
something else I need to check? (Apologies is this is just a naive question - still \
learning my way around PostGIS).<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">And in case it helps, here's my \
SQL:<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> \
<u></u><u></u></p></div><div><div><p class="MsoNormal" \
style="margin-left:.5in">CREATE TABLE SI_Unclaimed AS<u></u><u></u></p></div><div><p \
class="MsoNormal" style="margin-left:.5in">SELECT gid, \
COALESCE(ST_Difference(geom_2263, (SELECT ST_Collect(b.geom_2263) \
<u></u><u></u></p></div><div><p class="MsoNormal" style="margin-left:.5in"> \
FROM citywide_basedata.parcels15 b<u></u><u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in"> \
WHERE ST_Intersects(a.geom_2263, b.geom_2263)<u></u><u></u></p></div><div><p \
class="MsoNormal" style="margin-left:.5in"> \
)), a.geom_2263)<u></u><u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in">FROM citywide_basedata.boroughs_nowater a where boroname \
like 'Staten Island';<u></u><u></u></p></div></div><div><p class="MsoNormal" \
style="margin-left:.5in"><u></u> <u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in">Thanks!<u></u><u></u></p></div><div><p class="MsoNormal" \
style="margin-left:.5in">Mike<u></u><u></u></p></div></div></div></div></div></div><br></div></div>_______________________________________________<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/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</blockquote></div><br></div>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://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