<div dir="ltr"><div><div>Damn I'm really tired.<br></div>Second join is useless.<br></div>So you end up like this : <br><div><br><div><div><div class="im"><div>WITH my_input_polygon AS (</div>
<div><span style="white-space:pre-wrap"> </span>SELECT geom</div><div><span style="white-space:pre-wrap"> </span>FROM sdgis.parcels4326 AS parcels</div><div>
<span style="white-space:pre-wrap"> </span>WHERE apn = 3500600300</div><div>)<br></div></div><div>,filtering_with_geometry AS (<br></div><div class="im"><div style="margin-left:40px">SELECT mip.geom AS target, p.geom <br>
</div><div style="margin-left:40px">FROM my_input_polygon AS mip</div>
</div><div style="margin-left:40px">INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,2*100)=TRUE)<br></div>)
</div>SELECT fw.geom <br>FROM filtering_with_geometry AS fw<br></div>WHERE ST_DWITHIN(target::<div dir="ltr">geography,fw.geom::geography,<div>100)=TRUE</div></div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">
2014-07-10 18:54 GMT+02:00 Alexander W. Rolek <span dir="ltr"><<a href="mailto:a.rolek@gmail.com" target="_blank">a.rolek@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">This looks great. I will give it a run tonight and let you know the results. I really appreciate the suggestions. </div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">
On Thu, Jul 10, 2014 at 9:36 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>Oups, sent it to early.<br></div>If you trust the planner you can simply try :<br><br><div><div><div>
<div><div>WITH my_input_polygon AS (</div>
<div><span style="white-space:pre-wrap"> </span>SELECT geom</div><div><span style="white-space:pre-wrap"> </span>FROM sdgis.parcels4326 AS parcels</div><div>
<span style="white-space:pre-wrap"> </span>WHERE apn = 3500600300</div><div>) <br></div></div></div><div>SELECT mip.*<br>FROM my_input_polygon AS mip
</div><div><div style="margin-left:40px">INNER JOIN sdgis.parcels4326 AS p ON <br>(<br></div></div><div style="margin-left:40px"><div style="margin-left:40px">(ST_DWITHIN(mip.geom,p.geom,2*100)=TRUE) <br></div></div>
<div style="margin-left:40px">
AND (ST_DWITHIN(mip.geom::geography,p.geom::geography, <br></div></div><div><div>100)=TRUE)<br><div style="margin-left:40px">)<br></div></div> <br></div><div>Cheers,<br>Rémi-C<br></div></div><div class="gmail_extra"><br>
<br>
<div class="gmail_quote">2014-07-10 18:33 GMT+02:00 Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span>:<div><div><br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr"><div><div>A single statement <br></div>, the 2 step is only metaphorical (or can be enforced if you use CTE).<br></div><div>Somehting liek this (untested)<br><br></div><div><div><div><div>WITH my_input_polygon AS (</div>
<div><span style="white-space:pre-wrap"> </span>SELECT geom</div><div><span style="white-space:pre-wrap"> </span>FROM sdgis.parcels4326 AS parcels</div><div>
<span style="white-space:pre-wrap"> </span>WHERE apn = 3500600300</div><div>)<br></div></div><div>,filtering_with_geometry AS (<br></div><div><div style="margin-left:40px">SELECT p.*</div><div style="margin-left:40px">
FROM my_input_polygon AS mip</div>
</div><div style="margin-left:40px">INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,2*100)=TRUE)<br></div>)
</div>SELECT fw.*<br>FROM filtering_with_geometry AS fw<br></div>INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom::geography,p.geom::geography,<div>100)=TRUE)</div><div><div><br>Cheers,<br>Rémi-C<br></div></div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-07-10 18:01 GMT+02:00 Alexander W. Rolek <span dir="ltr"><<a href="mailto:a.rolek@gmail.com" target="_blank">a.rolek@gmail.com</a>></span>:<div><div>
<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">The 2 step query is an interesting idea. Would I need to do that in 2 queries, or can that be accomplished in a single statement?<div><br></div></div><div><div><div class="gmail_extra">
<br><br><div class="gmail_quote">
On Thu, Jul 10, 2014 at 8:58 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:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr"><div><div><div><div><div>Hey,<br></div>I'm glad you found out.<br></div>I never tried but you could probably try to index on the result of the cast :<br></div>CREATE INDEX ON ... USING GIST ((CAST geom AS GEOGRAPHY))<br>
.<br></div><div><br></div>However if your distance are not that big (compaired to earth curve), stick to geometry.<br></div>If your distances are big, go geography. <br>You could also simply do it in 2 steps, a first step with geometry and maybe 2 times the distance to be sure on all the table, a second step more precise with geography on only the result of the previous filter.<br>
<br>Cheers,<br>Rémi-C<br><div><div><br></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-07-10 17:47 GMT+02:00 Alexander W. Rolek <span dir="ltr"><<a href="mailto:a.rolek@gmail.com" target="_blank">a.rolek@gmail.com</a>></span>:<div>
<div><br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Remi -<div><br></div><div>Thanks again for the quick response. I dug into this a bit more last night, and apparently the issue was the distance I was using with the ST_DWithin call. When I had a projection under 2230, the unit of measure was feet. With 4326 it appears to be degrees. I kept the distance of 100 on both tests, so the 2230 project returned quick, but a distance of 100 on a 4326 projection is very large so it was returning my whole table. When I changed the distance to something small (0.0009) my query is now fast. ;-)</div>
<div><br></div><div>My next challenge will be figuring out the best way to convert between degrees & meters. I understand the geography type is better for queries like this, but I'm already using the geometry type for other queries. Is it common to have both geometry and geography for a record in a table? I know I can cast using ::geography but the queries are dramatically slower.</div>
<div><br></div><div>Alex </div></div><div class="gmail_extra"><div><div><br><br><div class="gmail_quote">On Thu, Jul 10, 2014 at 1:11 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:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div><div><div><div><div><div><div><div><div>Hey,<br></div></div>If I take into account your last mail, <br>
you probably have forget to transform your data.<br><br></div>If you have a table in srid 4326 <br>
, you can't use a transform in your querry if you want it to uses indexes.<br></div>OR you have to buil and index like this : <br>CREATE INDEX ON sdgis.parcels4326 USING GIST (ST_Transform(geom, 4326));<br><br></div>
So can you confirm thatl your geom have the correct srid in the correct table? <br>
<br></div>SELECT DISTINCT ST_SRID(geom)<br></div>FROM sdgis.parcels ;<br><br></div>and <br>SELECT DISTINCT ST_SRID(geom)<br>FROM sdgis.parcels4326 ;<br><br></div>You uses pgadmin , so instead of running your querry <br>, run it with "explain analyse" (Select the text of your querry, then press shift+F7).<br>
</div>You should see a graphic explaining what it happening. You should look for sequential scans.<br><br></div>You can also try the same querry without CTE :<br><br><div><div>SELECT p.*</div><div>FROM (<div><div>
<span style="white-space:pre-wrap"> </span>SELECT geom</div>
<div><span style="white-space:pre-wrap"> </span>FROM sdgis.parcels4326 AS parcels</div>
<span style="white-space:pre-wrap"> </span>WHERE apn = '3500600300<br></div>) AS mip</div><div><div>INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);</div>
</div></div><div><br></div><br><div><div><div><div><div><div><div><div><div><div><div><div>Of course vacuum analyze both table before running the querry.<br>It would be difficult to help you more without detailed information (tables declarations, querry used, result of explain analyse), because slowness can come from several reasons .<br>
<br></div><div>Here is a link of requirements for people having slow querry on postgres mailing list (<a href="https://wiki.postgresql.org/wiki/Slow_Query_Questions" target="_blank">https://wiki.postgresql.org/wiki/Slow_Query_Questions</a>). <br>
</div><div>I'm not guru so I couldn't use all this informations but that give you an idea of how many reasons of slow query they can be<br></div><div><br>Cheers,<br></div><div>Rémi-C<br></div><div><br><br><br><br>
""<br><div dir="ltr">I tried the query that Remi suggested (thank you!) and it
works and doesn't work. I have the exact same table in two different
projections (2230, 4326). When I run the ST_Dwithin query against the
2230 table, I get results in under 100 ms. When I run the same query
against the 4326 projection it still takes around 270 seconds! I have
confirmed that I have the gist index on the 4326 table. Any ideas why
the 4326 projection would be dramatically slower?</div>
<br>""<br><div class="gmail_extra"><br><br><div class="gmail_quote">2014-07-09 18:38 GMT+02:00 Alexander W. Rolek <span dir="ltr"><<a href="mailto:a.rolek@gmail.com" target="_blank">a.rolek@gmail.com</a>></span>:<div>
<div><br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Remi - <div><br></div><div>Thanks for the quick response. Sorry to respond to you directly, but my message settings are set on digest so I can't respond to the thread yet.</div>
<div><br></div><div>I went through your steps, and have built out my query, but it's still taking around 270 seconds to run the query, and it's returning ever record in my table. Here's my query:</div>
<div><br></div><div><div>WITH my_input_polygon AS (</div><div><span style="white-space:pre-wrap"> </span>SELECT geom</div><div><span style="white-space:pre-wrap"> </span>FROM sdgis.parcels4326 AS parcels</div><div>
<span style="white-space:pre-wrap"> </span>WHERE apn = '3500600300'</div><div>)</div><div>SELECT p.*</div><div>FROM my_input_polygon AS mip</div><div>INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);</div>
</div><div><br></div><div>I also have indexes on the apn and the geom columns (see attached screen shots)</div><div><br></div><div>Any ideas what I'm missing here?</div><div><br></div><div>Thanks again for the help</div>
<span><font color="#888888">
<div><br></div><div>-- <br>Alexander W. Rolek<br></div></font></span></div>
</blockquote></div></div></div><br></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
</blockquote></div><br><br clear="all"><div><br></div></div></div><span><font color="#888888">-- <br>Alexander W. Rolek<br><a href="tel:303-829-9989" value="+13038299989" target="_blank">303-829-9989</a>
</font></span></div>
</blockquote></div></div></div><br></div>
</blockquote></div><br><br clear="all"><div><br></div>-- <br>Alexander W. Rolek<br><a href="tel:303-829-9989" value="+13038299989" target="_blank">303-829-9989</a>
</div>
</div></div></blockquote></div></div></div><br></div>
</blockquote></div></div></div><br></div></div>
</blockquote></div><br><br clear="all"><div><br></div>-- <br>Alexander W. Rolek<br>303-829-9989
</div>
</div></div></blockquote></div><br></div>