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

Fri Sep 14 01:34:14 PDT 2018

Hi!
A question related to this. I have set up a PostGIS structure with tables, rules, views, triggers and constraints. To this there is a QGIS project with styling, forms, relations and so on. If I want to make a file based version of this for testing, working copies, versions, distribution etc. where do I put the database logic? Could it fit in a file based database or virtual layers? Which strategy is the best? Any ideas or experiences?

Regards
Karl-Magnus Jönsson

Från: Qgis-user [mailto:qgis-user-bounces at lists.osgeo.org] För Bo Victor Thomsen
Skickat: den 13 september 2018 22:40
Till: qgis-user at lists.osgeo.org
Ämne: Re: [Qgis-user] Geopagkage and "geom" in DBmanager

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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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/20180914/da77465e/attachment.html>