From: | René Fournier <renefournier(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Best PostGIS function for finding the nearest line segment to a given point |
Date: | 2011-10-08 07:45:30 |
Message-ID: | E1841DD9-5F6F-4172-B5F5-1007EE802105@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
…Rene
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2011-10-08 08:26:58 | Re: Best PostGIS function for finding the nearest line segment to a given point |
Previous Message | Craig Ringer | 2011-10-08 06:25:57 | Re: Getting PostGIS 1.5.3 working with Postgresql90 (Macports) |