How can I make a PostGIS ST_Distance_sphere query more efficient?

Question

Within PostgreSQL/PostGIS I have written a SQL query to get the closest distance for each element within a point coordinate table from a polyline table. The query comes back and works correctly, but it takes about 1 minute per record (see SQL statement below note: I have limited it to the first 10 records so that I can derive how long it would take per record otherwise it would take days to process all 366,681 records). I have created a GIST index on both the "the_geom" table fields.

The other table which is polyline has 369,962 records.

How can I make a PostgreSQL/PostGIS ST_Distance_sphere query more efficient?

SELECT "ParAdd", "ParZip", min(distance) FROM 
  (SELECT a."ParAdd", a."ParZip", ST_Distance_sphere(a.the_geom, b.the_geom) distance FROM 
    (SELECT "ParAdd", "ParZip", the_geom
      FROM reflux_blue_06_21_2012_15147_va_md_premise_res_prosp_filtered_t limit 10
    ) a, 
    ( SELECT the_geom FROM reflux_blue_06_21_2012_15147_va_md_premise_gis_main_pipe_4326
    ) b
  ) as testing group by "ParAdd", "ParZip" ORDER BY "ParAdd", "ParZip"

Answer 1

try casting to geography type instead. I think the faster geography distance calculation is released. But maybe it is just in trunk.

SELECT "ParAdd", "ParZip", min(distance) FROM 
  (SELECT a."ParAdd", a."ParZip", ST_Distance(a.the_geom::geography, b.the_geom::geography) distance FROM 
    (SELECT "ParAdd", "ParZip", the_geom
      FROM reflux_blue_06_21_2012_15147_va_md_premise_res_prosp_filtered_t limit 10
    ) a,
    (SELECT the_geom
      FROM reflux_blue_06_21_2012_15147_va_md_premise_gis_main_pipe_4326
    ) b
  ) AS testing GROUP BY "ParAdd", "ParZip" ORDER BY "ParAdd", "ParZip"