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"