<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi list -</p>
<p>I'm working with making spatial views "visible" for QGIS, i.e. so
they look like a read-only table from the data source manager.</p>
<p>However, I have one problem: QGIS doesn't recognize the crs for
the view. I've tracked the problem down to that <b>OGR</b> dosn't
recognize the crs for the view. Hence this mail...</p>
<p>What I've done: <br>
</p>
<p><b><font face="monospace">- creating the view: A union of parcels
with the same owner (CVR-number)<br>
CREATE VIEW marker_2019_cvr AS<br>
SELECT "CVR", SUM(COALESCE(st_area(geom),0.0)) as area_m2,
count(*) as antal, st_multi(st_union(geom)) as geom from
marker_2019 group by 1;<br>
<br>
-- insert or update gpkg_contents<br>
INSERT OR REPLACE INTO
gpkg_contents(table_name,data_type,identifier,min_x,min_y,max_x,max_y,srs_id)<br>
SELECT <br>
'marker_2019_cvr' AS table_name, <br>
'features' AS data_type,<br>
'marker_2019_cvr' AS identifier, <br>
MIN(ST_MinX(geom)) AS min_x, <br>
MIN(ST_MinY(geom)) AS min_y, <br>
MAX(ST_MaxX(geom)) AS max_x, <br>
MAX(ST_MaxY(geom)) AS max_y,<br>
25832 AS srs_id -- same srs as the base layer for the view<br>
FROM marker_2019_cvr;<br>
<br>
-- insert or update gpkg_geometry_columns<br>
INSERT OR REPLACE INTO gpkg_geometry_columns ( table_name,
column_name , geometry_type_name, srs_id, z, m)<br>
VALUES ('marker_2019_cvr', 'geom', 'MULTIPOLYGON', 25832, 0,
0);<br>
<br>
-- insert or update gpkg_ogr_columns (necessary ?)<br>
INSERT OR REPLACE INTO
gpkg_ogr_contents(table_name,feature_count)<br>
SELECT <br>
'marker_2019_cvr' AS table_name,<br>
count(*) AS feature_count <br>
FROM marker_2019_cvr;<br>
</font></b></p>
<p>AFAIK, this is the necessary steps to make a spatial view visible
in a GeoPackage. And it works; sort of...</p>
<p>The QGIS datasource manager shows the view as a spatial table. I
can add it to the QGIS map, but... it can't find the CRS for the
layer and defaults to the projects CRS.</p>
<p>Digging a litle bit deeper: Using ogrinfo like this: <br>
</p>
<p><b><font face="monospace">ogrinfo -ro -so
data_marker_vandloeb_soe.gpkg marker_2019_cvr</font></b></p>
<p>shows the layers SRS WKT as (unknown).</p>
<p>Using the same command on the base layer <b><font
face="monospace">marker_2019 </font></b>gives the correct
WKT.</p>
<p>25832 is the correct EPSG number for the CRS. And the definition
exists in the <b><font face="monospace">gpkg_spatial_ref_sys</font></b>
table.<br>
</p>
<p>What am I doing wrong ? Or is it just "out of scope" for OGR ?? <br>
</p>
<pre class="moz-signature" cols="72">--
Med venlig hilsen / Best regards
Bo Victor Thomsen</pre>
</body>
</html>