Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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) 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 bostongis.com/?content_name=postgis_nearest_neighbor_generic , gis.stackexchange.com/questions/14456/… 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
add comment

1 Answer 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"
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 at 11:04
add comment

Your Answer

 
discard

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.