[Qgis-user] Geopagkage and "geom" in DBmanager

[Bo Victor Thomsen]

Ok.. From the Spatialite 4.2.0 wiki:


   - *CastAutomagic()*: will indifferently accept both GPKG and SpatiaLite
   BLOB Geometries, then returning anyway a SpatiaLite's own BLOB Geometry.


I can confirm, that using the "CastAutomagic(geom)" function instead of the
simple field "geom"  will get the st_area and other ogc functions in the
QGIS Databasemanager Dataprovider for GeoPackage to work.

That means that the geopackage binary format for geometry is incompatible
with the underlying spatialite spatial system and functions.

This is *not* the most helpful and thoughtful user oriented solution I've
encountered ... Or should I have RTFM somewhere in the QGIS.org site ?

However it does work. Thanks for the tip




Den tor. 13. sep. 2018 kl. 13.20 skrev Jean-Baptiste Desbas <
jb.desbas at gmail.com>:

> Hi,
>
> I have to use *st_area(CastAutomagic(geom)) *instead of *st_area(geom)*,
> otherwise null column is retourned.
>
> Le jeu. 13 sept. 2018 à 11:45, Bo Victor Thomsen <
> bo.victor.thomsen at gmail.com> a écrit :
>
>> Hi Lene -
>>
>> I've made a few observations regarding the use of GeoPackage in
>> DBManager...
>>
>> I have a *GeoPackage* based table called "renhold" containing polygons.
>> Primary key column is an integer called "fid" and the geometry column is
>> called "geom" - It's pretty close to your setup
>>
>> If I use the *DBManager* with the *GeoPackage *dataprovider and execute
>> the following statement: *select fid, st_area (geom) from renhold , *it
>> returns a table with 2 columns as expected. However , column nr. 2 contains
>> only nulls. However, the statement does not result in an error. The
>> function "st_area" simply doesn't return a value besides null.
>>
>> An now the mystery!! :
>>
>> If I use the *DBManager* with the *Virtual Layers -> Project layers *dataprovider
>> and execute the following statement: *select fid, st_area (geometry)
>> from renhold , *it returns a table with 2 columns as expected. And
>> column nr. 2 do contain the expected area values. Go figure...
>>
>> NB! The "renhold" layer is visible in the map and named "renhold"
>> NBB! In the *Virtual Layers* dataprovider you have to use the standard
>> name "geometry" to represent the geometry column - not the real column name
>> ("geom" in this case)
>>
>> Actually I have a reasonable explanation: The 2 different dataproviders
>> uses different internal functions to calculate the area. However the
>> function used by the GeoPackage Dataprovider doesn't work !
>>
>>
>> Regards
>> Bo Victor Thomsen
>> LIFA Denamrk
>>
>> Den tor. 13. sep. 2018 kl. 09.27 skrev Alexandre Neto <
>> senhor.neto at gmail.com>:
>>
>>> Hi Lee,
>>>
>>> Can you try removing the quotation marks around geom?
>>>
>>> Best wishes,
>>>
>>> Alex Neto
>>>
>>> A qui, 13/09/2018, 02:04, Lene Fischer <lfi at ign.ku.dk> escreveu:
>>>
>>>> Hi,
>>>> Trying out a geopackage layer in DBManager asking a simple SQL
>>>> statement st_area
>>>> But when I try to write a statement like this
>>>>
>>>> SELECT "vandopland".* , st_area("vandopland"."geom") as areal_m2
>>>> FROM "vandopland"
>>>>
>>>>  "geom" is not found. Suggestions as geomfromexifgpsblob or geomfromfgf
>>>> or geomfromwkb . Then I get "an error occured"
>>>>
>>>> Any suggestions what I might have done wrong.
>>>>
>>>> Regards from a newbee Geopagkace/DBmanager
>>>>
>>>> Lene Fischer
>>>>
>>>>
>>>> _______________________________________________
>>>> Qgis-user mailing list
>>>> Qgis-user at lists.osgeo.org
>>>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>> --
>>> Alexandre Neto
>>> ---------------------
>>> @AlexNetoGeo
>>> http://sigsemgrilhetas.wordpress.com
>>> http://gisunchained.wordpress.com
>>> _______________________________________________
>>> Qgis-user mailing list
>>> Qgis-user at lists.osgeo.org
>>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>>
>> --
>> Med venlig hilsen
>>
>> Bo Victor Thomsen
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
>

-- 
Med venlig hilsen

Bo Victor Thomsen
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20180913/50b3ea51/attachment.html>