0

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

1 Answer 1

6

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

3
  • So my approach is good for learning how to use subqueries =)! I like your approach, thank you. But can you adapt my code in that way I can use it like a view? I have nearly the same structure in another query.
    – Stefan
    Commented Nov 17, 2013 at 17:19
  • 1
    Not really. That layout forces an execution order on the system which doesn't allow the system to easily add a clause. I'd end up adapting your code to run in one query, so the system can easily plan it out. Think less procedurally and more in sets. Commented Nov 18, 2013 at 5:12
  • A little bit late, but I think I can wrap the query into a function. There I can play with parameters.
    – Stefan
    Commented Jan 9, 2014 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.