Populate_Geometry_Columns does not work with materialized view Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Create a View with a new geometry field in PostGISSome items have coordinates switched when loading PostGIS layers in QGISProblem with ST_UnionUsing Postgis spatial index in st_distance operation based upon explicit SRID columnPostGIS Out of db VRT containing JPEG2000 GDAL 2.1 OpenJPEGDuplicate PostGIS view entries in QGISPostGIS ST_Intersects vs ArcGIS Select by LocationStrange slow behavior of a very simple query utilizing single PointField fieldUpdating the Entry in geometry_columns for Spatial Table Created from a QueryPostgis Update using calculation from various fields
List of Python versions
Bete Noir -- no dairy
What is a non-alternating simple group with big order, but relatively few conjugacy classes?
Short Story with Cinderella as a Voo-doo Witch
Why are Kinder Surprise Eggs illegal in the USA?
How to tell that you are a giant?
Why did the rest of the Eastern Bloc not invade Yugoslavia?
porting install scripts : can rpm replace apt?
Why didn't this character "real die" when they blew their stack out in Altered Carbon?
Why are there no cargo aircraft with "flying wing" design?
What does an IRS interview request entail when called in to verify expenses for a sole proprietor small business?
What is Arya's weapon design?
How do I keep my slimes from escaping their pens?
How come Sam didn't become Lord of Horn Hill?
What exactly is a "Meth" in Altered Carbon?
How to bypass password on Windows XP account?
Coloring maths inside a tcolorbox
ListPlot join points by nearest neighbor rather than order
How to find all the available tools in mac terminal?
List *all* the tuples!
Seeking colloquialism for “just because”
Why do we bend a book to keep it straight?
prime numbers and expressing non-prime numbers
What is the meaning of the new sigil in Game of Thrones Season 8 intro?
Populate_Geometry_Columns does not work with materialized view
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?Create a View with a new geometry field in PostGISSome items have coordinates switched when loading PostGIS layers in QGISProblem with ST_UnionUsing Postgis spatial index in st_distance operation based upon explicit SRID columnPostGIS Out of db VRT containing JPEG2000 GDAL 2.1 OpenJPEGDuplicate PostGIS view entries in QGISPostGIS ST_Intersects vs ArcGIS Select by LocationStrange slow behavior of a very simple query utilizing single PointField fieldUpdating the Entry in geometry_columns for Spatial Table Created from a QueryPostgis Update using calculation from various fields
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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
postgis
asked Apr 9 at 19:03
JaakLJaakL
1,540916
add a comment |
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
postgis
asked Apr 9 at 19:03
JaakLJaakL
1,540916
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
add a comment |
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
postgis
asked Apr 9 at 19:03
JaakLJaakL
1,540916
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
postgis
postgis
asked Apr 9 at 19:03
JaakLJaakL
1,540916
asked Apr 9 at 19:03
JaakLJaakL
1,540916
asked Apr 9 at 19:03
JaakLJaakL
1,540916
asked Apr 9 at 19:03
JaakLJaakL
1,540916
asked Apr 9 at 19:03
JaakLJaakL
1,540916
1,540916
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
add a comment |
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
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
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
@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
add a comment |
1 Answer
1
active
oldest
votes
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
answered Apr 9 at 22:52
JGHJGH
13.6k21239
Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
– JaakL
Apr 10 at 4:18
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318281%2fpopulate-geometry-columns-does-not-work-with-materialized-view%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Apr 9 at 22:52
JGHJGH
13.6k21239
Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
– JaakL
Apr 10 at 4:18
add a comment |
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
answered Apr 9 at 22:52
JGHJGH
13.6k21239
Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
– JaakL
Apr 10 at 4:18
add a comment |
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
answered Apr 9 at 22:52
JGHJGH
13.6k21239
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
answered Apr 9 at 22:52
JGHJGH
13.6k21239
answered Apr 9 at 22:52
JGHJGH
13.6k21239
answered Apr 9 at 22:52
JGHJGH
13.6k21239
answered Apr 9 at 22:52
JGHJGH
13.6k21239
13.6k21239
Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
– JaakL
Apr 10 at 4:18
add a comment |
Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
– JaakL
Apr 10 at 4:18
Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
– JaakL
Apr 10 at 4:18
Thanks, this worked for me also in quick test. Will need some hours to rerun full table.
– JaakL
Apr 10 at 4:18
add a comment |
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318281%2fpopulate-geometry-columns-does-not-work-with-materialized-view%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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