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 ?
Best Answer
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.
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:
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,
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,
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))