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

Thu Sep 13 13:40:11 PDT 2018

Ok, after some googling, I have a glimmer of understanding of the
(technical and personal) issues. Enough said about that...

So technically, GPKG support in QGIS is a hybrid of a "simple" GPKG
implementation bundled/merged with the spatialite sqlite-extension incl.
conversion functions, so you can convert the GPKG binary object to and fro
a spatialite ditto -  when you have to use som spatialite-specific
functionality like spatial SQL, projection support and so on ?
(If there is a web page somewhere that explains this in detail, i'll
respecfully redraw my questions and do some RTFM)

Regards
Bo Victor Thomsen

Den tor. 13. sep. 2018 kl. 16.00 skrev Luigi Pirelli <luipir at gmail.com>:

> 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
>
>

-- 
Med venlig hilsen

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