5

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. – John Powell Oct 6 '14 at 14:04
  • If you order by geometry it's ordered alphabetically. – Jakub Kania Oct 6 '14 at 19:26
  • What does "a point geometry column with "left" direction" mean? – Mike T 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) – Bill87 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. – John Powell Oct 7 '14 at 11:01
5

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 – Mike T Oct 6 '14 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.