Have Geek Brain

Find users quickly using PostGIS

I have 5 tables:

- users - information about user with current location_id (fk to geo_location_data)
- geo_location_data - information about location, with PostGIS geography(POINT, 4326) column
- user_friends - relationships between users.

I want to find close friends for the current user, but it takes a long time to execute the select query to find out if the user is a friend and then select with ST_DWithin

. Is there something wrong with the domain model or the queries?

+2


a source to share


2 answers


The first step is to index the geometry column. Something like that:



  CREATE INDEX geo_location_data_the_geom_idx ON geo_location_data USING GIST (the_geom);
+1


a source


Try using a buffer at your points and the intersection operator.

SELECT ... FROM A, B WHERE Intersects(B.the_geom, ST_Buffer(A,1000))


It should be faster.

0


a source






More articles:


All Articles