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

[Luigi Pirelli]

you should look into the history of the gpkg format definition vs
spatialite format adoption. Nothing related with QGIS, or gdal or etc etc
Luigi Pirelli

**************************************************************************************************
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Mastering QGIS 2nd Edition:
*
https://www.packtpub.com/big-data-and-business-intelligence/mastering-qgis-second-edition
* Hire me: http://goo.gl/BYRQKg
**************************************************************************************************


On Thu, 13 Sep 2018 at 14:30, Bo Victor Thomsen <bo.victor.thomsen at gmail.com>
wrote:

> 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
>
> _______________________________________________
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20180913/14b9a7ec/attachment.html>