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> 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 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> wrote: > > Try doing > > SELECT s.*, 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 > <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 > *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 > 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
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
