# Order by geometry columns

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 ?

• If you are asking how can you sort by the left-most x coordinate of each polygon, I have posted an answer. Otherwise, could you please clarify. Oct 6 '14 at 14:04
• If you order by geometry it's ordered alphabetically. Oct 6 '14 at 19:26
• What does "a point geometry column with "left" direction" mean? Oct 6 '14 at 21:15
• The problem is that a have points of the coastline of an island and i want to sort them by direction (stating of a north point, complete the circle and probably even draw the linestring) Oct 7 '14 at 9:16
• So, you want to do this from the most Northerly point going in a clockwise direction? That isn't what your question says. Can you please edit your question. It is not at all clear what you want. Oct 7 '14 at 11:01

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))`

• ST_Envelope is unnecessary overhead; ST_XMin and related `box3d` functions will directly access the geometry's bounding box from a spatial index (if available) or other means Oct 6 '14 at 21:13