Geoserver – Cloudy DATA Blog

Geoserver – CREATE View

Geoserver has this really great feature whereby admins are able to create views that are held with the configuration of the Geoserver but separate from the database. This allows admins the ability to create views WITHOUT the need to have database admin rights on the server. This is particularly useful where you have an onsite vendor database that you would not otherwise be allowed to write views in but nonetheless you require some way to store spatially enabled views .

Quick setup guide how to do this.

Firstly go to layers and hit

Select the Store that you are interested in

Next you should see text and just hit it.

Then there will be a place where you can put the SQL Statement in.

GeoServer and ORACLE – WFS & WMS Config Tips

This post is a brief description of my findings on setting up Geoserver from scratch and some particular points on setting it up for an Oracle connection. I hope it will prove useful for others.

INSTALLATION
GeoServer with ORACLE database connection support
Geoserver Postgres (PostGIS) connection support comes with the default installation. This is NOT the case for Oracle or SQL Server therefore for enterprise purposes it is a requirement to install Geoserver WITH Oracle and SQL Server extensions if you wish to support these database connections.

I’ve done a post on Geoserver on windows installation link here that talks specifically about SQL Server. I will not go into a lot of detail about installation here. See below for links to the geoserver docker image I didn’t install the geoserver I describe here but I can confirm that we have been successful in including Oracle as a plugin to our Geoserver installation and we are able to connect.

Official Geoserver Docker Image GITHUB

Below is a recent (Feb 2024) list of Docker parameters available at install including Oracle and SQL Server please seek advice on the use of Docker for installation if you are not familiar with it already.

GENERAL SETUP

Contact Information – General Setup
On the Welcome screen there is a line that reads For more information visit {organization} to change this from the initial meaningless variable setting go to Contact Information and fill out your organisation.

Difference between Save and Apply – General point

In geoserver admin there are a lot of pages where there is a Save and an Apply button. Most users initially at least find this confusing.

In GeoServer, the terms “Save” and “Apply” serve different purposes:

Save:
When you click “Save”, it permanently stores the changes you’ve made to the configuration. These changes are saved in the GeoServer configuration files. , “Save” ensures that your modifications persist even after restarting GeoServer or rebooting the system. It’s like committing your changes to a version control system—it makes them permanent.
Apply:
“Apply”, on the other hand, is more immediate. When you click “Apply”, it takes the current configuration (including any unsaved changes) and applies it dynamically. The applied changes take effect immediately without requiring a restart or permanent storage. Think of it as a temporary adjustment that affects the running instance of GeoServer but doesn’t alter the saved configuration. In summary, “Save” makes changes permanent, while “Apply” applies them immediately without saving them to the configuration files

The first thing you will want to do is determine if you can actually connect to your ORACLE database.

ORACLE required parameters for database connection. Before you continue make sure you have the following

ORACLE host name
Port ORACLE database (default 1521)
database name
user – case sensitive
password – case sensitive
Geoserver installed with Oracle extension

It is good idea to get your DBA to setup a read only user if you are connecting to a database that is part of a vendor supplied product as you really should not be editing the database through anything except the vendor’s UI and Geoserver can be setup to allow edit of data. Geoserver has powerful security configuration included but conflicting settings can arise that may alter the intended security levels making it safer to enforce read only least privilege at the database level.

ORACLE and Postgres – some SQL Differences
Please be aware Oracle databases are by default case sensitive but may convert SQL Strings to upper case when passed to Oracle. I believe this complies strictly with the SQL Standard.

Postgres classifies tables, columns views and other database objects as Identifiers. SQL Identifiers must begin with a letter. If possible avoid including special characters in the naming of identifiers because although postgres may support them special characters are not allowed in the SQL Standard so their use might render applications less portable. Additional identifiers and keyword must begin with a letter. Keywords are defined as items such as SELECT / UPDATE / CREATE etc.. The SQL Standard says that databases should not define a key word that contains a digit or starts or ends with an underscore so identifiers could be something like t001users should be safe against possible conflict with future extensions of the standard. In postgres the maximum identifier length is 63 bytes. Additionally Postgres also folds unquoted names to lower case but some postgres management UIs allow for the naming of identifiers with varied case.

UPSHOT for Geoserver (and anything else for that matter)

If you are having difficulty referencing tables or views in any enterprise database – experiment with the case sensitivity in the query. There might be some folding of object names when passed to the server.
This characteristic should be born in mind when using SQL Views in Geoserver.

ORACLE Store setup to get the most out of Geoserver
Login to Geoserver admin panel using your admin account

On the left hand side under Data hit Stores

Assuming you have successfully installed Geoserver with the Oracle extension you should be presented with the following dialog and in particular the items highlighted below.

Oracle NG uses a standard Oracle driver (ignore the other options this article will not explain them)

Select Oracle NG

Use the default settings for most things but IMPORTANTLY ensure Expose Primary Keys is ticked

NOTE from our brief testing we found that exposing primary keys seemed to be one factor that improved stability in our Oracle connection when dealing with QGIS.

QGIS WFS set up to improve Oracle Connection Stability
It is recommended that you have a copy of QGIS to test setup to things like WFS

When we first started displaying specifically the Oracle spatially enabled table in QGIS via WFS service we found that the layer would initially display but if we were to leave the project and go back in for a period of time the layer would not be visible. This seemed to be temporary and after an unpredictable amount of time the layer would reappear. The attribute table continued to be viewable but polygons were not visible.

We fixed this by ensuring Expose Primary Key (see Geoserver layer configuration) was ticked and within QGIS within the WFS Connection Configuration ensure within the WFS options section that the version is set to match the linked WFS Service. This might not be necessary in all QGIS versions.

LAYER Config
Useful background information (partly written by co-pilot)

Enabled and Advertised parameters – Layer Config
In GeoServer, the Enabled and Advertised checkboxes play distinct roles in layer configuration:

Enabled:
When a layer is enabled, it means that the layer is available for requests (such as WMS GetMap or WMS GetFeature). If a layer is not enabled, it won’t be accessible for any kind of request. However, it will still appear in the configuration (and in the REST config). Essentially, enabling a layer makes it operational and ready to serve data.
Advertised:
By default, a layer is advertised. An advertised layer is included in the GetCapabilities request and appears in the layer preview. However, if you uncheck the Advertised checkbox, the layer will still be available for data access requests (like WMS), but it won’t appear in any capabilities documents or previews. In other words, non-advertised layers remain functional but are not explicitly listed in service metadata. Remember, enabling a layer makes it operational, while advertising it determines whether it appears in service capabilities and previews

Name and Title parameters – Layer Config
Name:
The Name corresponds to the identifier used to reference the layer in WMS (Web Map Service) requests. It is primarily used for computer interaction and serves as an internal reference default name is usually the name of the table or the name of the referenced view. When creating a new layer for an already-published resource, the Name must be unique to avoid conflicts. Essentially, it’s the technical name associated with the layer.
Title:
The Title provides a human-readable description that briefly identifies the layer to clients. Unlike the Name, which is for computers, the Title is meant for humans to read. It helps users understand the purpose or content of the layer. For example, if you have a layer representing a map of the USA, you might set the Title to “This is a map of USA.” Remember, while the Name is essential for system functionality, the Title enhances user experience by providing meaningful context about the layer1

Security – the Name does not need to be the table or view name you are referencing. This string appears in the URL which may be viewable to the user – you may wish to obfuscate this by changing the name

i18n – Internalization – Layer Config
Internationalization (i18n):
GeoServer supports returning a GetCapabilities document (used for describing available services and layers) in various languages. The i18n functionality is available for the following services:
WMS 1.1 and 1.3
WFS 2.0
WCS 2.0
The i18n editor allows you to provide translations for the title and abstract of various components:
Layers configuration page
Layergroups configuration page
WMS, WFS, and WCS service configuration pages
Additionally, for Styles, there’s a separate i18n configuration (see i18N in SLD).By default, the i18n editor is disabled and can be enabled via the i18n checkbox.

GetCapabilities Document Language:
The language of the GetCapabilities document can be selected using the AcceptLanguages request parameter.GeoServer’s response varies based on the following rules:
Internationalized elements include titles, abstracts, and keywords.If a single language code is specified (e.g., AcceptLanguages=en), GeoServer tries to return content in that language.If multiple language codes are specified (e.g., AcceptLanguages=en,fr), GeoServer attempts to return content in one of the specified languages.

Coordinate Reference Systems – Declared SRS and SRS handling – Layer Config

If you are in the UK ensure that Declared SRS is set to 27700

SRS Handling
In GeoServer, the SRS handling setting plays a crucial role in how coordinate reference systems (CRS) are managed. Let’s explore its significance:

Declared SRS:
The Declared SRS specifies the coordinate system that GeoServer publishes to clients. It represents the CRS that clients should use when interacting with the layer. Essentially, it’s the officially declared CRS associated with the layer.
SRS Handling:
The SRS Handling option determines how GeoServer should handle projection transformations when the declared CRS and the native CRS of the data differ. Here are the possible values for SRS Handling:
Force declared (default): In this mode, GeoServer forces the declared SRS upon the data, overwriting the native CRS if necessary.
Other options (not mentioned in the snippet):
Reproject native to declared: GeoServer performs a reprojection from the native CRS to the declared CRS.
Reproject declared to native: GeoServer reprojects from the declared CRS to the native CRS.
None: No reprojection is performed; the data remains in its native CRS.
In summary, the SRS handling setting ensures that data is presented consistently to clients, regardless of the underlying native CRS. It’s a critical aspect of maintaining accurate spatial information in GeoServer1.

Bounding Box recommendations – Layer Config
Scotland

Min X = 010000
Min Y = 533000

Max X = 464000
Max Y = 1215000

West Lothian

Min X = 287000
Min Y = 655000

Max X = 314000
Max Y = 678000

Workspace setup and visibility in WFS
Workspaces can allow for editing and not editing of information although to date I seem to be struggling to isolate layers and workspaces to allow them to be edited or not edited. One thing that is important in our present system is to Enable the settings in the Basic info – without this the layers within the workspace were not showing up in the WFS

Restrictions on Layers : Important Note

Important – Workspace Security settings override Layer Security Settings

If you need to have different layer security settings within one Workspace you will need to turn off all Workspace security settings e.g see below.

Geoserver Installation (Configuration for SQL Server on Windows)

Once you have your data in an Enterprise Grade database sharing that information becomes important. Some vendors, in particular Digital mapping SAAS vendors want you to transfer your data into their clouds but for a lot of authorities that is categorically impossible for large segments of their data. For example about 3/4 of UK planning authorities run on software built by a company called IDOX Group plc which holds its data in Oracle this cannot be moved. For Geographical data here’s where Geoserver comes in.

Geoserver Official Website

From the website
GeoServer is an open source server for sharing geospatial data.
Designed for interoperability, it publishes data from any major spatial data source using open standards.

GeoServer implements industry standard OGC protocols such as Web Feature Service (WFS), Web Map Service (WMS), and Web Coverage Service (WCS). Additional formats and publication options are available as extensions including Web Processing Service (WPS), and Web Map Tile Service (WMTS).

Another way of putting it Geoserver allows programs that display and manipulate geographic information to display and edit that data while keeping it safe and secure and located in enterprise databases. It can connect to SQL Server / Postgresql and I am told Oracle which are the main relational databases used by Local authorities today so once setup and configured it could be used to compare and contrast information in one location from variable datastores. Datastores that for historical reasons cannot be moved. Here I concentrate on SQL Server because we have a particular issue with new systems needing to connect to SQL Server but it would be great if we could get it working with Oracle as well.

The following are my notes on installation and configuration of Geoserver on Windows linking to SQL Server and in this case a remote(not on the same computer) SQL Azure instance I believe any remote SQL Server instance would be similar. This post is a detailed explanation of the installation of Geoserver locally on a laptop but I believe installation would be exactly the same on a Windows Server machine albeit additional steps would be required to publish to the web either internally or externally.

Its all configuration so remember your host names / IP numbers / ports / usernames / passwords / database, table and column names

To start this tutorial please ensure the following resources are available;

SSMS – installation of Sql Server Management Studio

SQL Azure instance with privileges to run SQL. Please note you will need to know the port number of your SQL server instance and have a Username and Password for your user.

Necessary privileges to set the firewall on the database which you will link to Geoserver –

QGIS – installation – If you don’t have a SQL Server instance with geodatabase already that you can use you will probably need this to add at least one record to a geometry enabled table I plan to expand my notes on this but at present you might be able to skip this

Geoserver 2.24.1 – installation files

Enough privileges on the computer you are installing geoserver to download installation files and move files into a library subdirectory of the c:/program file/geoserver directory

–

WorkFlow Synopsis Overview
1)Download and install Java runtime engine (as above importantly here I use 11)
2)Download and install Geoserver (2.24.1 in this case)
3)Test that the Geoserver Admin dashboard is up and working
4)Download and configure Geoserver Extension for SQL Server library (SQL Azure example)
5)Good idea to setup a login specifically to your SQL Azure instance with least privilege – Your SQL Server instance with geodatabases may already have suitable logins. So steps 5 and 6 can be skipped
6)Good idea to create an example table with Geodatabase in SQL Azure – will be used as a test connection table – NOTE if you already have a SQL Server with geodatabases you could use that instead.
7)White list IP within the SQL Azure instance firewall rules to Geoserver computer
8)Opening Geoserver and setting the ‘Store’ to reference your SQL Azure database using the login setup in step 5.
9)Adding a new layer from the Store made in point 8 and seeing if you can Preview the layer – Ideally you should have a table that has some features in it. Setting the WMS and WFS up in Geoserver with sufficient rights to allow editing (if WFS setup)
10)I don’t explain it in my post below but the next step would be to test in QGIS to see if you can setup a project in QGIS which can pull from Geoserver against a basemap and you check that the Polygons are actually where you want them to be.

Workflow Detailed
1)Download the install Java Runtime via JDK

There are some complications here
Initially I tried to use the Java Runtime engine 17 and went through the complete Geoserver installation but on testing I was getting a Java error when trying to view layers. After a short google I came across a stack overflow thread that indicated that geoserver support for JDE 17 was experimental and I should use Java Runtime engine 11. Deinstallation of 17 and installation of 11 solved this.

Additionally there is the issue of where you obtain the Java Runtime installation from – there is the Adoptium location and then there is Oracle location. Oracle are making moves to charge for use of their Java Runtime engine so it is important that you use Adoptium resource to reduce costs.

Later I will list the url to the JDK download for Java Runtime engine but it is important to realise that Adoptium is a safe source, well supported and noted as open source.

https://adoptium.net/en-GB/members/

Google Search for Adoptium
https://adoptium.net/en-GB/

Navigate to other platforms and versions and target windows 64 bit JDK and 11 LTS version and download the .msi.

After a short delay you will be shown the msi which will be in your downloads folder

Double click on it to install.

Now the only thing that is tricky here is to setup the java home directory as follows

Video Installing Adoptium JDK on Windows

In particular note this section

2) Download and install Geoserver

Go to the official Geoserver web page and follow the links to download the windows installer

Here I use the windows version of Geoserver version 2.41.1 obtained January 2024 from the following url
Geoserver Official Website

See download button and then go to Windows Installer

I chose the left option and then I chose the Windows Installer option

This will download Geoserver-2.24.1-winsetup.exe to your download directory and you can then start the geoserver installation process

Next open up the executable and follow the instructions

Next agree the licence

Next we reference the java library installed in the previous step – if you have set the java_home variable correctly it should automatically find JRE 11 for you and it will place it within the path reference below. I don’t have a screenshot of the JRE 11 reference here as on my first installation I referenced the Oracle 17 JRE – (note I went back de-installed geoserver installed JRE 11 and then reinstalled geoserver to counter the proprietary Oracle runtime library and importantly to fix the issue that I was facing of geoserver not being compatible with JRE 17

The rest of the install from here is standard for a windows install

Next setup the default admin password – defaults are admin / geoserver

Set up port geoserver runs on – default is 8080

Next choose the execution type I like to install geoserver as a service

Confirm your preferences and then trigger install

Now you should see geoserver on your local machine as a service which you will need to ensure is running to access properly

3) Test that Geoserver Admin dashboard is up and working at least locally

If you have used the same settings as me open a browser and navigate to the following url

https://localhost:8080/geoserver/web

At which point you should see something similar to the following

4) Download and configure Geoserver Extension for SQL Server

At install Geoserver comes with the ability to connect to Postgres but NOT SQL Server so we must install/configure plugin extension to enable Geoserver SQL Server connections.

Here we go back to the geoserver.org website and go to download but this time instead of choosing the windows installer we look to the Extensions section and choose SQL Server

This should download geoserver-sqlserver extension plugin

Next copy all files with the jar suffix into the following directory

C/program files/GeoServer/webapps/geoserver/WEb-Inf/Lib

Next restart the geoserver and go back to the local host and sign in

http://localhost:8080/geoserver/web/?0

We are now very close to linking to SQL Server prior to that we must whitelist our geoserver so that your SQL Server instances will accept connections from your geoserver. Steps 5 and 6 are more about creating a user and ensuring you have a geodatabase with a georeferenced table skip to 7 if you want to read about white listing in SQL Azure or you have an alternative database that you could use with requirements already set up.

5) Next its a good idea to create a user with least privilege this will be used to set up the link so go to SSMS

Least privilege User Login Setup SQL Server

Here allow the amount of access you wish users to have remembering the principles of least privilege

6) Lets create an example table with geometry that we will be connecting
When I first tried connecting to SQL Server (SQL Azure in my case) I didn’t have any georeferenced tables so I created one and added a few records. If you have a database already with tables with geometry or geography you might not need to do this step.

So open SSMS and navigate to your database and use the following TSQL to create a table here I call it t064

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[t064](
[PKID] [int] IDENTITY(1,1) NOT NULL,
[geomcol] [geometry] NULL,
[sitename] [varchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[PKID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

This is a really simple table with three columns and you might want to link to it through QGIS and just create a few records – I would try and use a default projection of 27700 if you are UK based. I might come back with an example table with records..

If you want to link to this table for testing then you should try and input at least one record here. I added records using QGIS and I hope to come back to this post and update it to be more specific

7) White List the IP within your SQL Server or SQL Azure instance.
Setting IP White List in SQL Azure

8) Opening Geoserver and setting the ‘Store’ to reference your SQL Azure database using the login setup in step 5

Navigate to the local host url for Geoserver namely

http://localhost:8080/geoserver/web/?0

Go to store and then you should see the following page

Hit the Add New Store green plus button in the top left corner you will be presented with the following screen

Fill out as many details as you can

For SQL Azure this is likely to be similar to the following

Host = namevariable.database.windows.net (normally unique to instance in SQL Azure)
Port = 1433 (sql azure default)
Schema = dbo (sql azure default is dbo but your database maybe bespoke)
User = uservariable (remember least privilege is a good idea)
Passwd = passwordvariable (should be unique to your database)

Save and Apply and then your new connection should appear in your store

Here’s an example…. (see top line)

For the purposes of this tutorial I setup a login with db_owner rights to the database LandRegisterAzure your mileage will vary.

9) Next we add the layer which references the SQL Server

Select Layers within the Data section of the menu (usually to the left of the geoserver dashboard) then hit Add a new layer

You should get the following windows

From the drop down select the store which references your SQL server. This will reveal all the tables and views in the database and you scroll through them to the table or view you wish to publish and in the column marked Action will be publish you can hit the Publish highlighted text (I’ve done that here already so under Action our table is marked as Publish again and there is a tick in the column titled Published.

We can now quickly test to see if things look like they are working by going to Layer Preview.

Look to the left hand side and select Layer Preview

Scroll down through the list and identify the layer that you have just added. I now select the open layers option

A new browser tab will open and if you have successfully configured the SQL Server you should be presented with your layer – without any background

10) I don’t go into it here but next I will try and write up my notes on QGIS and Geoserver setup – On my laptop I managed to set up a WFS to my SQL Azure instance through Geoserver where the layer was added to a project and I was able to edit information in my SQL Azure database through QGIS and were displayed correctly against a basemap in the right place!!

–/END/–

Geoserver – Setting up Users / Roles / Workspaces and Layers

Create Users
Firstly ensure you are logged in as administrator ONLY the system administrator can set up users and roles and the default geoserver username/password of the administrator is admin/geoserver

The sa login has additional menus as here

Go to the Security menu on left towards the bottom and find line marked – Users/Groups/Roles

Go to the Roles tab

Look to the green circle with white cross and add new role and complete as appropriate

Note once you have created the role you will not be able to edit the name of that role so you have to delete the role and then create a role with a different name if you don’t want the old role name to continue.

Next we create the users

Go to the Users/Groups tab

Look to the green circle with white cross and add new user and within the Roles taken section ensure you move the role you created from the left box to the right see grey above.