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
