Re: Best PostGIS function for finding the nearest line segment to a given point

[Filip Rembiałkowski <plk.zuber@xxxxxxxxx>]

2011/10/8 René Fournier <renefournier@xxxxxxxxx>

Thanks. Based on some further reading, this is what I came up with, in order to hopefully use the GiST index to greatest benefit:

gc3=# SELECT datasetnam, r_hnumf, r_hnuml, r_stname_c, r_placenam, ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom) AS distance 

gc3-# FROM nrn_ab_8_0_roadseg 

gc3-# WHERE the_geom && SetSRID('BOX3D(-114.1 49.9,-113.9 51.1)'::box3d,4269)

gc3-# ORDER BY distance ASC LIMIT 5;

 datasetnam | r_hnumf | r_hnuml |      r_stname_c      | r_placenam |       distance       

------------+---------+---------+----------------------+------------+----------------------

 Alberta    |     407 |     459 | 19 Avenue North-east | Calgary    | 5.74515867479735e-05

 Alberta    |    2004 |    2004 | 4 Street North-east  | Calgary    | 0.000663366090673065

 Alberta    |       0 |       0 | 4 Street North-east  | Calgary    | 0.000667603774783403

 Alberta    |     425 |     425 | 18 Avenue North-east | Calgary    | 0.000835708003512673

 Alberta    |     407 |     449 | 20 Avenue North-east | Calgary    | 0.000981910679856406

(5 rows)

gc3=# EXPLAIN SELECT datasetnam, r_hnumf, r_hnuml, r_stname_c, r_placenam, ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom) AS distance 

gc3-# FROM nrn_ab_8_0_roadseg 

gc3-# WHERE the_geom && SetSRID('BOX3D(-114.1 49.9,-113.9 51.1)'::box3d,4269)

gc3-# ORDER BY distance ASC LIMIT 5;

                                                                                                                          QUERY PLAN                                                                                                                          

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=33632.15..33632.16 rows=5 width=480)

   ->  Sort  (cost=33632.15..33693.00 rows=24341 width=480)

         Sort Key: (st_distance('0101000020AD100000F5BEF1B567835CC06A2E3718EA884940'::geometry, the_geom))

         ->  Bitmap Heap Scan on nrn_ab_8_0_roadseg  (cost=812.99..33227.85 rows=24341 width=480)

               Recheck Cond: (the_geom && '0103000020AD10000001000000050000006666666666865CC03333333333F348406666666666865CC0CDCCCCCCCC8C49409A99999999795CC0CDCCCCCCCC8C49409A99999999795CC03333333333F348406666666666865CC03333333333F34840'::geometry)

               ->  Bitmap Index Scan on nrn_ab_8_0_roadseg_the_geom_gist  (cost=0.00..806.91 rows=24341 width=0)

                     Index Cond: (the_geom && '0103000020AD10000001000000050000006666666666865CC03333333333F348406666666666865CC0CDCCCCCCCC8C49409A99999999795CC0CDCCCCCCCC8C49409A99999999795CC03333333333F348406666666666865CC03333333333F34840'::geometry)

(7 rows)

Does this appear optimal to you?

I think it's closer to optimal. The real question is: is this fast enough for your application?

Can you show EXPLAIN (ANALYZE on,BUFFERS on) result?