Using PostGIS: syntax-basics for function, I was able to solve a long-standing problem by creating a geometry, using a SQL-query with PostGIS. However, I want to use the created geometry in QGIS and have problems exporting it.
QGIS 3.8.2. Zanzibar, Windows 10, PostgreSQL 11.5.1., PostGIS 2.5.2
The query I used was as follows - it creates a Medial Axis of a Polygon (Shapefile bufferrail imported to PostGIS with shp2pgsql-gui.exe):
Select ST_ApproximateMedialAxis(geom) from public.bufferrail;
The result is shown in the Geometry-viewer-tab in pgAdmin4 and it looks like what I wanted to accomplish. However, I failed to export this result into QGIS. What I already tried:
1.) Using database-manager in QGIS, when I perform the query there and try to load the result as a new layer (load-Button), I get an error-message - see the protocol-entry (in german, unfortunately, it basically says Invalid PostgreSQL-Layer and WARNING: incorrect query):
Ungültiger PostgreSQL-Layer WARNING Fehlerhafte Abfrage: SELECT "" FROM (SELECT row_number() over () AS _uid_,* FROM (SELECT ST_ApproximateMedialAxis(geom) from public.bufferrail ) AS _subq_1_ ) AS "subQuery_0" LIMIT 0 ergab 7 [FEHLER: Bezeichner in Anführungszeichen hat Länge null bei »""« LINE 1: SELECT "" FROM (SELECT row_number() over () AS _uid_,* FROM ... ^ ] 2019-08-25T23:47:57 WARNING Ungültiger PostgreSQL-Layer
2.) Again in QGIS database-manager, I created a view from the query result. When I try to add this view to map (always from QGIS DB manager - right-cick on the view in pubic), QGIS always crashes.
3.) Still in QGIS database-manager, I try to export to file (Geopackage) - however, when I add the saved file as a layer to QGIS, an empty layer with no geometries is added.
4.) In pgAdmin 4: I run the SQL-query mentioned above and than click Download as CSV. I get a text-file with cryptic content (when opened in an editor). So no idea how to use it.
5.) I tried to google export shp from PostGIS and tried to use pgsql2shp - however, all of the many expressions to include pgsql2shp in my query failed - again, no idea how correct syntax works here.
6.) By the way: is there no way to use a simple GUI-method for export - similar to import shp to PostGIS with shp2pgsql-gui.exe? I tried the Export-tab in the GUI there, but export works only with tables. So probably a solution would be to convert the result of my query to a table - but unfortunately, again all my efforts failed - wrong syntax, again!
To come to my question: I don't care much how I can export the result form a relativele simple query in PostGIS to opening it in QGIS. Probably solution 5 is the most promising: so if someone could help with correct syntax, connection my original query and the export-function with pgsql2shp. But any other solution is good, too.