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

[Paul Ramsey]

Try this:

-- 
-- ST_DWithin_Sphere(lonlat-point, lonlat-point, radius-in-meters)
returns boolean
--
-- Meters/Degree @ 60N: select 1/distance_sphere('POINT(0
60)','POINT(1 60)') = 1.79866403673916e-05
--
CREATE OR REPLACE FUNCTION ST_DWithin_Sphere(geometry, geometry, float8)
        RETURNS boolean
        AS 'SELECT $1 && ST_Expand($2,$3 * 1.79866403673916e-05) AND
$2 && ST_Expand($1,$3 * 1.79866403673916e-05) AND
ST_Distance_Sphere($1, $2) < $3'
        LANGUAGE 'SQL' IMMUTABLE;


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(m.the_geom, c.the_geom)
AS numeric), 2) AS dist_meters
FROM market_locations m, customer_locations c
WHERE st_dwithin_sphere(m.the_geom, c.the_geom, 2414.016)