Thanks Regina, Simon and Alexandre for a quick help. I used both the suggestions in the QGIS DB Manager. Simon's SQL worked but gave me an error when I try to add the SQL layer on a map however, Regina's SQL worked without any problem.
I appreciate your help and suggestions. Thanks, -Tsering ________________________________ From: postgis-users <postgis-users-boun...@lists.osgeo.org> on behalf of Regina Obe <l...@pcorp.us> Sent: Tuesday, April 28, 2020 1:03 PM To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Postgis spatial join display error Oh one more thing – don’t put a ; after your query. Sorry that was my mistake. I ran into an issue which I discovered was because I put in a ; at the end QGIS when it tried to add the row_number() just wrapped the query around – so the ; made the sub query invalid. From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Alexandre Neto Sent: Tuesday, April 28, 2020 12:59 PM To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Postgis spatial join display error Hi, Yes, QGIS dislike columns with the same name, so you need to use alias to change their names. 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. Alexandre Neto A terça, 28/04/2020, 17:51, Simon Norris <snor...@hillcrestgeo.ca<mailto:snor...@hillcrestgeo.ca>> escreveu: Also, the DB manager insists on a unique key per feature. If there is more than one station per neighbourhood you may also have to add one - I generally wrap the query something like this: SELECT row_number() over() as id, * FROM ( SELECT s.*, n.name<http://n.name> AS neighborhood FROM nyc_neighborhoods AS n JOIN nyc_subway_stations AS s ON ST_Contains(n.geom, s.geom) ) as q; On Apr 28, 2020, at 9:45 AM, Regina Obe <l...@pcorp.us<mailto:l...@pcorp.us>> wrote: Try doing SELECT s.*, n.name<http://n.name> AS neighborhood FROM nyc_neighborhoods AS n JOIN nyc_subway_stations AS s ON ST_Contains(n.geom, s.geom); When you do *, it picks up all columns from both tables and I don’t think QGIS likes duplicated columns From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Tsering W. Shawa Sent: Tuesday, April 28, 2020 12:32 PM To: postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> Subject: [postgis-users] Postgis spatial join display error 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 [SELECT * FROM nyc_neighborhoods AS n JOIN nyc_subway_stations AS s ON ST_Contains(n.geom, s.geom); ] 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 [SELECT * FROM nyc_neighborhoods WHERE name = 'Soho';]. 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. I was wondering what I am missing here. Any suggestions or help will be appreciated. FYI. I have installed PostgreSQL 12 with PostGIS 3. Many thanks, -Tsering _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
