QGIS 2.18: Import Result Table of a query into QGIS as pointlayer

Question

I have a query which outputs a table with an unique nodeID and x-,y-coordinates.

How can I import this query result table into qgis as an point-layer?

I have tried it with the DB-Manager (see below) and by WFS. But both hasn't worked out for me although I was able to specify a query.

The following shows you the SQL query I am running to get my result query table which I want to import in QGIS as point-layer:

SELECT "from" AS nodeID,
       st_x AS x,
       st_y AS y,
       priority AS type
FROM (SELECT "from",
             st_x(st_pointn(geom,1)),
             st_y(st_pointn(geom,1)) 
      FROM public.graph
      UNION
      SELECT "to",
             st_x(st_pointn(geom,st_npoints(geom))), 
             st_y(st_pointn(geom,st_npoints(geom))) 
      FROM public.graph
      ORDER BY "from") AS nodes
INNER JOIN junctions on nodes."from" = id

I'm not familiar enough with this process to fully answer but I think you need to add a geometry column to your query so you end with spatial data — J.R, Feb 07 '19 at 09:38
but I do have the x-/y-coordinates though. I think this must be enough for importing it as a point-layer to qgis. — applebrown, Feb 07 '19 at 09:50
what you probably rather want is using ST_StartPoint & ST_EndPoint instead of extracting the coordinates from the first and last vertex (from which you, btw., would only need to create a ST_MakePoint). please copy your query as code formatted text into the question body instead of an image! it'seasier to parse for anybody trying to help, and also complete... — geozelot, Feb 07 '19 at 09:52
@ThingumaBob I have added the query to the question body. Thank you for your suggestion. But how can I import the result of the query to qgis as a point-layer? — applebrown, Feb 07 '19 at 09:58
This question could help you : https://gis.stackexchange.com/questions/77302/adding-xy-data-on-qgis — J.R, Feb 07 '19 at 10:10

score 3 · Accepted Answer · answered Feb 07 '19 at 10:17

Try this (I've deducted some data provenance), and I've assigned as CRS the WGS84, EPSG: 4326 (I let you correct this if wrong) :

SELECT nodes.nodeID,
       junctions.priority AS type,
       nodes.geom
FROM (SELECT "from" AS nodeID,
             ST_SetSRID(ST_MakePoint(st_x(st_pointn(geom,1)), st_y(st_pointn(geom,1))), 4326) AS geom
      FROM public.graph
      UNION ALL
      SELECT "to" AS nodeID,
             ST_SetSRID(ST_MakePoint(st_x(st_pointn(geom,st_npoints(geom))), st_y(st_pointn(geom,st_npoints(geom)))), 4326) AS geom
      FROM public.graph) AS nodes
INNER JOIN public.junctions ON nodes.nodeID = junctions.id

QGIS 2.18: Import Result Table of a query into QGIS as pointlayer

1 Answers1