Re: [Geoserver-users] Issues with Geopackage support?

Steggink, Frank Tue, 24 Oct 2017 03:02:52 -0700

Hi Ian,

I have created the following issue: 
https://osgeo-org.atlassian.net/browse/GEOT-5852. I’m not authorized to assign 
it to you. I’ve added my test data, as well the WMS and WFS requests I could 
reproduce this issue with.

Regards,

Frank

Van: Ian Turton [mailto:ijtur...@gmail.com]
Verzonden: dinsdag 24 oktober 2017 11:05
Aan: Rahkonen Jukka (MML); Steggink, Frank
CC: geoserver-users@lists.sourceforge.net
Onderwerp: Re: [Geoserver-users] Issues with Geopackage support?

It looks like GeoPkgDialect should override encodeTableName & encodeColumnName
to handle this case and to force lower case names. Can you raise a GeoTools
ticket preferably with a test case or test data and assign it to me.

Cheers

Ian

On 24 October 2017 at 09:46, Rahkonen Jukka (MML)
<jukka.rahko...@maanmittauslaitos.fi<mailto:jukka.rahko...@maanmittauslaitos.fi>>
wrote:
Hi,

Sorry, I just did not read your mail long enough for seeing that you already
used –nln for getting rid of the dot in the name of the table.

In principle using delimited identifiers everywhere would be the right
solution. That would make a difference between
"mosselenoesterhabitats.mosselenoesterhabitats" and
"mosselenoesterhabitats”.”mosselenoesterhabitats"

However, as you can see from
https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Data_structure_definition/Delimited_identifiers
there are quite a lot differences in how delimited identifiers are handled by
the database engines and SQLite is the most special of all. For example you can
create a table "Mosselenoesterhabitats.Mosselenoesterhabitats" or
"mosselenoesterhabitats.mosselenoesterhabitats" but not both because the names
are the same for SQLite. This issue is recognized in the GeoPackage standard
which states “All specified table and column name values SHALL be lowercase”
but there are not similar rules about how to use characters with special
meaning for SQL.

As far as I can see all the SQL examples in the GeoPackage standard are using
the simple, non-delimited syntax. I did not test and it may be that there will
be no problems if for example gpkg_geometry_columns contain identifiers with
dots in the name in columns table_name and column_name in queries like
SELECT table_name, column_name FROM gpkg_geometry_columns WHERE table_name IN
(SELECT table_name FROM gpkg_contents WHERE data_type == ‘features'));

In any case such examples lead developers to use the similar SQL syntax in
other places instead of the delimited version
SELECT “table_name”, “column_name” FROM “gpkg_geometry_columns” WHERE
“table_name” IN (SELECT “table_name” FROM “gpkg_contents” WHERE “data_type” ==
‘features'));

I am pretty sure that creating GeoPackages with dots and perhaps some other
special characters in table and columns names means pegging for troubles when
it comes to interoperability. Even if Geoserver would be patched to handle them
right I would say that it would be better not to deliver such GeoPackages for
other users outside your organization. That affects also Geoserver which in my
mind should launder names like my.dot.column into my_dot_columns by default.

-Jukka Rahkonen-

Lähettäjä: Steggink, Frank
[mailto:frank.stegg...@kadaster.nl<mailto:frank.stegg...@kadaster.nl>]
Lähetetty: 24. lokakuuta 2017 11:00
Vastaanottaja: Rahkonen Jukka (MML)
<jukka.rahko...@maanmittauslaitos.fi<mailto:jukka.rahko...@maanmittauslaitos.fi>>;
Andrea Aime <andrea.a...@geo-solutions.it<mailto:andrea.a...@geo-solutions.it>>
Kopio:
geoserver-users@lists.sourceforge.net<mailto:geoserver-users@lists.sourceforge.net>
Aihe: RE: [Geoserver-users] Issues with Geopackage support?

Hi Jukka,

When attaching another database with the table mosselenoesterhabitats, then my
query without the quotes succeeds. However, that is not a viable option for
usage in geopackage, since a geopackage is supposed to be self-contained. If I
were to add a connection to another database, I would need to add a second
connection as well, because of the spatial index
(rtree_mosselenoestehabitats.mosselenoesterhabitats_geom).

I’ve already mentioned that I used -nln as a workaround. This is working fine.
I’ll keep on using it for now. Wouldn’t it be better to support table names
which contains dots? Apparently they are valid in SQLite. Querying a table name
without dots surrounded by double quotes also works, for example: sqlite>
select count(*) from "mosselenoesterhabitats";

Regards,

Frank Steggink

Van: Rahkonen Jukka (MML) [mailto:jukka.rahko...@maanmittauslaitos.fi]
Verzonden: zondag 22 oktober 2017 22:29
Aan: Andrea Aime; Steggink, Frank
CC:
geoserver-users@lists.sourceforge.net<mailto:geoserver-users@lists.sourceforge.net>
Onderwerp: Re: [Geoserver-users] Issues with Geopackage support?

Hi Frank,

I would test if table name without the dot would work better. Give some other
name with -nln

ogr2ogr -f GPKG mossel.gpkg "PG:dbname='#DB-name#' host='#Host-IP#' port=#Port#
user='#User-name#' password='#Password#'"
"mosselenoesterhabitats.mosselenoesterhabitats" -nln name_without_a_dot.

The table name that you have requires double quotes in SQL. This tries to find
table
mosselenoesterhabitats from database mosselenoesterhabitats

select * from mosselenoesterhabitats.mosselenoesterhabitats;

SQL that works with your table would look like

select * from "mosselenoesterhabitats.mosselenoesterhabitats";

-Jukka Rahkonen-

________________________________
Lähettäjä: Andrea Aime
<andrea.a...@geo-solutions.it<mailto:andrea.a...@geo-solutions.it>>
Lähetetty: 21. lokakuuta 2017 12:14
Vastaanottaja: Steggink, Frank
Kopio:
geoserver-users@lists.sourceforge.net<mailto:geoserver-users@lists.sourceforge.net>
Aihe: Re: [Geoserver-users] Issues with Geopackage support?

Hi Frank,
at least part of the issues you're seeing seem to be related to running inside
docker.
Can you do tests on the native OS instead, to check if they are reproducable?

As for a test file, yep, if you could make it available it would be nice,
someone else could run tests too.

Cheers
Andrea

On Fri, Oct 20, 2017 at 4:33 PM, Steggink, Frank
<frank.stegg...@kadaster.nl<mailto:frank.stegg...@kadaster.nl>> wrote:
Hi,

I’m investigating to serve Geopackages through Geoserver 2.12. We are using
Docker, with Geoserver 2.12.0 and Tomcat 8.5.20. I just started experimenting
with Geopackage, using Geoserver 2.11 and the Geopackage plugin, when Geoserver
2.12 was released with core support for Geopackage.

With the old setup I was able to serve Geopackages, although the performance
was disappointing, because Geotools version 17 didn’t support the spatial index
from Geopackage. So I’m glad to notice that spatial index support was added to
2.12 as well (https://osgeo-org.atlassian.net/browse/GEOT-5826). Your release
notes (http://blog.geoserver.org/2017/10/17/geoserver-2-12-0-released/) seem to
be outdated: “Currently, GeoPackage vector does not support spatial indexes but
stay tuned, it’s cooking!”

Anyways, it is possible that some of the issues I’m mentioning are due to our
setup and because we’re just dipping our toes in the water when it comes to
Geopackage. My apologies if that turns out to be the case, although I did make
sure that this was not the case. I also decided to write this e-mail first,
before setting out for Jira straight ahead.

First of all, I’m having difficulties adding a new geopackage store. I’m adding
a new datastore by just specifying the data source name and the database file
(SQLite file), using the file browser. In the GEOSERVER_DATA_DIR (which is at
/data in my container) I’ve created a subdirectory, also called “data”, which
is mounted as a volume from the host into my Docker container. Because it is a
subdirectory of the Geserver data dir, I’m getting the following relative path:
file:data/mossel.gpkg. Since there are no other mandatory parameters (the
namespace comes from my workspace), I just click “Save”, but the following
error message appears: Error creating data store, check the parameters. Error
message: Unable to obtain connection: Cannot create PoolableConnectionFactory
(path to 'data/mossel.gpkg': '/usr/local/tomcat/data' does not exist). The
directory /usr/local/tomcat is the Tomcat (Catalina) base dir. When creating a
symbolic link from /usr/local/tomcat to /data/data (by executing the command ln
-s /data/data from within /usr/local/tomcat), I’m not getting this error
message anymore. So I’m fairly certain that this issue is not caused by my
particular setup. My guess is that when the connection to the SQLite database
is being set up, the relative path is not prefixed by the Geoserver data dir.

My second issue occurs when I’m publishing the only layer which is contained in
my geopackage. I’ve just calculated the bbox and WGS84 bbox, and press Save.
When using WMS or WFS, my layer shows up in the Capabilities document. However,
when executing a GetMap request, I’m getting a service exception:

<?xml version="1.0" encoding="UTF-8"?><ServiceExceptionReport version="1.3.0"
xmlns="http://www.opengis.net/ogc";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://www.opengis.net/ogc
http://localhost:8080/geoserver/schemas/wms/1.3.0/exceptions_1_3_0.xsd";>
<ServiceException code="internalError">
Rendering process failed
java.io.IOExceptionBorrow prepareStatement from pool failed
[SQLITE_ERROR] SQL error or missing database (no such table:
rtree_mosselenoesterhabitats.mosselenoesterhabitats_geom)
</ServiceException></ServiceExceptionReport>

My apologies for the unwieldy layer name. I’m getting a similar service
exception when doing a WFS GetFeature request. When accessing my geopackage
with the sqlite3 CLI I’m seeing this table (and also the main table) with the
.tables command. But when doing a count on this table (or the main table), I’m
also getting a “no such table” exception. I’m getting a count when enclosing
the table name within double quotes, like: select count(*) from
"rtree_mosselenoesterhabitats.mosselenoesterhabitats_geom". (The result is 577.)

I have created this geopackage with OGR 2.2
(http://www.gdal.org/drv_geopackage.html) as an export of a PostGIS table using
the following command: ogr2ogr -f GPKG mossel.gpkg "PG:dbname='#DB-name#'
host='#Host-IP#' port=#Port# user='#User-name#' password='#Password#'"
"mosselenoesterhabitats.mosselenoesterhabitats". In case anyone is interested,
I can share this geopackage. As a workaround I recreated the geopackage by
appending -nln mosselenoesterhabitats to my OGR command. I don’t care about the
schema name.

And lastly, I’ve also found a weird issue when putting my geopackage in the
Geoserver data dir itself (/data, not mounted as a Docker volume), and adding
it as a store. The database setting at the connection parameters is just
“file:mossel.gpkg”. When clicking Save, I’m getting an overview of the layers I
wish to publish, but it is showing no layers. I’m also not seeing any errors.
When adding a new layer and selecting the geopackage store, I’m also getting an
empty list of layers. I could provide a screenshot if necessary. I’m able to
reproduce this issue on a Windows 7 machine with the standard Geoserver 2.12.0
installation, and putting the geopackage in the data dir. (Because the data dir
is still in c:\program files (standard), I had to start Geoserver as
administrator, because the JDBC driver needs write access to this dir.)

Is anyone able to confirm these issues, and, if this is the case, what would be
the best way to go forward? Next week I might be able to create some patches.
Please let me know if more information is required (data, screenshots). With
regard to my own work, there is no blocking issue because of the workarounds.
They might be useful to someone else who is looking into serving Geopackage
files with Geoserver.

Thank you in advance,

Frank Steggink
Kadaster PDOK

Disclaimer:
De inhoud van dit bericht is uitsluitend bestemd voor geadresseerde.
Gebruik van de inhoud van dit bericht door anderen zonder toestemming van het
Kadaster
is onrechtmatig. Mocht dit bericht ten onrechte bij u terecht komen, dan
verzoeken wij u
dit direct te melden aan de verzender en het bericht te vernietigen.
Aan de inhoud van dit bericht kunnen geen rechten worden ontleend.

Disclaimer:
The content of this message is meant to be received by the addressee only.
Use of the content of this message by anyone other than the addressee without
the consent
of the Kadaster is unlawful. If you have received this message, but are not the
addressee,
please contact the sender immediately and destroy the message.
No rights can be derived from the content of this message.

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html

Geoserver-users@lists.sourceforge.net<mailto:Geoserver-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Regards,

Andrea Aime

==
GeoServer Professional Services from the experts! Visit http://goo.gl/it488V
for more information.
==

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313<tel:+39%200584%20962313>
fax: +39 0584 1660272<tel:+39%200584%20166%200272>
mob: +39 339 8844549<tel:+39%20339%20884%204549>

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i
file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo
è consentito esclusivamente al destinatario del messaggio, per le finalità
indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne
il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di
procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro
sistema. Conservare il messaggio stesso, divulgarlo anche in parte,
distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse,
costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the
attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act (Legislative
Decree June, 30 2003, no.196 - Italy's New Data Protection Code).Any use not in
accord with its purpose, any disclosure, reproduction, copying, distribution,
or either dissemination, either whole or partial, is strictly forbidden except
previous formal approval of the named addressee(s). If you are not the intended
recipient, please contact immediately the sender by telephone, fax or e-mail
and delete the information in this message that has been received in error. The
sender does not give any warranty or accept liability as the content, accuracy
or completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of e-mail
transmission, viruses, etc.

Geoserver-users@lists.sourceforge.net<mailto:Geoserver-users@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Ian Turton

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot

_______________________________________________
Geoserver-users mailing list

Please make sure you read the following two resources before posting to this 
list:
- Earning your support instead of buying it, but Ian Turton: 
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines: 
http://geoserver.org/comm/userlist-guidelines.html

Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Re: [Geoserver-users] Issues with Geopackage support?

Reply via email to