3dpostgissql
I have a line table in my PostGIS database and a table with invert levels for the start and end of each line.
My goal is to create create 3D lines in my table, from my invert data. I know I can use st_makeline to recreate the line with a 3D point at the start and end but I was hoping to be able to run a update just on the Z value for the start and end.
Can this be done or is it easier to just recreate the line.
You don't have quite enough information, because there are two solutions. You need to specify whether you are traversing the circle positively or negatively. I will discuss the positive (counterclockwise) case; the negative case is computed in the same way with obvious changes.
For the positive direction, the circle's center is to the left of the arc. Just add 90 degrees to the bearing and travel for the radial distance and stop: that's the center. Now we know the circle's center and radius, we can find any point on it. The bearing from the center to a point on the circle is 90 degrees less than the bearing around the circle (in the positive direction).
Here are some formulas. The start point has coordinates (x1,x2), the radius is r, and the bearing is alpha. (I use the mathematical convention that 0 is due east, 90 is north.) We seek the coordinates (y1,y2) of the endpoint where the new bearing will be beta. Let the origin's coordinates be (o1,o2).
The direction vector for bearing alpha is (by definition)
(cos(alpha), sin(alpha))
Rotating this 90 degrees to the left gives the unit vector
(-sin(alpha), cos(alpha))
Moving along this from (x0,x1) by distance r ends up at
(o1,o2) = (x1,x2) + r * (-sin(alpha), cos(alpha))
That's the circle's center.
The bearing from the center to the end point is beta - 90 degrees. The endpoint is reached by moving a distance r in this direction, whence
(y1,y2) = (o1,o2) + r * (cos(beta-90), sin(beta-90))
That's it.
Here is an animation for the start point (x1,x2) = (1000, 2000), shown in red, and the radius r = 4000. The end point (y1,y2) is shown in red. Their bearings are indicated by arrows tangent to the circles. It begins with start and stop bearings at 0 (due east) and increases the stop bearing until it reaches 360. (During this time, because the start bearing is fixed, the circle centers--shown as little black dots--do not change.) Then it increases the start bearing until it, too, reaches 360. (During this time, because the start bearing is changing, the circle centers are moving accordingly.) Both solutions are shown simultaneously: the positive direction and the negative direction.
You need to use ST_GeomFromText to specify your point geometry, note that the coordinates are separated by a blank space and not a comma.
Try the following (changed one of your point coordinates so that it isn't the same as the other):
INSERT INTO myTable(id, name, geom)
VALUES('1','test', ST_SetSRID(ST_MakeLine(ST_GeomFromText('POINT(528115 181037)'),ST_GeomFromText('POINT(528115 181137)')), 27700));
Best Answer
-You can update a point in a linestring with ST_SetPoint(geometry linestring, integer zerobasedposition, geometry point) and ST_Translate().
-To get the first and last point use st_endpoint() and st_startpoint().
-To get the "index" of the last point use ST_NumPoints(the_geom)-1.
-The first point "index" is 0.
Somethng like that for the first point: