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
