1

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.

enter image description here

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
Improve this question
5
  • 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 7, 2019 at 9: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 7, 2019 at 9: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 7, 2019 at 9: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 7, 2019 at 9:58
  • This question could help you : gis.stackexchange.com/questions/77302/adding-xy-data-on-qgis
    – J.R
    Feb 7, 2019 at 10:10

1 Answer 1

Reset to default
3

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
Improve this answer
0

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.