1

I am working with multipoint geometries in postgis and would like to extract the geometry point associated with the greatest y value of the multipoint geometry. How may I accomplish such a task?

For example, if my multipoint geometry is defined as follows:

'MULTIPOINT(2 5, 3 1, 4 0)'

Then how may I gather the geometry point associated with the greatest y-value?

The following query will tell me that the greatest y-value is 5:

SELECT ST_ASTEXT(MP), ST_YMAX(MP) FROM 
(SELECT ST_GeomFromText('MULTIPOINT(2 5, 3 0.9, 4 1.2)') as MP) as foo;

However, I would like to create a query that returns 'POINT(2 5)' rather than the YMAX value of 5.

Any thoughts?

3

In short: use ST_Dump, which will break your MULTIPOINT into its constituent parts. Then sort descending by ST_Y(geom) and get the first row.

Here's a query that works on my PostGIS:

SELECT ST_AsText(geom)
FROM ST_Dump(ST_GeomFromText('MULTIPOINT(2 5, 3 1, 4 0)'))
ORDER BY ST_Y(geom) DESC
LIMIT 1
  • and "Order By ST_Y(geom) ASC" to get the last point – kttii Jun 28 '16 at 13:22

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.