1

I use OSM data. I need to find nearest street for GPS point. I wrote next SQL query:

SELECT 
     osm_id, 
     name,  
     ST_DISTANCE(geometry, ST_SetSRID(ST_POINT(37.72308, 55.47957), 4326), true) as dist 
FROM 
     roads 
WHERE 
     ST_DWithin(geometry, ST_SetSRID(ST_POINT(37.72308, 55.47957), 4326), 1) ORDER BY dist LIMIT 1;

But it's execution is very slow. It's take 7.5 seconds for not big territory. Table structure is next:

newdb=# \d roads
                                   Table "public.roads"
  Column  |           Type            |                     Modifiers                      
----------+---------------------------+----------------------------------------------------
 id       | integer                   | not null default nextval('roads_id_seq'::regclass)
 osm_id   | bigint                    | 
 type     | character varying         | 
 name     | character varying         | 
 tunnel   | smallint                  | 
 bridge   | smallint                  | 
 oneway   | smallint                  | 
 ref      | character varying         | 
 z_order  | integer                   | 
 access   | character varying         | 
 service  | character varying         | 
 class    | character varying         | 
 geometry | geometry(LineString,4326) | 
Indexes:
    "roads_pkey" PRIMARY KEY, btree (id)
    "roads_geography" gist (geometry)
    "roads_geom" gist (geometry)

is there any way to increase speed of the query?

PostGIS 2.2 PostgreSQL 9.6

2

The problem is, that your query calculates the distance using ST_DISTANCE from your point to every road in a 1° distance (~ 100 km). This is slow!

You can instead use the <-> operator which makes use of indexes:

SELECT
  osm_id, 
  name,
  ST_DISTANCE(geometry, ST_SetSRID(ST_POINT(37.72308, 55.47957), 4326), true) as dist 
FROM roads
ORDER BY geometry <-> ST_SetSRID(ST_POINT(37.72308, 55.47957), 4326)
LIMIT 1;

From the docs:

The <-> operator returns the 2D distance between two geometries. Used in the "ORDER BY" clause provides index-assisted nearest-neighbor result sets.

  • thanks! Should I write geometry::geography or geometry is enough? – Dmitry Bubnenkov Apr 13 '17 at 9:26
  • geometry is enough, otherwise you would need add ::geography also to your point to compare them. – pLumo Apr 13 '17 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.