<div dir="ltr"><div><div><div><div>Hey,<br></div>please _don't_ use st_buffer<br>, but ST_DWithin (<a href="http://postgis.net/docs/ST_DWithin.html">http://postgis.net/docs/ST_DWithin.html</a>).<br><br>This querry should be around 30 ms.<br>
<br>You need to add an index to the geometry :<br>CREATE INDEX parcels_geom_gist_index ON sdgis.parcels4326 USING GIST(geom) ;<br><br>Now if you want to use the field "id", you also need an index on it (unless it is already a primary key):<br>
CREATE INDEX parcels_id_btree_index ON sdgis.parcels4326 (id) ;<br><br>Depending on your use case you may need to use the GEOGRAPHY type instead of the geometry.<br><br>Your querry could look like this (there could be more efficicient way, at the price of readability)<br>
<br>WITH my_input_polygon AS (<br></div>SELECT *<br></div>FROM sdgis.parcels4326 AS parcels<br></div>WHERE id = #ID_OF_TARGET_POLYGON#<br><div><div><div>)<br>SELECT p.*<br>FROM my_input_polygon AS mip<br>INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);<br>
<br>It sholdn't take more than few dozen milliseconds.<br><br>Cheers,<br>Rémi-C<br><br><br></div></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-07-09 9:25 GMT+02:00 Guido Lemoine <span dir="ltr"><<a href="mailto:guido.lemoine@jrc.ec.europa.eu" target="_blank">guido.lemoine@jrc.ec.europa.eu</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div link="blue" vlink="purple" lang="EN-GB"><div><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Alexander,<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">You would want to check out ST_BUFFER, which is doing what you intend to do.<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">Guido<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"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"" lang="EN-US"> <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Alexander W. Rolek<br>
<b>Sent:</b> 09 July 2014 05:36<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>; Jeffrey Johnson<br><b>Subject:</b> [postgis-users] Finding records withing XX distance from a geometry in the same table<u></u><u></u></span></p>
<div><div class="h5"><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">I'm trying to query all the records within XX distance from a geometry. I would like to be able to reference the subject geometry via it's id, and then find every geometry that is either:<u></u><u></u></p>
<div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">A) within XX distance from a x/y coordinate of the geometry<u></u><u></u></p></div><div><p class="MsoNormal">B) within XX distance from the edge of the geometry on all sides. (ideal)<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">What is the best way to accomplish this? My current query:<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div>
<div><p class="MsoNormal">SELECT *<u></u><u></u></p></div><div><p class="MsoNormal"> FROM sdgis.parcels4326 AS parcels<u></u><u></u></p></div><div><p class="MsoNormal"> WHERE ST_Distance_Sphere(<u></u><u></u></p>
</div><div><p class="MsoNormal"> parcels.geom,<u></u><u></u></p></div><div><p class="MsoNormal"> ST_Transform(ST_SetSRID(ST_MakePoint('6255896.314288','1898596.929109'),2230),4326)<u></u><u></u></p>
</div><div><p class="MsoNormal"> ) < 100.0;<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">This takes a few minutes to run on a table with just over 1 million records. I would like to swap out the ST_MakePoint, with a multipolygon from a record that is looked up via it's id. <u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Any suggestions are greatly appreciated.<u></u><u></u></p></div><div><div><p class="MsoNormal"><u></u> <u></u></p></div><p class="MsoNormal" style="margin-bottom:12.0pt">
-- <br>Alexander W. Rolek<u></u><u></u></p></div></div></div></div></div></div></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" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>