I want to create a view from the query below, whereby I can select the result by an ID (that defines a linestring).
My problem is, that I had no success with coding the query in that way, that for every linestring only one
newpoint is created that is half the length away from the linestring (see last subquery 'newdistance').
I tried to
SELECT DISTINCT ON (id)... but that failed.
The linestring table:
id smallint NOT NULL, geom geometry(linestring, 31468)
I have the same problem in another query, to "extract" a WHERE clause (here in the first part of the query) from an subquery.
My goal is to have a view
newpoint (from the query below) whereby I can query like this
SELECT * FROM newpoint WHERE id=1
WITH line AS (SELECT geom FROM linestring WHERE id=1), points AS (SELECT ST_StartPoint(geom) AS A, ST_EndPoint(geom) AS B FROM line), azimuth AS (SELECT ST_Azimuth(A,B) AS azimuth FROM points), distance AS (SELECT ST_Distance(A,B) AS length FROM points), newdistance AS (SELECT length + (length * 1/2) AS newlength FROM distance) SELECT ST_SetSRID(ST_Translate(A, sin(azimuth) * newlength, cos(azimuth) * newlength), 31468) AS newpoint FROM newdistance, azimuth, points
Setting the WHERE clause id=1 at the end of the query didn't succeed.
When I use
SELECT DISTINCT ON (geom) ...
in the last part of the query it works ONLY with 2 objects (linestrings). With more than 2 linestrings it doesn't work.
Here is a picture, how does it has to look like.