[postgis-users] Postgis spatial join display error

[Simon Norris]

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 <lr at 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-bounces at lists.osgeo.org] On Behalf Of Tsering W. Shawa
> Sent: Tuesday, April 28, 2020 12:32 PM
> To: postgis-users at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200428/b486d6b6/attachment.html>