Answer the question
In order to leave comments, you need to log in
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;
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)
Answer the question
In order to leave comments, you need to log in
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)
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question