What is the information behind this geometry?

Question

I am new to PostGIS. I have a geometry value. And I have used the following query to that geometry.

SELECT (ST_AsText(the_geom)) FROM (SELECT (ST_Dump(ST_Boundary(geom_original))).geom As the_geom FROM sample_table) As geom_text LIMIT 2

I have got the following result:

POINT Z (623566.485 6961791.436 96.825)
POINT Z (623599.006 6961805.281 94.874)

I want the explanation of this result. I have assumed something like that this geometry contains z(elevation) component also. 96.825 and 94.874 is showing the elevation. The first two values are boundary x and y values. But there should be two set of terminal points i.e. start and end and it is giving only one set of points. How to get start and point of this boundary? I have tried st_startpoint and st_endpoint but these are giving null value.

I have also executed st_astext query:

SELECT ST_AsText(geom_original) FROM public.sample_table As geom_text LIMIT 2

The result of the st_astext query is:

MULTILINESTRING ZM ((635205.589 7068587.674 226.674 0,635211.789 7068585.293 226.361 6.641,635222.143 7068578.926 226.15 18.796,635231.575 7068572.259 225.988 30.347,635233.497 7068566.092 226.011 36.806,635228.315 7068558.871 225.969 45.694,635220.565 7068558.979 226.061 53.445,635213.056 7068563.573 226.317 62.248,635208.348 7068574.825 226.304 74.445,635205.589 7068587.674 226.674 87.587))
MULTILINESTRING ZM ((623566.485 6961791.436 96.825 0,623576.041 6961796.754 95.624 10.936,623584.711 6961800.301 94.832 20.304,623599.006 6961805.281 94.874 35.441))

But the problem is that I am unable to correlate the st_astext and the st_astext of st_boundary result. And how to get exact coordinate value from these values?

Answer 1

The two points you have are the start and end points of the second linestring. You are correct that it is in the form x,y,z. You don't have any points for the second line because you have put Limit 2 in the first call (and you got 2 points).

St_StartPoint and ST_EndPoint both expect Linestrings and you gave multilinestrings. These are different datatypes and, when you think of it, giving a single start or end point of a multilinestring doesn't make any sense. So, that's why those calls gave you null results.

Your SQL looks strangely complicated and I suspect you can vastly simplify this along the following lines:

SELECT ST_AsText(ST_Boundary(geom)) As SartAndEnd FROM sample_table LIMIT 2

This will give you two rows, each containing a multipoint object in text form. The multipoint object will be the first and last points and each row will represent a line object. A little fiddling and you could fan those out if needed. Alternatively you could simplify your data to linestrings instead of multi-geometry types and use st_startpoint and st_endpoint (I don't know your use-case obviously so that might not be a useful suggestion). Of course if your lines really are multi-geometry types ST_bounday will give you a point set of all the end points but you'll not know which part of the geometry they come from:

select ST_asText(st_Boundary(St_geomfromtext('MULTILINESTRING((1 1, 3 3, 5 5),(3 3, 5 5, 7 7))')));

will give you:

"MULTIPOINT(1 1,3 3,5 5,7 7)"

Now, that might be acceptable, but if it is not what you want, then you do need to convert multi-geometry to single-geometry.

EDIT:
To make the output a little more useful you will want to be able to cross reference the start and end points with the original lines. Just make sure the lines have a unique id (which is good DB practice anyway) and then add that to your SQL, something like this:

SELECT id, ST_AsText(ST_Boundary(geom)) As SartAndEnd FROM sample_table

Answer 2

Linestring boundary is made of the first and last vertex, that's why you get just two points. At the same time, both ST_StartPoint and ST_EndPoint return empty value when called on Multigeometry.