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:
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):
And the weird part, if I run the same query again, the lines that display are different (dashed black lines):
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?