[GIS] Defining a WHERE clause from a subquery for a query to create a handy view

linestringpointpostgispostgresqlquery

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.

enter image description here

Best Answer

Skip the trig, 

create view mypoints as
select id, 
  st_makepoint(
    st_x(st_endpoint(geom)) + (st_x(st_endpoint(geom))-st_x(st_startpoint(geom)))/2, 
    st_y(st_endpoint(geom)) + (st_y(st_endpoint(geom))-st_y(st_startpoint(geom)))/2
  ) as geom 
from mytable;

then

select geom from mypoints where id = 1;

should work fine, for all values of id

Related Question