how can convert geometry type to geometry(Point)?

Question

I think my question is stupid but I'm blocking. I just work with postgis ST_Line_Locate_Point function (linestring, point). to find the ends of lines. My problem is that my objects geometry type, not point. So how can I convert the geometry to geometry (point) ..?

Answer 1

To get identify a geometry's type use the GeometryType function.

SELECT GeometryType(ST_AsText('LINESTRING(1 2, 3 4, 5 6)'));

That returns 'LINESTRING'. You can put in any geometry and it will give back the type.

Expanding on @Nicklas' and @simplexios's answers, you can use the ST_DumpPoints function to return the points of a line in order. Note that this is a set-returning function which means that it returns a set of rows formed by a geometry (given by .geom) and an array of integers (given by .path).

Edit: You can also extract the GeometryType at the same time.

SELECT
    (line.a).path, ST_AsText((line.a).geom), GeometryType((line.a).geom)

FROM ( SELECT
    ST_DumpPoints(ST_AsText('LINESTRING(1 2, 3 4, 5 6)')) as a
    ) as line;

That one returns:

"{1}";"POINT(1 2)";"POINT"
"{2}";"POINT(3 4)";"POINT"
"{3}";"POINT(5 6)";"POINT"

Answer 2

If you want the end of line why not use ST_EndPoint

If it is all the points you want then use ST_DumpPoints

EDIT:

You use ST_DumpPoints like this:

SELECT (ST_DumpPoints(geom)).geom as geom from my_table;

to det the geometry part of the dump type.