<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">Also, the DB manager insists on a unique key per feature.<div class="">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 class=""><br class=""></div><div class="">SELECT row_number() over() as id, * </div><div class="">FROM (</div><div class=""> SELECT </div><div class=""> s.*, n.name AS neighborhood </div><div class=""> FROM nyc_neighborhoods AS n </div><div class=""> JOIN nyc_subway_stations AS s </div><div class=""> ON ST_Contains(n.geom, s.geom)</div><div class="">) as q;<br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Apr 28, 2020, at 9:45 AM, Regina Obe <<a href="mailto:lr@pcorp.us" class="">lr@pcorp.us</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="WordSection1" style="page: WordSection1; caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none;"><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class="">Try doing<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""><o:p class=""> </o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-family: Calibri, sans-serif; color: rgb(200, 38, 19);" class="">SELECT s.*, n.name AS neighborhood FROM nyc_neighborhoods AS n JOIN nyc_subway_stations AS s ON ST_Contains(n.geom, s.geom);<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-family: Calibri, sans-serif; color: rgb(200, 38, 19);" class=""><o:p class=""> </o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-family: Calibri, sans-serif;" class="">When you do *, it picks up all columns from both tables and I don’t think QGIS likes duplicated columns<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125);" class=""><o:p class=""> </o:p></span></div><div style="border-style: none none none solid; border-left-width: 1.5pt; border-left-color: blue; padding: 0in 0in 0in 4pt;" class=""><div class=""><div style="border-style: solid none none; border-top-width: 1pt; border-top-color: rgb(225, 225, 225); padding: 3pt 0in 0in;" class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><b class=""><span style="font-size: 11pt; font-family: Calibri, sans-serif;" class="">From:</span></b><span style="font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span class="Apple-converted-space"> </span>postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org" class="">mailto:postgis-users-bounces@lists.osgeo.org</a>]<span class="Apple-converted-space"> </span><b class="">On Behalf Of<span class="Apple-converted-space"> </span></b>Tsering W. Shawa<br class=""><b class="">Sent:</b><span class="Apple-converted-space"> </span>Tuesday, April 28, 2020 12:32 PM<br class=""><b class="">To:</b><span class="Apple-converted-space"> </span><a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a><br class=""><b class="">Subject:</b><span class="Apple-converted-space"> </span>[postgis-users] Postgis spatial join display error<o:p class=""></o:p></span></div></div></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><o:p class=""> </o:p></div><div class=""><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class="">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);" class="">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;" class=""><span class="Apple-converted-space"> </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);" class="">SELECT * FROM nyc_neighborhoods WHERE name = 'Soho';</span><span style="font-family: Calibri, sans-serif;" class="">]. 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.<o:p class=""></o:p></span></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></span></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class="">I was wondering what I am missing here. <o:p class=""></o:p></span></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></span></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class="">Any suggestions or help will be appreciated. FYI. I have installed PostgreSQL 12 with PostGIS 3.<o:p class=""></o:p></span></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></span></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class="">Many thanks,<o:p class=""></o:p></span></div></div><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif; background-color: white;" class=""><span style="font-family: Calibri, sans-serif;" class="">-Tsering<o:p class=""></o:p></span></div></div><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></span></div></div><div class=""><div class=""><div style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;" class=""><span style="font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></span></div></div></div></div></div><span style="caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">_______________________________________________</span><br style="caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><span style="caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">postgis-users mailing list</span><br style="caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><span style="caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class=""><a href="mailto:postgis-users@lists.osgeo.org" class="">postgis-users@lists.osgeo.org</a></span><br style="caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><span style="caret-color: rgb(0, 0, 0); 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; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class=""><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" class="">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></span></div></blockquote></div><br class=""></div></body></html>