[postgis-users] st_dist, st_sphere, st_spheroid - overhead/efficiency question

[Bo Coughlin]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090709/c00d8f80/attachment.html>