Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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"
share|improve this question
Describe the tables, also post the EXPLAIN ANALYZE of the query. – nickves Apr 14 '13 at 23:16
See ,… post says it "fastest" way to get nearest neighbor, and after that use ST_Distance or modify function to return id and distance – simplexio Aug 14 '13 at 7:52

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"
share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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