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"
dmci
  • 4,882
  • 2
  • 19
  • 31
John Mitchell
  • 201
  • 1
  • 3
  • Describe the tables, also post the EXPLAIN ANALYZE of the query. – nickves Apr 14 '13 at 23:16
  • See http://www.bostongis.com/?content_name=postgis_nearest_neighbor_generic , http://gis.stackexchange.com/questions/14456/finding-the-closest-geometry-in-postgis post says it "fastest" way to get nearest neighbor, and after that use ST_Distance or modify function to return id and distance – simpleuser001 Aug 14 '13 at 07:52

1 Answers1

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"
wittich
  • 2,356
  • 1
  • 16
  • 30
Nicklas Avén
  • 13,241
  • 1
  • 39
  • 48
  • 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