CODEWITHSUNDEEP
postgresqlgispostgis
Fionn
Fionn

Reputation: 11265

PostGIS finding all circles containing a specific point - how to define an index

I want to find all points which are not farther than a point specific distance away from a given point. Like a normal radius search, only the radius is variable with each point (Each entry is defining a circle around itself using a point + radius).

The question I have is how to define an index for this problem (the query I use at the moment is below), also if it is possible to create an index using non GIS and GIS fields (like the Enable field below).

I currently have this working query for it

SELECT "Locations"."Name",
FROM public."Locations"
WHERE
    "Locations"."Enabled" = TRUE
    AND ST_DWithin(
        "Locations"."MyCoord"::geography,
        ST_SetSRID(ST_MakePoint(<Given Long>, <Given Lat>),4326)::geography,
        "Locations"."Radius");

with a table like the following

Id: uuid
Name: text
Enabled: boolean
MyCoord: GEOGRAPHY(Point)
Radius: double precision

Upvotes: 1

Views: 391

Answers (1)

Jendrusk
Jendrusk

Reputation: 813

Create index some_name_idx on public."Locations" using gist("MyCoord")

But you have a BUG - there is no such function ST_DWithin(geometry,geography,numeric) - if you have radius in meters use geography in both cases, so you don't need a conversion (also in index)

EDIT:

Don't worry - it'll work, I checked it... as a proof, I've got a table planet_osm_point created by osm2pgsql with index:

CREATE INDEX planet_osm_point_index
  ON planet_osm_point
  USING gist
  (way);

And I'm running a query like yours:

explain select * 
from planet_osm_point 
where st_dwithin(way,ST_geomfromtext('POINT(2219360.7 6457010.96)'),300)

Output is here. As you see spatial index is in use...

Upvotes: 1

Related Questions