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