B
B
beduin012017-04-13 11:44:44
PostgreSQL
beduin01, 2017-04-13 11:44:44

Is it possible to speed up the execution of a spatial query?

I'm trying to speed up the processing of the following spatial 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;

The request is executed in a small area of ​​7.5 seconds.
The structure of the roads table is as follows:
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)

Here you can see that there is a spatial geometry index. The question is, in PostgreSQL terminology, geometry and geography are the same or not? If not, how to create a geographic index?
How it is possible to increase speed of execution of the given request?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Korotkov, 2017-05-09
@smagen

To be able to use Fast Nearest Neighbor Search (KNN GiST), do an ORDER BY on the expression

geometry <-> ST_SetSRID(ST_POINT(37.72308, 55.47957), 4326)

Requires PostgreSQL 9.5+, PostGIS 2.2+

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question