Postgres: Order coordinates points in a direction

user_17589878 0
Viewed 386

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?

postgis postgresql sql
1 Answers

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)');

enter image description here

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

Further reading:

user_22753880
Related