1

(I'm very new to (Post)GIS in general, and I couldn't find an existing answer to this (or just don't know the right terminology) -- sorry if it's already been asked & answered)

I have a bunch of company addresses, each with a Point and a range of operations (radius in m):

create table public.company_addresses (
  id bigserial not null,
  -- some more data
  range_of_operation int4 not null,
  "location" geometry(POINT, 4326) NULL,
);

CREATE INDEX company_addresses_location_index ON public.company_addresses USING gist (location);

Now I have some other Point (long,lat) and I want to find all the company addresses in which the circle from their Point + radius overlaps with that point.

I've been able to do this with:

select id from company_addresses ca where ST_DWithin(ca."location"::geography, st_setsrid(st_makepoint(long, lat), 4326), ca.range_of_operation);

I was wondering if this is the best way to do this? Should I store the data differently?

  • the index on the field will never be used as you are not using the said field. Instead, you would need an index on ca."location"::geography (the transformed field, using the same transformation as in your query). To make sure it is usable, try your query while sequential scans are disabled SET enable_seqscan = OFF; – JGH Mar 18 at 22:46
2

St_Dwhithin is the way to go for this kind of query, as the manual says:

[Note]
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

so, you can improve your performance by storing your data as geography on the table, and creating GIST INDEXES

see: https://postgis.net/docs/using_postgis_dbmanagement.html#gist_indexes

GiST stands for "Generalized Search Tree" and is a generic form of indexing. In addition to GIS indexing, GiST is used to speed up searches on all kinds of irregular data structures (integer arrays, spectral data, etc) which are not amenable to normal B-Tree indexing.

Once a GIS data table exceeds a few thousand rows, you will want to build an index to speed up spatial searches of the data (unless all your searches are based on attributes, in which case you'll want to build a normal index on the attribute fields).

The syntax for building a GiST index on a "geometry" column is as follows:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); 

The above also applies to the geography datatype.

  • Thanks for the reply. I already had a gist index on the field, but I don't think I'll be exceeding a few thousand rows any time soon, so it'll mostly be doing sequential scans for a while. I'll keep using ST_DWithin then! – randyr Mar 18 at 22:19
  • @Randall, note that your index is over the geometry field, you are casting it to geography inside the ST_DWithin call, use Postgres EXPLAIN to see if the index is used on your query. You probbly should CREATE INDEX ... USING GIST ( location::geography ) – Javier JC Mar 18 at 22:51
  • I hadn't even noticed that, thanks for point it out. I'll test it out tomorrow. – randyr Mar 18 at 23:03

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.