Postgres: Order coordinates points in a direction

Question

I ran into a problem in Postgres when I was working with spatial dates. I have a table with columns: object id and its coordinates (latitude and longitude, data type is geometry). I need to sort this data by coordinates in the direction from northwest to southeast. How can I do this? I suppose that of course I need to use an "order by" and an index. But which and how?

How do these coordinates look like once concatenated? Are they on a straight line? — Timothy Dalton, Dec 05 '21 at 06:04
no, this is not a straight line, but a field of points of one island. moving diagonally (from northwest to southeast) there may be a situation that several points will pretend to be next. so I think I need to add a condition, for example "show more northern points first" — Wazy to, Dec 05 '21 at 08:48
@Wazyto welcome to SO. Wouldn't it suffice to simply order by the `x` axes? — Jim Jones, Dec 05 '21 at 09:39
Then it may be a situation that the most northeastern point will be defined as the most southeast. perhaps I need to try not order by axes, but order by distance from the northwestmost point? the distance to the southeast point should be the largest. am I right? — Wazy to, Dec 05 '21 at 11:37

Jim Jones · Answer 1 · 2021-12-06T08:41:09.620

You could create an envelope from the whole data set using ST_Envelope, and from there you could retrieve the corner you want as a reference - e.g. lower right corner. Using this reference point you can then order the geometries with the distance operator <->.

Demo: db<>fiddle

CREATE TABLE t (gid int, geom geometry(point,4326));
    
INSERT INTO t VALUES 
(6,'SRID=4326;POINT(-4.3675 54.2428)'),
(5,'SRID=4326;POINT(-4.5488 54.3591)'),
(1,'SRID=4326;POINT(-4.5405 54.2974)'),
(7,'SRID=4326;POINT(-4.3098 54.2973)'),
(4,'SRID=4326;POINT(-4.4952 54.2742)'),
(2,'SRID=4326;POINT(-4.4568 54.2790)'),
(3,'SRID=4326;POINT(-4.4156 54.2509)');

The following CTE creates an envelope based on all points in table t (see envelope in the image above), retrieves the value of the lower right corner, and in the outer query it uses this reference point to order the records by distance.

WITH j AS (
  SELECT 
    ST_SetSRID(
      ST_MakePoint(
        ST_XMax(ST_Envelope(ST_Union(geom))),
        ST_YMin(ST_Envelope(ST_Union(geom)))),
      4326) AS reference_point
  FROM t
)
SELECT 
  t.gid,
  ST_AsText(t.geom) AS geom, 
  ST_AsText(j.reference_point) AS reference_point,
  t.geom <-> j.reference_point AS distance_to_ref_point
FROM j,t
ORDER BY t.geom <-> j.reference_point;

 gid |          geom          |    reference_point     | distance_to_ref_point 
-----+------------------------+------------------------+-----------------------
   7 | POINT(-4.3098 54.2973) | POINT(-4.3098 54.2428) |   0.05449999999999733
   6 | POINT(-4.3675 54.2428) | POINT(-4.3098 54.2428) |   0.05769999999999964
   3 | POINT(-4.4156 54.2509) | POINT(-4.3098 54.2428) |   0.10610961313660537
   2 | POINT(-4.4568 54.279)  | POINT(-4.3098 54.2428) |   0.15139167744628546
   4 | POINT(-4.4952 54.2742) | POINT(-4.3098 54.2428) |   0.18804020846616745
   1 | POINT(-4.5405 54.2974) | POINT(-4.3098 54.2428) |    0.2370730899954694
   5 | POINT(-4.5488 54.3591) | POINT(-4.3098 54.2428) |    0.2657944506568918

Postgres: Order coordinates points in a direction

1 Answers1