Order by geometry columns

Question

I am wondering how it really works, I mean "order by" statement when it comes to geometry/geography columns in postgis/postgresql. Besides that really need a way gather rows from a table with a point geometry column with "left" direction. Suppose we have the coastline from an island on a table where each row represents a point on the island coastline, is it possible to select all the points with a query like that?

select * from island_table order by ?

Answer 1

I'm not sure a natural ordering really exists for a geometry, given that it is two-dimensional and can start from any point in its outer linear ring. However, to answer the second part of your question, the following will order by the left-most x coordinate of every geometry.

Select * from island_table order by ST_XMin(ST_Envelope(geom));

EDIT: As pointed out by MikeT, the call to ST_Envelope is superfluous, as the bounding box is available anyway to any indexed geometry, so it is sufficient just to write:

Select * from island_table order by ST_XMin(geom);

ST_Envelope gives you the bounding box, from where you can access the minimum and maximum values of x and y, using ST_XMin, ST_YMin, ST_XMax, etc.

I just did a quick test on some test polygons and if you sort by the WKT representation of a geometry,

Select * from island_table order by ST_AsText(geom);

you get something like,

POLYGON((100.00,.....

POLYGON((101.86,.....

POLYGON((102.17,.....

POLYGON((11.82,.....

that is in text search order.

Whereas if you search by the geometry itself, that is,

Select st_astext(geom) from island_table order by geom;

it sorts by actual values of the first x coordinate in the geometry, eg,

POLYGON((11.82,.....

POLYGON((100.00,.....

POLYGON((101.86,.....

POLYGON((102.17,.....

Neither of these are guaranteed to give you the left-most x coordinate, as the start point is not guaranteed to be left mest, so you are better off using order by ST_XMin(ST_Envelope(geom))