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] 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
