Within Postgres/PostGIS I have written an 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 Postgres/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) distanceFROM (select "ParAdd", "ParZip", the_geomfrom reflux_blue_06_21_2012_15147_va_md_premise_res_prosp_filtered_t limit 10) a,(select the_geomfrom reflux_blue_06_21_2012_15147_va_md_premise_gis_main_pipe_4326) b) as testing group by "ParAdd", "ParZip" ORDER BY "ParAdd", "ParZip"
أكثر...
The other table which is polyline has 369,962 records.
How can I make a Postgres/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) distanceFROM (select "ParAdd", "ParZip", the_geomfrom reflux_blue_06_21_2012_15147_va_md_premise_res_prosp_filtered_t limit 10) a,(select the_geomfrom reflux_blue_06_21_2012_15147_va_md_premise_gis_main_pipe_4326) b) as testing group by "ParAdd", "ParZip" ORDER BY "ParAdd", "ParZip"
أكثر...