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
