Get the first 100 points ordered by distance in PostGIS

Question

I have a table of clients where I have the location of the client. The table has the latitude and longitude column and the geom field that creates it from latitude and longitude with the following query

ALTER TABLE clients_details_locations ADD COLUMN geom geometry(Point, 4326);

UPDATE clients_details_locations 
   SET geom = ST_SetSRID(ST_MakePoint(longitude , latitude), 4326);

Then create an index like this

CREATE INDEX clients_details_locations_geom_idx  ON clients_details_locations USING GIST (geom);

and then

VACUUM ANALYZE public.clients_details_locations

what I want to get are the first 100 points and from them create a polygon

I have tried the following query

SELECT cdl.id,client_id ,cdl.agent_id , cdl.department_id , cdl.district_id ,cdl.latitude , cdl.longitude , cdl.geom 
FROM clients_details_locations cdl
INNER JOIN "agents_georegions3" ON st_within(st_setsrid(cdl.geom, 4326), agents_georegions3.geom) = True                                  
    and "agents_georegions3"."id" in (SELECT id FROM agents_georegions3  WHERE cabecera  ilike '%mar del plata%')
    and cdl.district_id = 11673
    and cdl.agent_id in (SELECT id FROM agents where type_id = 1) 
    and cdl.department_id in (SELECT id FROM departments WHERE description='BUENOS AIRES')
    ORDER BY cdl.geom 
    LIMIT 100 offset 0

query output

As can be seen in the image, the first 100 points (red points) do not bring it to me well, so that the result can be understood, see that the points inside the red triangle are close and it did not bring it to me among the first 100 points. I don't know how to solve to get the first 100 points ordered by their location or their proximity.

I use LIMIT and OFFSET because I want to get the points by 100 to create a polygon with 100 points each.

Answer 1

ST_distance (https://postgis.net/docs/ST_Distance.html) seems to be the function you need.

ST_Distance(geometry g1, geometry g2);

So change your order by clause to

order by st_distance(geom,client_point)

(I do not quite understand from your question how you get the point you are calculating the distance from, therefore client_point)

One caveat: This may play badly with your index. I do not quite see how to optimize the index for this query, but it may be that postgis does the right ting.

Answer 2

An index optimized (K)NN query:

SELECT
  pts.*
FROM
  pts
ORDER BY
  pts.geom <-> <measure_point>
LIMIT
  100
OFFSET
  <offset>
;

---

As an aside: this should work very well with a CURSOR.

Answer 3

Here are my suggestions: (untested but medium-high confidence on syntax)

• Use WITH statements to pre-query the model abstractions in advance of join
• Create a centroid pseudo-column during the pre-query for use with the ORDER BY clause later
• Simplify the main spatial JOIN by referencing the pre-queried inputs
• Use in-code comments

WITH CDL as
/* main source joined to agents and departments */
(SELECT 
    C.id
   ,C.client_id
   ,C.agent_id
   ,C.department_id
   ,C.district_id
   ,C.latitude
   ,C.longitude
   ,st_setsrid(C.geom, 4326) geom
 FROM 
    clients_details_locations C
 WHERE C.district_id = 11673
 INNER JOIN agents A
    ON C.agent_id = A.id and A.type_id = 1
 INNER JOIN departments D
    ON C.department_id = D.id and D.description = 'BUENOS AIRES'
 ), AG3 as
 (SELECT 
 /* distance-based selection criteria queried down */
     cabecera
    ,geom
    ,st_centroid(geom) centroid
  FROM "agents_georegions3" 
  WHERE 
     cabecera like '%mar del plata%'
 ) 
SELECT 
/* finally main selection */
    cdl.id
   ,cdl.client_id
   ,cdl.agent_id
   ,cdl.department_id
   ,cdl.district_id
   ,cdl.latitude
   ,cdl.longitude
   ,cdl.geom 
FROM cdl
INNER JOIN AG3 
  ON st_within(cdl.geom, AG3.geom) = True                                  
ORDER BY st_distance(cdl.geom, AG3.centroid)
LIMIT 100 offset 0;