1

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.

1

There was a comment to my question that provided an answer to my problem, but it disappeared meanwhile - however, here is the solution, proposed by user MrXsquared:

I simply have to add a unique ID field - I included the fid from my input-layer bufferrail:

SELECT fid, ST_ApproximateMedialAxis(geom) from public.bufferrail;

With this result, I could add it as a layer in QGIS (solution 2 from above). Still, exporting that layer created another issue with unique ID, but I simply had to select all objects and insert it in an already existing line-layer.

For background-information, more details on QGIS returns “Invalid Layer” message when trying to add PostGIS view?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.