From: | Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> |
---|---|
To: | René Fournier <renefournier(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Best PostGIS function for finding the nearest line segment to a given point |
Date: | 2011-10-08 08:26:58 |
Message-ID: | CAP_rww=LxzRuUAUXfurgPPyqUeprjKziQcux9kYC=xh=LhVJsA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2011/10/8 René Fournier <renefournier(at)gmail(dot)com>
> Wow, have to say, I love Postgresql and PostGIS. Just awesome.
>
> So I have a table with ~400,000 rows, each representing a road or street
> (multi line segment). I want to select the row whose line segment is closest
> the a given point. The following query...
>
> gc3=# SELECT r_stname_c, r_placenam,
> ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
> AS distance FROM nrn_ab_8_0_roadseg ORDER BY distance ASC LIMIT 1;
>
>
> ...works and produces...
>
> r_stname_c | r_placenam | distance
> ----------------------+------------+----------------------
> 19 Avenue North-east | Calgary | 5.74515867479735e-05
>
> …but seems a little slow (yes, there is a GIST index on the_geom). Explain
> shows:
>
> gc3=# explain SELECT r_stname_c, r_placenam,
> ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
> AS distance FROM nrn_ab_8_0_roadseg ORDER BY distance asc limit 1;
> QUERY PLAN
>
>
> -----------------------------------------------------------------------------------------------------------
> Limit (cost=128520.06..128520.06 rows=1 width=464)
> -> Sort (cost=128520.06..129493.57 rows=389404 width=464)
> Sort Key:
> (st_distance('0101000020AD100000F5BEF1B567835CC06A2E3718EA884940'::geometry,
> the_geom))
> -> Seq Scan on nrn_ab_8_0_roadseg (cost=0.00..126573.04
> rows=389404 width=464)
> (4 rows)
>
>
> Any suggests how to speed it up? Coming from MySQL, I'm brand-new to
> PostGIS (and Postgresql FWIW) and all the awesome spatial functions it has.
> I would think that maybe selecting a bounding box of rows, and then finding
> the one with the closest distance?
>
>
Yes exactly. That's how people do it now, in pre-PostGIS-2.0 era :-)
Make a search by bounding boxes, starting with some arbitraly selected
radius. Increase the radius until you have at least N=1 result found, than
sort these results by ST_Distance and select nearest neighbour.
PostGIS 2.0 solution: see
http://blog.opengeo.org/2011/09/28/indexed-nearest-neighbour-search-in-postgis/
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2011-10-08 11:19:22 | PG 9.1 on Debian Squeeze |
Previous Message | René Fournier | 2011-10-08 07:45:30 | Best PostGIS function for finding the nearest line segment to a given point |