<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>Hi all -</p>
<p>(for the impatient, read the last part of this mail)<br>
</p>
<p>I've been digging a little bit further into my own setup -
essentially doing the same operations as Richard</p>
<p>I examined the 4 layers i GeoServer<br>
</p>
<ul>
<li>groenreg:bykerne: <b>SqlServer</b> based layer <b>with</b>
rows. <br>
Geometry column defined as "geometry", but defined in
"geometry_columns" as "MULTIPOLYGON", dimensions = 2 and srid =
25832 <br>
<br>
</li>
<li>groenreg:bykerne_pg: <b>Postgres</b> based layer<b> with</b>
rows <br>
Geometry column defined as "geometry (MULTIPOLYON,25832) and
defined in "geometry_columns" as "MULTIPOLYGON", dimensions = 2
and srid = 25832 <br>
<br>
</li>
<li>groenreg:bykerne_empty:<b> Sql-server</b> based layer <b>without</b>
rows <br>
Geometry column defined as "geometry", but defined in
"geometry_columns" as "MULTIPOLYGON", dimensions = 2 and srid =
25832 <br>
<br>
</li>
<li>groenreg:bykerne_pg_empty: <b>Postgres</b> based layer <b>without</b>
rows .<br>
Geometry column defined as "geometry (MULTIPOLYON,25832) and
defined in "geometry_columns" as "MULTIPOLYGON", dimensions = 2
and srid = 25832 <br>
</li>
</ul>
The datastore in Geoserver for the layers in SQLServer is set up so
the location of the geometry_columns is known to GeoServer<br>
<br>
The documentation for GeoServer clearly states, that if there is a
geometry_columns meta table present and is known to GeoServer,
GeoServer will use information from this table to ascertain the
geometry type for the layers:
<a class="moz-txt-link-freetext" href="https://docs.geoserver.org/stable/en/user/data/database/sqlserver.html#using-the-geometry-metadata-table">https://docs.geoserver.org/stable/en/user/data/database/sqlserver.html#using-the-geometry-metadata-table</a><br>
<br>
When I make a WFS describefeature - request for each of the 4 layers
I get the following result: <br>
<ul>
<li>groenreg:bykerne: ...<xsd:element maxOccurs="1"
minOccurs="0" name="ogr_geometry" nillable="true" type="<b>gml:GeometryPropertyType</b>"/>...<br>
<br>
</li>
<li>groenreg:bykerne_pg: ... <xsd:element maxOccurs="1"
minOccurs="0" name="wkb_geometry" nillable="true" type="<b>gml:MultiSurfacePropertyType</b>"/>...<br>
<br>
</li>
<li>groenreg:bykerne_empty: ...<xsd:element maxOccurs="1"
minOccurs="0" name="ogr_geometry" nillable="true" type="<b>gml:GeometryPropertyType</b>"/>...<br>
<br>
</li>
<li>groenreg:bykerne_pg_empty: ... <xsd:element maxOccurs="1"
minOccurs="0" name="wkb_geometry" nillable="true" type="<b>gml:MultiSurfacePropertyType</b>"/>...<br>
<br>
</li>
</ul>
I.e. The layers based on ms-sqlserver (with or without rows) always
is seen as type="<b>gml:GeometryPropertyType</b>" (general geometry,
no subtype) and layers based on Postgres is seen as type="<b>gml:MultiSurfacePropertyType
(</b>i.e. the correct subtype for the specific layer)<br>
<br>
My conclusion: <br>
GeoServer is <b>not</b> using the information stored in the
geometry_columns meta table to define the real geometry subtype for
a MS-SQLServer based layer. Hence QGIS is forced to detect the
geometry-type by fetching one row and determine the type from this
row. If there is no rows, QGIS is unable the determine the type.<br>
On the other hand Postgres based layers from GeoServer shows the
right geometry subtype in the WFS describefeature request. These
layers in QGIS gets the right type with or without rows in the
source database table.<br>
<br>
Why GeoServer isn't using the information in the geometry columns
meta table is unknown to me. It can be a faulty setup by me or a bug
in the GeoServer code.<br>
<br>
<b>NB!!! STOP PRESS -</b><b><br>
</b><br>
I've just investigated the structure of geometry_columns in my
sqlserver database and compared it to the geometry_columns structure
that GeoServer prescribes:<br>
There <b>is</b> a bloody difference: The <b>geometry_type</b>
column in my sqlserver geometry_columns table has the "wrong" name.
It should be named simply <b>type</b> <br>
<br>
A simple renaming of column <b>geometry_type</b> to <b>type </b>in
table geometry_columns solves the issue with the zero-row sqlserver
tables as layers <br>
<br>
(Just turned away from computer and stomping head into wall for
about 3 minutes...<br>
<br>
<br>
.... Back again with a slight headache...) <br>
<br>
Retrying the WFS describefeature request on the sql server layers
now gives the equivalent result as with Postgres:<br>
<ul>
<li> ...<xsd:element maxOccurs="1" minOccurs="0"
name="ogr_geometry" nillable="true" type="<b>gml:MultiSurfacePropertyType</b>"/>...<br>
</li>
</ul>
<br>
There seems to be a <span data-dobid="hdw">discrepancy</span> in
how different spatial software interprets how geometry_columns
should be structured<br>
<ul>
<li>GDAL uses "geometry_type" (hence SQLServer uses this too,
because it was created by ogc2ogc command-line tool in
sqlserver) </li>
<li>PostGIS view use "type" </li>
<li>Spatialite uses "geometry_type" </li>
<li>GeoServer uses "type" </li>
</ul>
and so on ....<br>
<br>
How do we ever reach "world peace" or solve the climate crisis if we
even can't agree on the name of friggin' meta table column ....<br>
<br>
Regards <br>
<br>
Bo Victor Thomsen<br>
GIS & Database specialist<br>
<br>
AestasGIS<br>
Denmark<br>
<br>
<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">Den 10-05-2019 kl. 17:10 skrev
Alessandro Pasotti:<br>
</div>
<blockquote type="cite"
cite="mid:CAL5Q6733BeyQEax7Myamjgd8evVpMKeBNGGVAOmVc+YwQ2a=_w@mail.gmail.com">
<div dir="ltr">
<div dir="ltr"><br>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Fri, May 10, 2019 at 5:04
PM Richard Duivenvoorde <<a
href="mailto:rdmailings@duif.net" moz-do-not-send="true">rdmailings@duif.net</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
Just tried with a Geoserver (2.15) I have running here.<br>
<br>
Moved a shp file with 1 feature in a postgis db, created a
wfs service<br>
for it and loaded in QGIS, styled it etc: fine.<br>
<br>
Removed the (one) feature from the table, retarted
Geoserver, and had a<br>
look into the requests QGIS is firing [0].<br>
<br>
QGIS first does a GetCapabilities request:<br>
/geoserver/wfs?SERVICE=WFS&REQUEST=GetCapabilities&ACCEPTVERSIONS=2.0.0,1.1.0,1.0.0<br>
<br>
Then a describefeature request (to determine the type):<br>
<br>
geoserver/wfs?SERVICE=WFS&REQUEST=DescribeFeatureType&VERSION=2.0.0&TYPENAME=test:wfspointPostgis<br>
<br>
That one responses with<br>
<br>
<xsd:element maxOccurs="1" minOccurs="0" name="geom"
nillable="true"<br>
type="gml:PointPropertyType"/><br>
<br>
So Geoserver (still?) knows about the geometry type of the
table.<br>
Not sure if that is cached? But it is available in the<br>
'geometry_columns'-view of Postgis (stating type=POINT).<br>
<br>
And QGIS keeps the styling, even after several
restarts/reloads.<br>
<br>
One thing I can think of: is that the postgis table does not
have a type<br>
defined, with someting like:<br>
geom geometry<br>
instead of<br>
geom geometry(Point,28992),<br>
what I see when the table created via QGIS...<br>
<br>
Could it be that<br>
- geoserver cannot determine the type?<br>
<br>
To test I created a minimal table:<br>
CREATE TABLE public.testgeoms<br>
(<br>
id integer,<br>
geom geometry,<br>
fid integer NOT NULL DEFAULT
nextval('testgeoms_fid_seq'::regclass),<br>
CONSTRAINT foo PRIMARY KEY (fid)<br>
)<br>
<br>
At that moment the geometry_columns view shows type=GEOMETRY<br>
So (I think) without data, geoserver can never determine the
type, hence<br>
shows:<br>
<xsd:element maxOccurs="1" minOccurs="0" name="geom"
nillable="true"<br>
type="gml:GeometryPropertyType"/><br>
in the DescribeFeatureType response...<br>
<br>
And indeed if I then empty the table, and reopen the project
the<br>
WFS-point layer shows an 'img', as it cannot style the zero
features.<br>
AND it does not have type info?<br>
<br>
So (confirmed) having that layer with zero (of unknown type)
features,<br>
QGIS forgets the style...<br>
<br>
Is that a bug?<br>
<br>
</blockquote>
<div><br>
</div>
<div>QGIS styles are bound to geometry types, it the type is
unknown there is not much we can do .... something similar
happens for bad/broken layers.</div>
<div><br>
</div>
<div>For that implementation I'm storing the original XML
layer style that can be re-applied in case the "bad layer"
is fixed, maybe we can re-use that mechanism for this use
case.<br>
</div>
<div><br>
</div>
<div><br>
</div>
<div> </div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">
IF you create the layers with a feature TYPE (geom<br>
geometry(Point,28992)) then the type will show up in the<br>
'geometry_columns'-view and then in the describefeature
response.<br>
<br>
Without that knowledge QGIS retrieves NO type information
(via the<br>
describefeature) upon loading of the project/layer and
(apparently)<br>
forgets the type in the layer???<br>
<br>
Does it work with you if you put the type in the table
definitions in<br>
Postgis (so they show up in the 'geometry_columns'-view)?<br>
With me it does, but I just did a quick test.<br>
<br>
Else I would create an issue for it or let somebody dive
into this...<br>
<br>
Regards,<br>
<br>
Richard Duivenvoorde<br>
<br>
<br>
[0] <a
href="https://github.com/rduivenvoorde/qgisnetworklogger/tree/model_rewrite"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://github.com/rduivenvoorde/qgisnetworklogger/tree/model_rewrite</a><br>
<br>
<br>
On 09/05/2019 22.22, Bo Victor Thomsen wrote:<br>
> Hi Lars and other members of this list..<br>
> <br>
> I feel somewhat obligated to help Lars since it's a
problem he has<br>
> inherited from me. (Sorry about that...) <br>
> <br>
> To sum it up:<br>
> <br>
> If QGIS connects to a WFS based layer from Geoserver
using an existing<br>
> project with styled layers and one or more layers
containing /zero/<br>
> objects, QGIS can't determine the object type (point,
line-string,<br>
> polygon...) for those layers. QGIS will not show
legends and promptly<br>
> forget any layer information stored in the project
related to the<br>
> object-type, ex. styling information.<br>
> <br>
> And what is worse - this is a permanent situation: If
you save the<br>
> project in the above-mentioned state and reopen it at a
later time -<br>
> where the layers /now/ contains objects - QGIS has
forgotten the styling<br>
> and other information, so the layers stays invisible
and without the<br>
> former styling.<br>
> <br>
> (long posting...)<br>
> <br>
> I've done some tests to determine the root cause of
this problem:<br>
> <br>
> * Setup of a Postgres database /and /a SQL Server
database.<br>
> <br>
> * Loaded the same MapInfo-dataset twice into each
database, called<br>
> them tableA and tableB, and populated the
meta-table<br>
> "dbo.geometry_columns" with the correct information
for tableA and<br>
> tableB in SQLServer.<br>
> <br>
> * Installed a fresh Geoserver 2.15 and MapServer
TinyOWS WFS-T server<br>
> <br>
> * Setup a Workspace in Geoserver with 4 layers:
tableA and -B from<br>
> SQLServer and the same tables from Postgres using
WFS.<br>
> <br>
> * In Geoserver, the datastore definition for the
SQLServer tables has<br>
> a pointer to the "dbo.geometry_columns" meta-table.<br>
> <br>
> * Setup of TinyOWS with tableA and -B from Postgres<br>
> <br>
> * And lastly: Made a project in QGIS 3.4.7 /Windows
64bit with the 6<br>
> WFS layers:<br>
> <br>
> o tableA is shown 3 times:<br>
> <br>
> 1. From SQLServer through GeoServer;<br>
> 2. From Postgres through GeoServer<br>
> 3. From Postgres through Tiny-WFS<br>
> <br>
> o tableB is shown 3 times using the same setup as
tableA<br>
> <br>
> All 6 layers in QGIS was shown correctly and the object
type for all<br>
> layers was recognized.<br>
> <br>
> After this I /deleted/ all rows from tableB in /both/
MS-SQLServer and<br>
> in Postgres and reopened the same QGIS project:<br>
> <br>
> * The layer showing (the zero objects) tableB from
SQLServer through<br>
> GeoServer was not in order.<br>
> * The 5 other layers was shown correctly. Legends was
correct and<br>
> styling was not deleted<br>
> <br>
> I'm drawing the following conclusion:<br>
> <br>
> * GeoServer WFS connections (maybe in conjunction
with QGIS) doesn't<br>
> work properly when the dataprovider is MS-SQLServer
and the layers<br>
> contains zero objects. More precisely: GeoServer's
SQLServer<br>
> dataprovider can't or don't use geometry_columns
meta-table defined<br>
> in the datastore as described in:<br>
> <a
href="https://docs.geoserver.org/stable/en/user/data/database/sqlserver.html#using-the-geometry-metadata-table"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://docs.geoserver.org/stable/en/user/data/database/sqlserver.html#using-the-geometry-metadata-table</a><br>
> .<br>
> <br>
> After this test I've tried to change the placement and
existence of the<br>
> "geometry_columns" metadata table: <br>
> <br>
> * No value<br>
> * dbo.geometry_column (ordinary placement)<br>
> * data.geometry_column (I made a copy of the
geometry_columns table<br>
> and placed it in the same schema as the test
tables)<br>
> * geometry_columns (without schema definition)<br>
> <br>
> Nothing of the above worked.<br>
> <br>
> @Lars:<br>
> You might change your database system to Postgres and
perhaps replace<br>
> GeoServer with TinyOWS for WFS purposes (ducks and and
run....) .<br>
> Or getting the issue investigated and fixed using some
GeoServer core<br>
> developer, perhaps in cooperation with a QGIS core
developer?<br>
> Apparently the problem is fixable, since neither
TinyOWS or<br>
> GeoServer/Postgres have the same issues.<br>
> <br>
> Obviously, the above is a far cry from a really
rigorous investigation<br>
> of the problem, but it points at some of the "pain"
points<br>
> <br>
> Regards<br>
> <br>
> Bo Victor Thomsen<br>
> <br>
> AestasGIS<br>
> Denmark<br>
> <br>
> Den 09-05-2019 kl. 13:36 skrev Lars I. Nielsen, LIFA
A/S:<br>
>><br>
>> Hi developers.<br>
>><br>
>> <br>
>><br>
>> I asked this question on the user’s list, so far
without any answers,<br>
>> but have been advised to ask here as well.<br>
>><br>
>> <br>
>><br>
>> <br>
>><br>
>> I’m working with a number of projects, which
statically open a fixed<br>
>> number of layers. All layers are fetched using WFS
/ WFS-T.<br>
>><br>
>> <br>
>><br>
>> Each layer has an extensive rule based styling, and
an extensive<br>
>> field/attribute dialog setup. This is saved in the
project files.<br>
>><br>
>> <br>
>><br>
>> Our plugin changes the data provider filter for
many layers, when the<br>
>> user works with the data. These filter expressions
are also saved in<br>
>> the project, of course.<br>
>><br>
>> <br>
>><br>
>> Our problem is, that if the user accidently chooses
a work area (= new<br>
>> data provider filter), which contain no data in any
layer, those<br>
>> layers lose all styling etc. And it’s apparently
impossible to recover<br>
>> these styles etc. by changing the filter back.<br>
>><br>
>> <br>
>><br>
>> So basically the user’s project is unrecoverably
trashed at this point<br>
>> in time.<br>
>><br>
>> <br>
>><br>
>> A - Is this a correct description of how QGIS
handles “no-data” layers ?<br>
>><br>
>> <br>
>><br>
>> B - Are there any standard ways to handle this in
QGIS (using metadata<br>
>> or otherwise) in an automated fashion (i.e. not
manually) ?<br>
>><br>
>> <br>
>><br>
>> C - If the answers to A and B are yes and no resp.,
are there any<br>
>> plans to remedy this unnecessarily serious problem
?<br>
>><br>
>> <br>
>><br>
>> <br>
>><br>
>> Might it not be solved simply by adding an
(optional?) layer metadata<br>
>> key, that stores the last used topology, and revert
to this if no data<br>
>> are found on the layer ?<br>
>><br>
>> <br>
>><br>
>> <br>
>><br>
>> Using 3.4.5 x64 (Danish)<br>
>><br>
>> <br>
>><br>
>> <br>
>><br>
>> Med venlig hilsen<br>
>><br>
>> Lars I. Nielsen, LIFA A/S<br>
>> GIS-konsulent, FME Certified Professional<br>
>><br>
>> Beskrivelse: <a
href="http://website.lifa.dk/lsp.gif" rel="noreferrer"
target="_blank" moz-do-not-send="true">http://website.lifa.dk/lsp.gif</a><br>
>><br>
>> *T*<br>
>><br>
>> <br>
>><br>
>> 6313 6800<br>
>><br>
>> <br>
>><br>
>> *@* <br>
>><br>
>> <br>
>><br>
>> <a href="mailto:lin@lifa.dk" target="_blank"
moz-do-not-send="true">lin@lifa.dk</a><br>
>><br>
>> *D*<br>
>><br>
>> <br>
>><br>
>> 6313 6849<br>
>><br>
>> <br>
>><br>
>> *W*<br>
>><br>
>> <br>
>><br>
>> <a href="http://www.lifa.dk" rel="noreferrer"
target="_blank" moz-do-not-send="true">www.lifa.dk</a>
<<a href="http://www.lifa.dk" rel="noreferrer"
target="_blank" moz-do-not-send="true">http://www.lifa.dk</a>><br>
>><br>
>> *M*<br>
>><br>
>> <br>
>><br>
>> 2492 4866<br>
>><br>
>> <br>
>><br>
>> *CVR*<br>
>><br>
>> <br>
>><br>
>> 20937289<br>
>><br>
>><br>
>> Beskrivelse:<br>
>>
C:\Users\lin\AppData\Roaming\Microsoft\signatures\21x21-Images-Get-L749-l8.png<br>
>> <<a
href="https://www.linkedin.com/company/lifa-a-s/"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://www.linkedin.com/company/lifa-a-s/</a>><br>
>> Følg os på LinkedIn og læs de seneste nyheder fra
LIFA A/S <br>
>><br>
>><br>
>><br>
>> _______________________________________________<br>
>> QGIS-Developer mailing list<br>
>> <a href="mailto:QGIS-Developer@lists.osgeo.org"
target="_blank" moz-do-not-send="true">QGIS-Developer@lists.osgeo.org</a><br>
>> List info: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-developer"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
>> Unsubscribe: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-developer"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
> <br>
> <br>
> _______________________________________________<br>
> QGIS-Developer mailing list<br>
> <a href="mailto:QGIS-Developer@lists.osgeo.org"
target="_blank" moz-do-not-send="true">QGIS-Developer@lists.osgeo.org</a><br>
> List info: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-developer"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
> Unsubscribe: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-developer"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
> <br>
<br>
_______________________________________________<br>
QGIS-Developer mailing list<br>
<a href="mailto:QGIS-Developer@lists.osgeo.org"
target="_blank" moz-do-not-send="true">QGIS-Developer@lists.osgeo.org</a><br>
List info: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-developer"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a><br>
Unsubscribe: <a
href="https://lists.osgeo.org/mailman/listinfo/qgis-developer"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/qgis-developer</a></blockquote>
</div>
<br clear="all">
<br>
-- <br>
<div dir="ltr" class="gmail_signature">Alessandro Pasotti<br>
w3: <a href="http://www.itopen.it" target="_blank"
moz-do-not-send="true">www.itopen.it</a></div>
</div>
</blockquote>
<br>
</body>
</html>