1

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?) – DPSSpatial Apr 9 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 Apr 10 at 4:32
4

You can add a cast when you create the geometry

CREATE MATERIALIZED VIEW public.mv_test AS
    SELECT 
    adr.adr_id,
    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 Apr 10 at 4:18

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.