<div dir="auto">Hi,<div dir="auto"><br></div><div dir="auto">Yes, QGIS dislike columns with the same name, so you need to use alias to change their names.</div><div dir="auto"><br></div><div dir="auto"><div dir="auto">Regarding the unique id, if you are using the dbmanager sql editor, you no longer need a unique id column, qgis will create one. Nevertheless, if you use the query in a view, not only you should have a unique id column, it's suggested that it's the first column, so that QGIS pick it up automatically.</div><div dir="auto"><br></div><div dir="auto">Alexandre Neto</div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">A terça, 28/04/2020, 17:51, Simon Norris <<a href="mailto:snorris@hillcrestgeo.ca">snorris@hillcrestgeo.ca</a>> escreveu:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word;line-break:after-white-space">Also, the DB manager insists on a unique key per feature.<div>If there is more than one station per neighbourhood you may also have to add one - I generally wrap the query something like this:</div><div><br></div><div>SELECT row_number() over() as id, * </div><div>FROM (</div><div> SELECT </div><div>   s.*, <a href="http://n.name" target="_blank" rel="noreferrer">n.name</a> AS neighborhood </div><div>  FROM nyc_neighborhoods AS n </div><div> JOIN nyc_subway_stations AS s </div><div> ON ST_Contains(n.geom, s.geom)</div><div>) as q;<br><div><br><blockquote type="cite"><div>On Apr 28, 2020, at 9:45 AM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank" rel="noreferrer">lr@pcorp.us</a>> wrote:</div><br><div><div style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none"><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Try doing<u></u><u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-family:Calibri,sans-serif;color:rgb(200,38,19)">SELECT s.*, <a href="http://n.name" target="_blank" rel="noreferrer">n.name</a> AS neighborhood FROM nyc_neighborhoods AS n JOIN nyc_subway_stations AS s ON ST_Contains(n.geom, s.geom);<u></u><u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-family:Calibri,sans-serif;color:rgb(200,38,19)"><u></u> <u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-family:Calibri,sans-serif">When you do *, it picks up all columns from both tables and I don’t think QGIS likes duplicated columns<u></u><u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-family:Calibri,sans-serif"><u></u> <u></u></span></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></div><div style="border-style:none none none solid;border-left-width:1.5pt;border-left-color:blue;padding:0in 0in 0in 4pt"><div><div style="border-style:solid none none;border-top-width:1pt;border-top-color:rgb(225,225,225);padding:3pt 0in 0in"><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"><span> </span>postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank" rel="noreferrer">mailto:postgis-users-bounces@lists.osgeo.org</a>]<span> </span><b>On Behalf Of<span> </span></b>Tsering W. Shawa<br><b>Sent:</b><span> </span>Tuesday, April 28, 2020 12:32 PM<br><b>To:</b><span> </span><a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a><br><b>Subject:</b><span> </span>[postgis-users] Postgis spatial join display error<u></u><u></u></span></div></div></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><u></u> <u></u></div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif">I am new to PostGIS and trying to learn how everything works. I am using Introduction to PostGIS site data to test different GIS function including spatial join. The spatial join SQL statement seems to work [</span><span style="font-family:Calibri,sans-serif;color:rgb(200,38,19)">SELECT * FROM nyc_neighborhoods AS n JOIN nyc_subway_stations AS s ON ST_Contains(n.geom, s.geom);</span><span style="font-family:Calibri,sans-serif"><span> </span>] but when I try to add the result to the QGIS I get an error message saying "there was an error creating the SQL layer". When I tried the simpler the SQL function by selecting only row with a particular name [</span><span style="font-family:Calibri,sans-serif;color:rgb(200,38,19)">SELECT * FROM nyc_neighborhoods WHERE name = 'Soho';</span><span style="font-family:Calibri,sans-serif">]. The SQL statement worked as well as I was able to add the SQL layer on a map. I am using QGIS's DB Manager to write SQL statements.<u></u><u></u></span></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif"><u></u> <u></u></span></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif">I was wondering what I am missing here. <u></u><u></u></span></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif"><u></u> <u></u></span></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif">Any suggestions or help will be appreciated. FYI. I have installed PostgreSQL 12 with PostGIS 3.<u></u><u></u></span></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif"><u></u> <u></u></span></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif">Many thanks,<u></u><u></u></span></div></div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif;background-color:white"><span style="font-family:Calibri,sans-serif">-Tsering<u></u><u></u></span></div></div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-family:Calibri,sans-serif"><u></u> <u></u></span></div></div><div><div><div style="margin:0in 0in 0.0001pt;font-size:12pt;font-family:"Times New Roman",serif"><span style="font-family:Calibri,sans-serif"><u></u> <u></u></span></div></div></div></div></div><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none;float:none;display:inline!important">_______________________________________________</span><br style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none"><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none;float:none;display:inline!important">postgis-users mailing list</span><br style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none"><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none;float:none;display:inline!important"><a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a></span><br style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none"><span style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:none;float:none;display:inline!important"><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank" rel="noreferrer">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></span></div></blockquote></div><br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>