Bug report #21993

ST_Buffer does not work with postgis layer

Added by Sebastian Brocks over 5 years ago. Updated over 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:3.6.2 Regression?:No
Operating System:windows 7 Easy fix?:No
Pull Request or Patch supplied:No Resolution:invalid
Crashes QGIS or corrupts data:No Copied to github as #:29808

Description

I have a postgis table with a geometry column containg a linestring of SRID 3035. I can't get a buffer created using ST_Buffer to work.
When I use this SQL statement:

SELECT ST_Buffer(geom, 20)), id from erhaltung.abschnitt and set the unique-values column and geography column accordingly, I receive the warrning message

Feature type or srid for st_buffer of (SELECT ST_Buffer(geometrie, 20), id from erhaltung.abschnitt
) AS "subQuery_0" could not be determined or was not requested.

and the attribute table for the layer is empty.

When I use

SELECT ST_AsEWKT(ST_Buffer(geometrie, 20)), id from erhaltung.abschnitt

the preview of the data in the update sql layer dialog looks correct for the geometry:
st_asewkt id
SRID=3035;POLYGON) 26

and the features show up in the attribute table, but the features show up in a completely wrong place (some place in Saudi Arabia instead of Germany).

abschnitt.sql (31.6 KB) Sebastian Brocks, 2019-05-03 04:16 PM

History

#1 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Open to Feedback

can you attach a dump of your table?

#2 Updated by Sebastian Brocks over 5 years ago

Here's a dump of the table.

#3 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Open to Closed
  • Resolution set to invalid

Sebastian Brocks wrote:

Here's a dump of the table.

the correct query for your data is

SELECT ST_Buffer(geometrie, 20), id from erhaltung.abschnitt

#4 Updated by Sebastian Brocks over 5 years ago

  • Status changed from Closed to Reopened

I tried that, and it didn't work. As I said earlier, with that query, the attribute table is empty and I get this warning in the General Log:
Feature type or srid for st_buffer of (SELECT ST_Buffer(geometrie, 20), id from erhaltung.abschnitt ) AS "subQuery_0" could not be determined or was not requested.

#5 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Reopened to Feedback

Sebastian Brocks wrote:

I tried that, and it didn't work. As I said earlier, with that query, the attribute table is empty and I get this warning in the General Log:
Feature type or srid for st_buffer of (SELECT ST_Buffer(geometrie, 20), id from erhaltung.abschnitt ) AS "subQuery_0" could not be determined or was not requested.

it works perfectly fine here, with your data.

#6 Updated by Sebastian Brocks over 5 years ago

Ok, it seems the problem was that because I buffered a linear layer, I had to manually change the type of the layer from Line and LineString to Polygon. After doing that by editing the project file, I got it to work. Is there any way to do that automatically?

#7 Updated by Giovanni Manghi over 5 years ago

Sebastian Brocks wrote:

Ok, it seems the problem was that because I buffered a linear layer, I had to manually change the type of the layer from Line and LineString to Polygon. After doing that by editing the project file, I got it to work. Is there any way to do that automatically?

I don't follow, I didn't had to do that to load the results of the above query in the project, I just run the query in DB Manager and loaded the results as query layer.

#8 Updated by Sebastian Brocks over 5 years ago

Ah, I hadn't found the option to run a query directly without first adding a layer. Thanks for the info!

#9 Updated by Giovanni Manghi over 5 years ago

Sebastian Brocks wrote:

Ah, I hadn't found the option to run a query directly without first adding a layer. Thanks for the info!

so can we close this?

#10 Updated by Sebastian Brocks over 5 years ago

Yes.

#11 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Feedback to Closed

Also available in: Atom PDF