1

I've a multilinestring object and a points object , where the points are located on the line. The user can set a start and end point on the line and my goal is to return a subset of the points that are within start_point and end_point on the line.

I first create a temp table (subline_table) using ST_LineSubString to get the linestring within start_point and end_point and then query my points object with ST_DWithin using this subline_table. If I run this query in QGIS DB Manager the correct table/points are shown, but this layer will not show up as query layer once loaded.

If I store the point selection as a table, than the output is saved as a table and can be added succesfully as a postgis layer in QGIS.

My query is as follow:

-- SELECT SUBLINE WHERE INPUT POINTS ARE SNAPPED TO LINE AND STORE IN TEMP TABLE
CREATE TEMP TABLE subline_table (geom) ON COMMIT DROP AS
SELECT ST_LineSubString(line.sub, 
ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_start WHERE Id = 0)),
ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_end WHERE Id = 1))
)
FROM (SELECT ST_LineMerge((SELECT geom FROM linestring WHERE id = 2)) AS sub) AS line; 

-- SELECT POINTS WITHIN DISTANCE OF SUBLINE
-- CREATE TABLE subpoints_table AS
SELECT
  points.geom,
  points.gid,
  points.id
FROM tbl_all_points points, subline_table line
WHERE ST_DWithin(points.geom, line.geom, 20000)

Questions:

  1. Is it possible to add a querylayer to QGIS if there is a CREATE TABLE command in the query?

  2. If no, any suggestions how to do this query without the need to write the subline to a table?

1 Answer 1

1

I just placed the first bit that i wrote to a seperate table on the place where i refer to the table. Where eventually subline.st_linesubstring was the tricky bit.

-- SELECT SUBLINE WHERE INPUT POINTS ARE SNAPPED TO LINE
-- SELECT SUBPOINTS WITHIN DISTANCE OF SUBLINE
SELECT
  points.geom,
  points.id
FROM 
  tbl_all_points AS points, 
  (SELECT ST_LineSubString(line.sub, 
    ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_start WHERE Id = 0)),
    ST_LineLocatePoint(line.sub, (SELECT geom FROM tbl_start_end AS point_end WHERE Id = 1))
  )
  FROM (SELECT ST_LineMerge((SELECT geom FROM linestring WHERE id = 2)) AS sub) AS line) AS subline
WHERE ST_DWithin(points.geom, subline.st_linesubstring, 20000)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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