[GIS] Extracting specific geometry point from MultiPoint geometry using PostGIS

geometrypostgispostgresql

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?

Best Answer

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

Related Solutions

[GIS] Order by geometry columns

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

[GIS] Saving select query results (year wise) from PostgreSQL/PostGIS to text files

I would go with a simple bash loop. Even the years array could be defined as a result fo SQL query.

years=( 2001 2002 2003 )

for y in "${years[@]}"
do
    echo "Copy (
    Select
       foo.gid As addr_ID,
       bar.geom As streets,
       St_LineInterpolatePoint
           (ST_LineMerge(bar.geom),
              St_LineLocatePoint
                 (St_LineMerge(bar.geom),foo.geom)
       ) As interpolated_point
    FROM foo
    Left JOIN bar ON ST_DWithin(foo.geom, bar.geom, 50)
    WHERE Extract(Year FROM foo.Start_time)=${y}
    ORDER BY
    foo.gid, St_Distance(foo.geom, bar.geom)
 )
To '~path/my_file_${y}.txt' WITH DELIMITER ',';" | psql -h host -d your_db
done

Best Answer

Related Solutions

[GIS] Order by geometry columns

[GIS] Saving select query results (year wise) from PostgreSQL/PostGIS to text files

Related Question