[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] invalid join selectivity: 179531936.000000
From: "Paul J. Caritj" <caritj () gmail ! com>
Date: 2015-09-28 19:38:58
Message-ID: CAL0u9cwMUCf7a9NxkwvHXd9HsWEVJ=3dsP_5WzkvKGnb2gXLxw () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Thanks everyone. I had mistakenly assumed that the bug Rémi and Imre have
pointed out would be fixed in 2.1.8. I haven't been able to upgrade or
patch, but the workaround of copying the data to a new table seems to work.
Thanks again,
Paul
On Fri, Sep 25, 2015 at 4:19 AM, Rémi Cura <remi.cura@gmail.com> wrote:
> Yep,
> I had the same problem
> https://trac.osgeo.org/postgis/ticket/2543
>
> The issue is that this bug is hard to reproduce.
>
> As a workaround, you can try to drop and recreate the table :
> DROP TABLE
> VACUUM FULL ANALYZE
> CREATE TABLE
> CREATE INDEX
>
>
> Cheers,
> Rémi-C
>
>
>
> 2015-09-25 1:32 GMT+02:00 Paul J. Caritj <caritj@gmail.com>:
>
>> Hello,
>> I am having trouble with what I expected to be a simple query. I have two
>> tables, one of census tracts (with a multipolygon geometry column, srid
>> 4269), and another of sites (with a point geometry column, srid 4269). I
>> want to find the number of sites that are within a tract of more than a
>> given population, so I've tried:
>>
>> SELECT count(sites.id) FROM sites, census_tracts WHERE AND
>> census_tracts.population > 2000 AND ST_Contains(census_tracts.geometry,
>> sites.location);
>>
>> But when I do this I get: psycopg2.InternalError: invalid join
>> selectivity: 179531936.000000.
>>
>> Both of the relevant tables have been analyzed and vacuumed.
>>
>> The output of postgis_full_version() is
>>
>> POSTGIS="2.1.8 r13780" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6
>> March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8"
>> LIBJSON="UNKNOWN" RASTER
>>
>> I'm using Windows 7.
>>
>> Does this ring a bell for anyone?
>>
>> Thanks very much!
>>
>> -Paul
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr">Thanks everyone. I had mistakenly assumed that the bug Rémi and Imre \
have pointed out would be fixed in 2.1.8. I haven't been able to upgrade or \
patch, but the workaround of copying the data to a new table seems to \
work.<div><br></div><div>Thanks again,</div><div>Paul</div></div><div \
class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 25, 2015 at 4:19 AM, \
Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" \
target="_blank">remi.cura@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"><div class="gmail_default" \
style="font-family:monospace,monospace">Yep,<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">I had the same problem<br><a \
href="https://trac.osgeo.org/postgis/ticket/2543" \
target="_blank">https://trac.osgeo.org/postgis/ticket/2543</a><br><br></div><div \
class="gmail_default" style="font-family:monospace,monospace">The issue is that this \
bug is hard to reproduce.<br></div><div class="gmail_default" \
style="font-family:monospace,monospace"><br>As a workaround, you can try to drop and \
recreate the table :<br>DROP TABLE <br>VACUUM FULL ANALYZE<br></div><div \
class="gmail_default" style="font-family:monospace,monospace">CREATE TABLE \
<br></div><div class="gmail_default" style="font-family:monospace,monospace">CREATE \
INDEX<br> <br></div><br><div class="gmail_default" \
style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" \
style="font-family:monospace,monospace">Rémi-C<br><br><br></div></div><div \
class="gmail_extra"><br><div class="gmail_quote"><span class="">2015-09-25 1:32 \
GMT+02:00 Paul J. Caritj <span dir="ltr"><<a href="mailto:caritj@gmail.com" \
target="_blank">caritj@gmail.com</a>></span>:<br></span><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div><div class="h5"><div dir="ltr">Hello,<div>I am having \
trouble with what I expected to be a simple query. I have two tables, one of census \
tracts (with a multipolygon geometry column, srid 4269), and another of sites (with a \
point geometry column, srid 4269). I want to find the number of sites that are within \
a tract of more than a given population, so I've \
tried:</div><div><br></div><div><div>SELECT count(<a href="http://sites.id" \
target="_blank">sites.id</a>) FROM sites, census_tracts WHERE AND \
census_tracts.population > 2000 AND ST_Contains(census_tracts.geometry, \
sites.location);</div></div><div><br></div><div>But when I do this I get: \
psycopg2.InternalError: invalid join selectivity: \
179531936.000000.</div><div><br></div><div>Both of the relevant tables have been \
analyzed and vacuumed. </div><div><br></div><div>The output of \
postgis_full_version() is </div><div><br></div><div><div>POSTGIS="2.1.8 \
r13780" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March \
2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" \
LIBJSON="UNKNOWN" RASTER</div></div><div><br></div><div>I'm using \
Windows 7.</div><div><br></div><div>Does this ring a bell for anyone? \
</div><div><br></div><div>Thanks very much!</div><span><font \
color="#888888"><div><br></div><div>-Paul</div></font></span></div> \
<br></div></div><span class="">_______________________________________________<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" rel="noreferrer" \
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></span></blockquote></div><br></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" \
rel="noreferrer" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/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