6

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"
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"
  • I think ST_Distance_Sphere(a.the_geom, b.the_geom) is equivalent to ST_Distance(a.the_geom::geography, b.the_geom::geography). ST_Distance_Sphere is just a wrapper around the geography version of ST_Distance. – dbaston Apr 11 '14 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.