3

When using UPDATE to create geometries based on the SCHNUM (school number) of students to their attend school, not all the lines show up in QGIS. However, when the basic query result is written to a new table, all the lines display just fine.

Without UPDATE, this query works just fine (wrapped in CREATE TABLE):

 select
      SCH.schnum
   , a.studentnumber
      , ST_MakeLine(a.geom,
                    sch.geom) as geom_new
    from
      "public"."students" as a
      join dpsdata."Schools_Current" as sch
        on  a.schoolnum = sch.schnum

Here is the result, which is what I expect:

enter image description here

When using the UPDATE function, which sets the GEOM_LINE column in the STUDENTS table, only some of the lines are displayed in QGIS.

Here is the SQL:

update
  public.centerprogram_mi_autism_2018 as students
set
  geom_line = data.geom_new
from
  (


    select
      SCH.schnum
      , a.studentnumber
      , ST_MakeLine(a.geom,
                    sch.geom) as geom_new
    from
      "public"."centerprogram_mi_autism_2018" as a
      join dpsdata."Schools_Current" as sch
        on
          a.schoolnum = sch.schnum
  ) as data
where
  data.schnum = students.schoolnum

And here are the results - when run the first time (dashed black lines):

enter image description here

And the weird part, if I run the same query again, the lines that display are different (dashed black lines):

enter image description here

Troubleshooting steps and results:

  • In QGIS, selecting the features by rectangle seems to select all the features, including the ones that aren't being displayed
  • SRID of all inputs and outputs seem to be fine (4326)
  • School number values all return correctly in the sub-query
  • Coordinates of the input points are actually the same as the coordinates of the nodes of the result lines
  • Results are the same in QGIS 2.18 and QGIS 3.2
  • PostgreSQL 9.5 w/ PostGIS 2.3.7

I've used this plenty of times before but can't figure out what's going on.

Perhaps the UPDATE syntax isn't correct, but again, it seems only the display is incorrect?

  • In regard to your first troubleshooting step: If you count the records in QGIS of your select do they match a count made in Postgres with a tool like pgadmin for the same select? Geometries not shown in map but having there attributes present in the attribute window usually has something to do with the projection. – Jakob Sep 11 '18 at 7:21
1

The issue is with where data.schnum = students.schoolnum. You are updating students but you join on schoolnum, so all students going to a given school will all have the same geometry (a random one among the possible ones, since the inner query is not ordered).

To fix it, just change the where clause to where data.studentnumber = students.studentnumber

  • aargh of course... why did I miss that!!! Thanks for the response!!! – DPSSpatial Sep 11 '18 at 14:48

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.