<div><font class="Apple-style-span" color="#500050" face="arial, sans-serif"><span class="Apple-style-span" style="border-collapse: collapse;">Ooops. sorry thought I'd pasted that last time.</span></font></div><div><font class="Apple-style-span" color="#500050" face="arial, sans-serif"><span class="Apple-style-span" style="border-collapse: collapse;"><br>
</span></font></div><div><span class="Apple-style-span" style="font-family: arial, sans-serif; font-size: 12px; border-collapse: collapse; color: rgb(80, 0, 80); ">select st_srid(the_geom), st_astext(the_geom) from market_locations limit 1;</span></div>
<div>4326<span class="Apple-tab-span" style="white-space:pre"> </span>POINT(-77.1042756692815 38.995397678429)</div><div><br></div><div><span class="Apple-style-span" style="font-family: arial, sans-serif; font-size: 12px; border-collapse: collapse; color: rgb(80, 0, 80); ">select st_srid(the_geom), st_astext(the_geom) from customer_locations limit 1;</span></div>
<div><div>4326<span class="Apple-tab-span" style="white-space:pre"> </span>POINT(-100.000971435723 45.0003533555503)</div><div><br></div></div><div>- bo</div><br><div class="gmail_quote">On Thu, Jul 9, 2009 at 12:40 PM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Could you run the two queries and show the results, just to be sure?<br>
<div class="im"><br>
select st_srid(the_geom), st_astext(the_geom) from market_locations limit 1;<br>
select st_srid(the_geom), st_astext(the_geom) from customer_locations limit 1;<br>
<br>
</div><div><div></div><div class="h5">On Thu, Jul 9, 2009 at 9:24 AM, Bo Coughlin<<a href="mailto:bo@rekindl.com">bo@rekindl.com</a>> wrote:<br>
> Thanks for the reply. Both are set to 4326 (both meaning both tables and<br>
> all records). - bo<br>
><br>
><br>
> On Thu, Jul 9, 2009 at 12:04 PM, Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>><br>
> wrote:<br>
>><br>
>> Two key points:<br>
>><br>
>> - Spatial indexes can't operate once you wrap the column in a<br>
>> function. The index is on 'value' not on 'f(value)'.<br>
>> - You are transforming into geographic coordinates (4326 == units of<br>
>> degree) and then running a dwithin test with a radius of 2414. The<br>
>> world is only 360 degrees wide. This term is providing no selectivity<br>
>> at all.<br>
>><br>
>> You are not understanding/interpreting your coordinates/srids<br>
>> correctly is my guess. What are the units of the coordinates of<br>
>> market_locations and customer_locations?<br>
>><br>
>> select st_srid(the_geom), st_astext(the_geom) from market_locations limit<br>
>> 1;<br>
>><br>
>> select st_srid(the_geom), st_astext(the_geom) from customer_locations<br>
>> limit 1;<br>
>><br>
>> P.<br>
>><br>
>><br>
>> On Thu, Jul 9, 2009 at 8:46 AM, Bo Coughlin<<a href="mailto:bo@rekindl.com">bo@rekindl.com</a>> wrote:<br>
>> > Have over a million POINT (the_geom) records located in two separate<br>
>> > tables<br>
>> > - x.table & y.table. x.table 50,000 records; y.table 995,000 records.<br>
>> > Distances required are all less than 1.5 miles (2414 meters) : all<br>
>> > locations<br>
>> > are located within a specific region of the US.<br>
>> > GiST index on the_geom<br>
>> > Goal: distance from all y.the_geom to x.the_geom - (shortest distance).<br>
>> > And<br>
>> > then place this distance back into each respective y.table.record<br>
>> > (currently creating temp table to then update y.table)<br>
>> > Current sql:<br>
>> > SELECT <a href="http://m.id" target="_blank">m.id</a> AS mid,<br>
>> > m.building_i AS mb_id,<br>
>> > <a href="http://c.id" target="_blank">c.id</a> AS cid, c.building_i AS cb_id,<br>
>> > m.streetaddr AS m_address,<br>
>> > m.city AS m_city,<br>
>> > m.state AS m_state,<br>
>> > m.zip AS m_zip,<br>
>> > m.zip4 AS m_zip4,<br>
>> > round(CAST(<br>
>> > ST_Distance_Sphere(<br>
>> > ST_Transform(m.the_geom,4326),<br>
>> > ST_Transform(c.the_geom,4326)<br>
>> > )<br>
>> > AS numeric<br>
>> > )<br>
>> > ,2) AS dist_meters<br>
>> > FROM market_locations m, customer_locations c<br>
>> > WHERE st_dwithin(ST_Transform(m.the_geom,4326),<br>
>> > ST_Transform(c.the_geom,4326) ,2414.016)<br>
>> > I tried ST_Spheroid as well but after 28 hours I killed it. This<br>
>> > current<br>
>> > query (ST_Sphere) has been working for .... 14 hrs. Question is, should<br>
>> > this be expected in terms of overhead and time, are there areas I might<br>
>> > be<br>
>> > able to tighten to increase performance on the query (additional btree<br>
>> > index<br>
>> > etc.)<br>
>> > System Information: PostgreSQL 8.3.7 on i386-apple-darwin8.11.1,<br>
>> > compiled by<br>
>> > GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build<br>
>> > 5370)<br>
>> > Thanks to any and all for assistance.<br>
>> > - bo<br>
>> ><br>
>> > _______________________________________________<br>
>> > postgis-users mailing list<br>
>> > <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>> > <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>> ><br>
>> ><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
>><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
</div></div></blockquote></div><br>