Loading Polygon From Postgres (w/o PostGIS) directly into QGIS

Question

I have a postgres 9.2 install that doesn't have postgis installed. There's a table which contains field name "Zone" of type polygon that I'd like to visualize using QGIS.

When I try to load the table as a PostGIS layer using the Also list tables with no geometry option, the table show up in the legend. When I open up attribute editor, I can see all of the columns except the polygon. In the table view of the DB Manager, I can see the cooridnate list in the Zone column, but am still unable to load a layer from there that has zone as part of the attribute list.

Is it possible to do what I'm trying to do without dumping to an intermediate file type? Maybe some special string in the Layer Source to let me say column "Zone" is the column containing the geometry to plot?

This is another vendor's product, so schema changes and extension installations on the database side are unlikely.

• Inactivated Account: If the server you're connecting to doesn't have PostGIS installed, I would think the column NAME is geometry, rather than geometry type, which wouldn't be available without PostGIS... can you share what is in that column for a record, maybe a screen capture?
• geozelot: the built in geometric types in postgres are essentially arrays of simple cartesian coordinate tuples defining points as part of paths and polygons. no geometry, no CRS etc. . can you query the column from within the DB Manager?
• Core.B: As @ThingumaBob said, the polygon type is just a list of coordinates like ((0,0),(1,2),(2,4),....), except these are decimal lat/lon coordinates. I can see the listing of coordinates when I look on the Table tab of DB Manager.
• geozelot: if you can unnest the coordinate arrays into lat lon columns you could create your geometries from there. or, how about a pyQGIS script?
• Core.B: Would a pyQGIS script be able to fetch coordinate list from db then plot to a feature/layer?

Answer 1

Haven't tried this, but it should work:

1. Create a local Postgres database on the machine that has QGIS installed. Install PostGIS on this database.

2. Create a view on the local database that references the remote database either using dblink or foreign tables. Select zone::geometry in this view to cast from the PostgreSQL polygon type to the PostGIS geometry type.

3. Load this view in QGIS.

This approach will give you unindexed access to the remote database and could be very slow if the dataset is large. Using a materialized view would mitigate this but would break the "no intermediate files" constraint.

• Core.B: There are definitely some benefits to me setting up my own PostGIS instance, but I'm not sure if I'm ready to do that just yet. The original shapefiles contained between 1000-2000 points/vertices per feature with a total of 80 features. As for the "no intermediate files" constraint, I wouldn't consider a materialized view as cumbersome as having to dump the polygons back to csv/shapefiles to see what was edited in the product.