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
end_point on the line.
I first create a temp table (
ST_LineSubString to get the linestring within start_point and end_point and then query my points object with
ST_DWithin using this
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)
Is it possible to add a querylayer to QGIS if there is a
CREATE TABLEcommand in the query?
If no, any suggestions how to do this query without the need to write the subline to a table?