[postgis-users] Postgis spatial join display error

[Alexandre Neto]

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> 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 <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
> <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
>
>
> _______________________________________________
> 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/ea51b211/attachment.html>