It is about my implementation from an answer of this question. It is more an postgresql issue than a GIS issue, whereas it is about geometries and spatial queries.
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
The query:
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.
EDIT
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.