I have created materialized view with Points as ... ST_SetSRID(ST_MakePoint( longitude,latitude), 4326) as geom... column data. The table kind of works, I can create spatial index which works etc, but the geometry_columns view shows srid=0 and type=GEOMETRY. This is major issue for QGIS which seems to try to full scan the table to find out true geometry type. And that takes too much time as my view is quite large (over 500M rows).

Next I tried to fix the metadata with select Populate_Geometry_Columns('public.mymaterializedview'::regclass) , but this returns immediately '0' instead of '1' what I normally get with real tables. And the geometry_columns does not get changed.

I tried also setting column type with ALTER TABLE public.mymaterializedview ALTER COLUMN geom type geometry(Point, 4326) USING ST_SetSRID(geom, 4326); but this gives [42809] ERROR: "mymaterializedview" is not a table, composite type, or foreign table which is not very helpful for me.

The DB is GCP managed Cloud SQL DB, i.e. Postgres 9.6 and PostGIS full version is currently POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 4392" PROJ="Rel. 6.0.0, March 1st, 2019" GDAL="GDAL 2.4.0dev, released 2018/99/99" LIBXML="2.9.1" LIBJSON="0.12.1" RASTER

  • Do the underlying tables have the correct attribution in geometry_columns? Our materialized views work great - but we haven't done anything more than create the views. In geometry_columns, they also show up with SRID of 0, but that has never bothered QGIS at all... (weird?) Commented Apr 9, 2019 at 22:18
  • @DPSSpatial It seems that QGIS just does full scan of the table to find out real srids and geometry types of all objects in this case. Tables are not usually hundreds of millions of rows, with these the scan takes hours.
    – JaakL
    Commented Apr 10, 2019 at 4:32

1 Answer 1


You can add a cast when you create the geometry

    ST_SetSRID(ST_MakePoint( adr.adr_longitude,adr.adr_latitude), 4326)::geometry(point,4326) as geom
 FROM  public.adresses adr; 

The geometry type and SRID will be properly reported in geometry_column

select * from geometry_columns where f_table_name = 'mv_test';
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type
 testdb          | public         | mv_test      | geom              |               2 | 4326 | POINT
  • Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
    – JaakL
    Commented Apr 10, 2019 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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