2

In GeoServer 2.15.2 I can "publish" an entire layer from a GeoPackage. No issues there, works great.

However, If I attempt to "Configure a new SQL view..." using that same layer in the GeoPackage (or any layer from any GeoPackage), GeoServer fails to recognize "geom" as the geometry column. In the "Edit SQL view" screen, the "type" for "geom" is listed as "BigDecimal".

What I would like to do via the "SQL View" is to publish a layer from a GeoPackage and use a SQL query to scope the results (cannot do this if I just publish the entire GeoPackage layer).

Note: As I mentioned, GeoServer does indeed recognize "geom" when you "publish" the entire GeoPackage layer . . . it can successfully identify the geometry type and SRID. This fails when trying to "Configure a new SQL view...".

Update: GeoServer does not prevent me from creating/saving a SQL View layer using a GeoPackage as the "Store". However, it is useless because the geometry type and SRID are unknown (and the Bounding Boxes are populated with invalid data). However, if I manually edit the "featuretype.xml" file for the layer and populate it with the correct values, I can at least view it via "Layer Preview".

Improve this question
5
  • Can you share more info, like the structure of the original table, the SQL view you're setting up, and the full stack trace of the output?
    – Andrea Aime
    Commented Aug 14, 2019 at 19:36
  • There is no stack trace and issue occurs if there is one column (attribute) or 100 in the table (layer). I am creating the GeoPackage file and the layers in it using QGIS 3.4.9. GeoServer 2.15.2 correctly identifies the geometry column ("geom") and geometry type when publishing an entire layer. However, if I "Create new SQL view" instead and select "Guess geometry type and srid" and click "refresh", GeoServer lists the "type" for "geom" as "BigDecimal" (the correct value should be "Polygon", "Point", etc).
    – Bruce Loth
    Commented Aug 14, 2019 at 21:02
  • Also should note that "SQL Views" work fine in this version of GeoServer when using a different "Store" such as Oracle. Fails when the "Store" is a GeoPackage.
    – Bruce Loth
    Commented Aug 14, 2019 at 21:09
  • I have observed exactly the same issue with GeoServer v2.19.1 two years later.
    – PeterS
    Commented Nov 4, 2021 at 9:26
  • 1
    GeoServer issue logged here: osgeo-org.atlassian.net/browse/GEOS-9835
    – PeterS
    Commented Nov 4, 2021 at 9:38

1 Answer 1

Reset to default
2

The workaround (as of GeoServer v2.19/2.20) is to Add a New Layer > Configure new SQL view... >

select * from table

(or rather your list of columns)

Then under Attributes > Refresh
GeoServer will pick up the geometry column incorrectly as BigDecimal
Save
Back in Edit Layer, the Declared SRS will be EPSG:404000 (not found) - leave it like that for now.
Set the Bounding Boxes and Save

Log into the server and change directory to the <GEOSERVER_DATA_DIR>/workspaces/<workspace>/<data store>/<layer>
Edit the featuretype.xml file and find the line <escapeSql>false</escapeSql>
After this and before </virtualTable>, add the lines:

<keyColumn>id</keyColumn>
<geometry>
  <name>geom</name>
  <type>Polygon</type>
  <srid>4326</srid>
</geometry>

(making adjustments to your id and geometry column name, type and SRID, as necessary).
Save the file

Back in GeoServer GUI > Server Status > Reload the Configuration and catalog
Edit the layer again > Edit sql view > and now the geometry column and type is correct. Just don't press Refresh again.
Save
Now the Native SRS is correct (EPSG:4326 above) and the Bounding Boxes can now be set correctly.
Proceed normally.

WFS on this GeoPackage SQL View layer certainly works, but I'm currently not 100% sure about WMS.

Improve this answer
2
  • Just note that the SQL variant used by GeoPackage (spatialite?) does not support full spatial functions like ST_Distance, etc. There is a very limited subset (including ST_MinX, etc): that are included in the base module: ref: gaia-gis.it/gaia-sins/spatialite-sql-latest.html#p5
    – PeterS
    Commented Nov 4, 2021 at 15:10
  • Awesome, thanks for the great write up. I had completely forgotten about this issue (went down some other road and did not look back). Still using a lot of GeoPackages with GeoServer so I am sure this will come up again in the future.
    – Bruce Loth
    Commented Nov 5, 2021 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.