[postgis-users] Postgis spatial join display error

[Tsering W. Shawa]

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-bounces at lists.osgeo.org> on behalf of Regina Obe <lr at pcorp.us>
Sent: Tuesday, April 28, 2020 1:03 PM
To: 'PostGIS Users Discussion' <postgis-users at 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-bounces at lists.osgeo.org] On Behalf Of Alexandre Neto
Sent: Tuesday, April 28, 2020 12:59 PM
To: PostGIS Users Discussion <postgis-users at 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 <snorris at hillcrestgeo.ca<mailto:snorris at 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 <lr at pcorp.us<mailto:lr at 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-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<mailto: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<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto: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/b49c9b18/attachment.html>