<div dir="ltr">Thanks <span style="font-family:arial,sans-serif;font-size:13px;white-space:nowrap">Francois,</span><div style><span style="font-family:arial,sans-serif;font-size:13px;white-space:nowrap">as I am a newbie, could you please help me a bit with that idea? How should i do? How do I calculate the X?</span></div>
<div style><span style="font-family:arial,sans-serif;font-size:13px;white-space:nowrap"><br></span></div><div style><span style="font-family:arial,sans-serif;font-size:13px;white-space:nowrap">Thank you.</span></div></div>
<div class="gmail_extra"><br><br><div class="gmail_quote">2013/1/22 Francois Hugues <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hello,<br>
<br>
In the with of your query, you are searching for the min distance and you calculate all distances between every polygons. Try add a where clause st_distance< X to avoid unuseful calcul (you should find the max of the min to set X value). This may improve performances.<br>
<br>
Hugues<br>
<br>
Carsten Hogertz <<a href="mailto:carsten.hogertz@gmail.com">carsten.hogertz@gmail.com</a>> a écrit :<br>
<div><div class="h5"><br>
I (maybe) have a similar problem.<br>
Could I rewrite something in my statement to make it faster?<br>
<br>
with dist_min as (<br>
select h.gid, min(st_distance(h.geom, i.geom)) dist_min from hotels_150000<br>
h, iata_codes i<br>
where h.l_iso_a2 = i.ctry<br>
group by h.gid<br>
)<br>
select h.h_key, h.gid, i.loc, i.gid, d.dist_min from dist_min d,<br>
hotels_150000 h, iata_codes i<br>
where st_distance(h.geom, i.geom) = dist_min<br>
order by h.h_key<br>
<br>
thanks<br>
--<br>
carsten<br>
<br>
<br>
2013/1/22 Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>><br>
<br>
> (sorry, a little precision: All st_* predicates internally use the &&<br>
> GIST operator: bbox intersects)<br>
><br>
><br>
> On 22 January 2013 16:44, Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>> wrote:<br>
><br>
>> In PSQL, \do will list you installed operators:<br>
>><br>
>> \do+<br>
>> List of operators<br>
>> Schema | Name | Left arg type | Right arg type | Result type |<br>
>> Description<br>
>><br>
>> --------+------+---------------+----------------+------------------+-------------<br>
>> public | && | geography | geography | boolean |<br>
>> public | && | geometry | geometry | boolean |<br>
>> public | && | geometry | raster | boolean |<br>
>> public | && | raster | geometry | boolean |<br>
>> public | && | raster | raster | boolean |<br>
>> public | &&& | geometry | geometry | boolean |<br>
>> public | &< | geometry | geometry | boolean |<br>
>> public | &< | raster | raster | boolean |<br>
>> public | &<| | geometry | geometry | boolean |<br>
>> public | &<| | raster | raster | boolean |<br>
>> public | &> | geometry | geometry | boolean |<br>
>> public | &> | raster | raster | boolean |<br>
>> public | < | geography | geography | boolean |<br>
>> public | < | geometry | geometry | boolean |<br>
>> public | <#> | geometry | geometry | double precision |<br>
>> public | <-> | geometry | geometry | double precision |<br>
>> public | << | geometry | geometry | boolean |<br>
>> public | << | raster | raster | boolean |<br>
>> public | <<| | geometry | geometry | boolean |<br>
>> public | <<| | raster | raster | boolean |<br>
>><br>
>> And this Pgis doc page describes them:<br>
>> <a href="http://postgis.net/docs/manual-2.0/reference.html#Operators" target="_blank">http://postgis.net/docs/manual-2.0/reference.html#Operators</a><br>
>><br>
>> All st_* predicates internally use GIST operator.<br>
>><br>
>> Nicolas<br>
>><br>
>><br>
>> On 22 January 2013 16:39, Pietro Rossin <<a href="mailto:pierigis@gmail.com">pierigis@gmail.com</a>> wrote:<br>
>><br>
>>> Is there any reference list for the GIST operators?<br>
>>> Thnx<br>
>>> Pietro<br>
>>><br>
>>><br>
>>> 2013/1/22 Pietro Rossin <<a href="mailto:pierigis@gmail.com">pierigis@gmail.com</a>><br>
>>><br>
>>>> Superthank you!<br>
>>>> It works and very fast!<br>
>>>> Bie<br>
>>>> Pietro<br>
>>>><br>
>>>><br>
</div></div>>>>> 2013/1/22 Nicolas Ribot-2 [via PostGIS] <[hidden email]<<a href="http://user/SendEmail.jtp?type=node&node=5002380&i=0" target="_blank">http://user/SendEmail.jtp?type=node&node=5002380&i=0</a>><br>
<div class="im">>>>> ><br>
>>>><br>
>>>>> Hi,<br>
>>>>><br>
>>>>> GIST index is only used with GIST operators:<br>
>>>>> &&, st_intersects, st_touches, etc.<br>
>>>>><br>
>>>>> You should rewrite your query to define a geographic bounding box and<br>
>>>>> test your data against it:<br>
>>>>><br>
>>>>> SELECT the_geom, layer, id<br>
>>>>> FROM gauss_b.polilineegb<br>
>>>>> where the_geom && 'BOX(2365385 5075215, 2366019 5075752)'::box2d;<br>
>>>>><br>
>>>>> Nicolas<br>
>>>>><br>
>>>>><br>
>>>>><br>
</div>>>>>> On 22 January 2013 15:38, Pietro Rossin <[hidden email]<<a href="http://user/SendEmail.jtp?type=node&node=5002378&i=0" target="_blank">http://user/SendEmail.jtp?type=node&node=5002378&i=0</a>><br>
<div><div class="h5">>>>>> > wrote:<br>
>>>>><br>
>>>>>> Hello<br>
>>>>>> I have a table with about 6.000.000 polilines records.<br>
>>>>>> The geometry value is in the_geom column.<br>
>>>>>> I made an index with this instruction<br>
>>>>>><br>
>>>>>> *************<br>
>>>>>> CREATE INDEX idx_polilineegb_geom<br>
>>>>>> ON gauss_b.polilineegb<br>
>>>>>> USING gist<br>
>>>>>> (the_geom );<br>
>>>>>> *************<br>
>>>>>><br>
>>>>>> If I try a query like this:<br>
>>>>>> **********************<br>
>>>>>> SELECT the_geom, layer, id<br>
>>>>>> FROM gauss_b.polilineegb<br>
>>>>>> where<br>
>>>>>> (st_xmin(the_geom)=2365385 and st_ymin(the_geom)=5075215) and<br>
>>>>>> (st_xmax(the_geom)=2366019 and st_ymax(the_geom)=5075752);<br>
>>>>>> **********************<br>
>>>>>><br>
>>>>>> It takes ages to execute..<br>
>>>>>> I take a look at the explain query it says:<br>
>>>>>><br>
>>>>>> ************************<br>
>>>>>> "Seq Scan on polilineegb (cost=0.00..727911.08 rows=1 width=2924)"<br>
>>>>>> " Filter: ((st_xmin((the_geom)::box3d) = 2365385::double precision)<br>
>>>>>> AND<br>
>>>>>> (st_ymin((the_geom)::box3d) = 5075215::double precision) AND<br>
>>>>>> (st_xmax((the_geom)::box3d) = 2366019::double precision) AND<br>
>>>>>> (st_ymax((the_geom)::box3d) = 5075752::double precision))"<br>
>>>>>> ************************<br>
>>>>>> This is a Seq Scan, so it doesn't use the index, right?<br>
>>>>>> Why?<br>
>>>>>> Is there some more performing index to be used??<br>
>>>>>><br>
>>>>>> Thank you<br>
>>>>>> Pietro<br>
>>>>>><br>
>>>>>><br>
>>>>>><br>
>>>>>><br>
>>>>>><br>
>>>>>> --<br>
>>>>>> View this message in context:<br>
>>>>>> <a href="http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377.html" target="_blank">http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377.html</a><br>
>>>>>> Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
>>>>>> _______________________________________________<br>
>>>>>> postgis-users mailing list<br>
</div></div>>>>>>> [hidden email] <<a href="http://user/SendEmail.jtp?type=node&node=5002378&i=1" target="_blank">http://user/SendEmail.jtp?type=node&node=5002378&i=1</a>><br>
<div class="im">>>>>>> <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>
>>>>>><br>
>>>>><br>
>>>>><br>
>>>>> _______________________________________________<br>
>>>>> postgis-users mailing list<br>
</div>>>>>> [hidden email] <<a href="http://user/SendEmail.jtp?type=node&node=5002378&i=2" target="_blank">http://user/SendEmail.jtp?type=node&node=5002378&i=2</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>
>>>>><br>
>>>>><br>
>>>>> ------------------------------<br>
<div class="im">>>>>> If you reply to this email, your message will be added to the<br>
>>>>> discussion below:<br>
>>>>><br>
>>>>> <a href="http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002378.html" target="_blank">http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002378.html</a><br>
>>>>> To unsubscribe from Why postgis doesn't use index during query?, click<br>
>>>>> here.<br>
</div>>>>>> NAML<<a href="http://postgis.17.n6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml" target="_blank">http://postgis.17.n6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml</a>><br>
>>>>><br>
>>>><br>
>>>><br>
>>>> ------------------------------<br>
<div class="im">>>>> View this message in context: Re: Why postgis doesn't use index during<br>
</div>>>>> query?<<a href="http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002380.html" target="_blank">http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002380.html</a>><br>
>>>><br>
>>>> Sent from the PostGIS - User mailing list archive<<a href="http://postgis.17.n6.nabble.com/PostGIS-User-f3516033.html" target="_blank">http://postgis.17.n6.nabble.com/PostGIS-User-f3516033.html</a>>at Nabble.com.<br>
<div class="HOEnZb"><div class="h5">>>>><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>
>>>><br>
>>>><br>
>>><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>
>>><br>
>>><br>
>><br>
><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>
><br>
><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>
</div></div></blockquote></div><br></div>