ArcSDE licensing

I was really curious about the ArcSDE licensing and tried to find some information in the internet about it. In my opinion it is very weird that the Enterprise GeoDB creation requires keycodes (Authorisation file) from ArcGIS Server  software and that there should be another way to do so. But after digging and surfing, there is not much information about that topic.

On this gis.stackexchange Question (answered on August of 2015) it is mentioned that:

“Esri incorporated ArcSDE into ArcGIS Server with the release of ArcGIS 9.2. In order to deploy ArcSDE technology as an enterprise geodatabase you must have, or be prepared to purchase, an ArcGIS Server license. Since the introduction of ArcGIS 10.0, the actual enterprise geodatabase installer lives within ArcPy, and can be invoked either as an ArcToolbox tool or directly from Python.”

Checkin on the Create Enterprise Geodatabase tool documentation for the authorization_file option, it is explained as:

“Provide the path and file name of the keycodes file that was created when you authorized ArcGIS Server. If you have not already done so, authorize ArcGIS Server to create this file.”

Also, in this GeoNet post (from 2011), it is mentioned that:

“You cannot purchase a license for ArcSDE. Since 9.2, the ArcSDE application server is a *part* of the Enterprise ArcGIS license.”

Finally, in this ESRI community post, it is explained on details what to do with the authorisation file (in case you miss it) and it notes that:

“NOTE: You must have authorized ArcGIS Server to create the keycodes file ahead of time!”

So, it is clear that there is no separate installation any more for ArcSDE and that this could be done via ArcGIS Desktop software (ArcMap or ArcGIS Pro). Nevertheless it is required to have ArcGIS Server licence in order to be able to create an Enterprise Geodatabase.

Theory & creation of GDB using PostgreSQL

ArcSDE

ArcSDE (Spatial Database Engine) is a server-software sub-system (produced and marketed by Esri) that aims to enable the usage of Relational Database Management Systems for spatial data. The spatial data may then be used as part of a geodatabase (wiki source).

While traditional RDBMS software keeps track of the tables and records contained in the database, ArcSDE pushes the relational model higher so that client software can manage geographic data – which comprise several tables – seamlessly. The user need have no awareness of nor dealings with the particulars of the RDBMS. The GIS environment routes all connections to the database through the ArcSDE middleware, which manages the storing and retrieval of data (wiki source).

Geodatabase

The geodatabase is a “container” used to hold a collection of datasets. There are three types:

1. File geodatabases—Stored as folders in a file system. Each dataset is held as a file that can scale up to 1 TB in size. The file geodatabase is recommended over personal geodatabases.
2. Personal geodatabases—All datasets are stored within a Microsoft Access data file, which is limited in size to 2 GB.
3. Enterprise geodatabases—Also known as multiuser geodatabases, they can be unlimited in size and numbers of users. Stored in a relational database using Oracle, Microsoft SQL Server, IBM DB2, IBM Informix, or PostgreSQL. This one requires the use of ArcSDE technology.

There is a comparison matrix on the ESRI documentation, where you can see all the details and differences between them.

Essential readings about the geodatabase can be found in this ESRI documentation. We will skip a big part of the documentation to go to the actual creation of the Enterprise geodatabase by connecting to the PostgreSQL.

Get started with geodatabases in PostgreSQL

The main tutorial to follow is this from ESRI documentation. It is very important to have a look on the PostgreSQL database requirements for ArcGIS 10.6.x in order to check the compatibility between the versions. In my case I do not have the minimum supported (PostgreSQL 10.3 & PostGIS 2.4) but I have PostgreSQL 10.5 & PostGIS 2.5. It says in the documentation “Newer minor versions are supported but are not certified”. So lets test it.

During creation of the geodatabase there is a more detailed description here. While running the “Create Enterprise Geodatabase” geoprocessing tool I entered the following options:

• DB platform: PostgreSQL
• Instance: localhost
• Database: myfirstgdb
• Tablespace Name: <empty>
• Authorization file: C:\Program Files\ESRI\License10.4\sysgen\keycodes

After finishing I can check in pgAdmin that the myfirstgdb geodatabase exists! The next step is to create a user to own data as described in ESRI documentation. Data stored in your geodatabase should be owned by users other than the sde user. Use the Create Database User geoprocessing tool to create a user in the PostgreSQL database cluster and a schema in your new database.

First step is to create a database connection to the newly created database. In the add Database connection menu select:

• DB platform: PostgreSQL
• Instance: localhost
• Database: myfirstgdb

Now run the Create Database User tool. Very straightforward procedure as well. Once again you can check this via pgAdmin.

Now that you have a user who can add data to your new geodatabase, connect to the database as that user. The easiest way to do that is to alter the connection file you created earlier. A small test is to try and import some data to “myfirstgdb”.

 

 

 

OpenStreetMap Tile Cache Server creation (using osm2pgsql) on Windows

As discussed previously there are several ways to import OSM data in a PostgreSQL database. In this post I will explain the most complete (in my opinion) way, which is using the osm2pgsql. In this way we can achieve creating an OpenStreetMap Tile Cache Server.

Details on the osm2pgsql can be found in the github page of the tool, in the wikipedia page of OSM and in this detailed usage description of the tool. Also, the procedure (for Linux Server) it is explained on this blog-post by Boundless and in this blog-post.

At the same time, I wanted to install the full solution in Windows so I started from scratch. The versions used in this tutorial are: Windows 7 SP1 64Bit / PostgreSQL 10.5 x86_64 / pgAdmin 4 v3.4 / PostGIS 2.5 / GeoServer 2.14

PostgreSQL installation

So, I started from scratch by downloading PostgreSQL from this site. Follow the installation as it goes. After installation you should allow the Stack Builder to open. During installation of PostGIS, I selected to create a PostGIS Sample (with default name).

OSM data download

Nest step was to download the OSM data from extract.bbbike.org. I selected a part of The Netherlands which includes Rotterdam, Den Haag, Delft e.t.c. Please note that data should be with the .pbf extension.

osm2pgsql installation

In order to use the osm2pgsql we have to download it from here. Unzip the file on your system. You should move the unzipped folder to a location where you will not move it later, because we need to add its location to the system path. Open the “Edit the system environment variables” and click on “environment variables”. At the bottom find the variable named “Path” and click “Edit…”. You must add the directory where osm2pgsql.exe is located to the Path variable. In order to check open Command Prompt and type osm2pgsql.

Prepare the Database

By accessing the pgAdmin and the Databases I created a DB based on the sample created while performing PostGIS installation. Just to be sure that the necessary extensions are available, create SQL script with the below:

CREATE EXTENSION postgis; 
CREATE EXTENSION postgis_topology; 
CREATE EXTENSION hstore;

Download style file

In order to have similar styles to the OSM in several websites we have to use style files. Download the mapzen_osm2pgsql.style from here. Save under the working folder.

Preparing and running the osm2pgsql

The osm2pgsql has several options which are explained quite well in the websites I already mentioned. The process of importing the data using the below command for the described region took 306 sec.

osm2pgsql -c -d <<DB name>> -U <<USER NAME>> -H localhost /Users/--<<style FILE PATH>>--.style/Users/--<<style FILE PATH>>--.pbf

In case the .pbf file is very big, there are a few options to be included, like cache, slim mode or flat-nodes. More details on the before mentioned osm2pgsql links.

In case the style file is not recognised, follow those steps:

• open the default style (lies in the osm2pgsql folder)
• copy (ONLY) the style details from the style file you want to use and copy it to the default.
• Save as different name and use this file

We can verify the import by checking in the pgAdmin the database/Schemas/public for the tables to be created. There should be tables like below:

• planet_osm_line
• planet_osm_point
• planet_osm_polygon
• planet_osm_roads
• spatial_ref_sys

Background data using shp2pgsql

The next step in creating our tile cache server is to load an ocean dataset to use in conjunction with the OSM data. Download the full Oceans Polygon (mercator) from this link. Alternatively to the full size shapefile you can use the simplified files. We can also add the land polygon. Unzip water-polygons-split-3957.zip file and then we have to use the shp2pgsql command. In order to use the shp2pgsql from any folder we have to set the environmental variables as explained above for the osm2pgsql. Then prepare and run the below shp2pgsql command on the Command Prompt window.

shp2pgsql -g geom -s 900913 -I -D ./water/simplified_water_polygons.shp ocean | psql -U <<USER NAME>> <<DB NAME>>
In case you use also land polygon:
shp2pgsql -g geom -s 900913 -I -D ./land/landpolygons.shp land | psql -U <<USER NAME>> <<DB NAME>>

Based on the shp2pgsql enter of the polygons we did, the EPSG will be 900913. You can check this by using the below on a DB script:

SELECT Find_SRID('public', 'ocean', 'geom');
--in case of land polygon use
SELECT Find_SRID('public', 'land', 'geom');

If in any case the shapefile you want to enter are in different coordinate system, then the below sql script has to run:

alter table <<TABLE NAME>> alter column geom type geometry(MULTILINESTRING,900913) using st_transform(geom,900913);

Download the createDBObjects.sql

Next step is to download and run the createDBobjects from this link. You can either run this from the command line or you can copy/paste the queries on the DB script and run it. After this process the tables in our database will be much more.

Install GeoServer and pre-configure

Download the GeoServer software from here and install using all the defaults. Using the GeoServer admin UI, create a workspace in GeoServer called osm with a http://osm.org as namespace.

Create a PostGIS datastore called openstreetmap that connects to the osm database in the database.

Download the SLD scripts

Next step is to create styles and connect them to layers. In order to save time we will use the styles and automatic process as proposed by Boundless. So, we will use the REST API to connect to GeoServer and create layers, symbology, and associate between the symbology and layers.

• Download the SLD scripts to your OSM data directory and unzip it.
• Edit the layergroup.xml to reflect your case. For example the admin0/admin1 layers are not there so, they can be removed.

Cygwin installation and SLD_create.sh run

The file provided by Boundless to import the styles, is an .sh file which runs on Linux machines and MacOS with minor changes. But not in Windows. In a future post I will convert this .sh file in a python file so that it will be usable in any format.

In order to run the .sh file we need to install the Cygwin software. Download here, install & run Cygwin. Navigate to your working folder (sld folder) and check if you can locate the SLD_create.sh file. This file will create the styles and layers in GeoServer, associate the style to the corresponding layer, and then create a group layer that is the base map. You can run the SLD_create.sh using:

bash SLD_create.sh

The SLD-create file includes Curl commands. If Curl is required, you can download curl on this link. In order to use curl from any folder we have to set the environmental variables as explained above for the osm2pgsql.

Once the SLD create script has run successfully you will see the layers and styles in your GeoServer instance. To see the basemap, go to layer preview and find the OSM group layer. It should look similar like below.

 

Overview of the region

More details while zooming

Delft markt in close detail

GeoServer styles (.sld) import troubleshooting

Trying to import .sld files on the GeoServer doesn’t always work. There are several issues that can occur and I will try to detail here the ones that I am facing more often (like in the previous post).

QGIS-GeoServer .sld version

As it is detailed on the GeoServer documentation  the .sld version that it is implemented on the GeoServer is the 1.0.0 while QGIS exports the .sld in 1.1.0. This might cause problems while importing the styles to GeoServer. The solution is to convert to the correct version as explained very well on this gis.stackexchange post. Just use the python script and the .xsl file to do the convertion and you are ready.

type IN single field issue

While on QGIS and working with the Rule-based styles of “roads” layer, I was trying to realise why is this layer/style not imported. The problem was caused by the styling fields where the expression was like type IN (‘motorway’). So, when the IN () includes only one value then GeoServer rejects it. The solution is very simple: you can add in the expression another (irrelevant) value. In my case (as seen bellow) I just added my name. 🙂

Unacceptable value

Fixed value

<se:Description> tag not acceptable

This was quite an obvious issue. While exporting the style from QGIS I was getting an error about the Description tag within the style file. I simply removed all the (unnecessary) Description fields from the file.

After fixing those issue I was able to import the .sld file in the GeoServer and use it on the roads layer. Obviously one can face much more issues but based on the feedback (errors) that GeoServer returns, fixes are always possible in order to achieve the desired result.

Add the OSM data to the GeoServer (including styles)

Setting the styles in QGIS is only the first step if you require to create a GIS web-app. The hard part is to deploy the styles on the GeoServer which can be very tricky.

There are several ways to add the PostGIS data to the GeoServer as layer but I will use the manual way as explained in this previous post. So, add Workspace, add store (if not already there) and finally add layers.

Layers in GeoServer

In order to upload the styles to the GeoServer, I used the GeoServer Explorer QGIS plug-in. This is a plug-in which helps to interact between GeoServer and QGIS.

GeoServer Explorer extension

In the GeoServer Explorer Panel click on Styles and then click the plus sign (+) on the top. Through the dialog you can add any style that has been created in the QGIS environment.

Importing Styles

Not always successfully though. I will deal with styles importing troubleshooting in a next post. Next step is to connect each layer to the appropriate style. This is done easily in the Publishing tab of the Edit Layer menu. On WMS settings set the Default style on the style you just imported.

In my case I created a simple Leaflet script to view the differences of OSM and on top the Delft:buildings layer styled via GeoServer. Find bellow a comparison. The first one is a simple OSM plot, while the second one has on top the buildings layer (with opacity 0.35 set on leaflet as shown bellow).

OSM-Delft

OSM-Delft with GeoServer WMS

 

L.tileLayer.wms("http://localhost:8080/geoserver/Delft/wms", {
    layers: 'Delft:Delft-buildings',
    format: 'image/png',
    version: '1.1.0',
    transparent: true,
    opacity: 0.35,
    attribution: "Geoserver Example",
    tiled: 'true'      
}).addTo(map);

 

Importing OSM data in PostGIS from shapefile & apply OSM styles

On the previous post we added multiple shapefiles to PostgreSQL with a python script and the use of the shp2pgsql. Now we will take it one step further in order to be able to have open street map (OSM) data in the PostgreSQL database. There are several ways and file formats to do so, but I selected the shapefile because is one of the most commonly used GIS file type.

First step to do was to download the OSM data for my area of interest. I downloaded the OSM data for Delft, NL from the bbbike.org. You can also use the geofabrik.de. It is a straightforward process. All you need to do is to define the bounding box polygon and then the file is created. Download/Save/Unzip and you are ready.

Use the same script from this previous post to add all the layers in the database. Based on the size of Delft the, import took less than 10sec.

Now that the data are in the db import them in QGIS as indicated in this previous post.

There are several OSM styles to be used in QGIS available in several sites. Nevertheless it is really important to use the ones that fit the imported data type in the database. In my case I downloaded and used the styles (QML) from this site. It contains QGIS stylesheets (QML) for use with OpenStreetMap (OSM) data in ESRI Shapefile format. List of styles can be found also wiki.openstreetmap.org.

Import style to QGIS

 

Please note the recommended layer order.

For background polygons you can use (as suggested) the OSM land-polygons and the OSM water-polygons. Styles for those are included in the styles package.

After importing the result looks amazing! Of course there are some changes to happen in order to reach the OSM style precisely. But this is up to each user. For example I didn’t like the given background colour of the land polygons and I changed it to the one used in the openstreetmap.org which is the #b8d9f7 and not the #dddde8 originally used. You can check this change in the last image bellow.

Delft OSM overview

Delft detailed overview

Amended bgr colour

Add multiple shapefiles to PostgreSQL with a python script

In this previous post we added a single shape file using command line and the shp2pgsql. We noted also that in this way is easy to automate this procedure and do this for a batch of files.

In this post we will create a .py script to import a set of shapefiles, saved in different sub-folders, into the PostgreSQL by creating new tables. Those new tables will be named based on the shapefile name.

So, the script:

1. should be able to search through a folder (and subfolders) and find all the shapefiles.
2. Use the shapefile names and paths to create the shp2pgsql command
3. execute the command

Lets go step by step:

In order to search through a folder the script need to know the path and the filetype to search for. Those are the variables defined in the beginning of the script

root = "/Users/.....<<ROOT PATH>>...../Data
pattern = "*.shp"

The search loop is using the os.walk module to go through folders & subfolders to look for the files with the pattern string. Then the paths of the files are saved in a list. The names to be used as database table names are created through the loop, according to the shapefile names.

#walk loop to find the pattern files
for path, subdirs, files in os.walk(root):
  for name in files:
    if fnmatch(name, pattern):
      #save the path name of the selected file to ff
      ff = os.path.join(path, name)
      #create a name variable to be used for table name in DB
      tablename = name[:-4]

Finally the command is created and executed using the subprocess module. Note that the .shp file and the table (to be created) names are the variables we defined earlier.

cmd = "shp2pgsql -I -s 2100 {0} <<SCHEMA NAME>>.{1}| psql -h localhost -d <<DB NAME>> -U <<DB USERNAME>>".format(ff, tablename)
subprocess.call(cmd, shell=True)

This is it. In my case I used data from the Athens municipality from the data.gov.gr.

Link to code

Result in QGIS

Data in PostgreSQL

Add shapefile data to PostgreSQL using shp2pgsql

In a previous post, I described which is the easiest way to add .shp data to the PostgreSQL database. Nevertheless, the most appropriate way to perform this task is using command line and the shp2pgsql. This is due to the fact that in this way it is easy to automate this procedure and do this for a batch of files.

There are numerous links who can guide you how to this (ex. link1, link2) and there is this very useful cheat-sheet.

So, in my case, I used the same shapefile as previously and I wanted to add it in the same database (postgis1) and a schema I have created previously as well (grdata) but in a table to created while entering the data, under the name PerfPP.

Since the database is running locally the host used is the:localhost and the username is the same as the PostgreSQL. Before executing, the user has to navigate to the folder of the .shp file. Another way is to give the path with the shapefile name instead of just the shapefile name.

shp2pgsql -I -s 4326 GreekPerfectures.shp grdata.PerfPP | psql -h localhost -d postgis1 -U <<USERNAME>>

This is really easy and way faster but not everyone is flexible on executing such commands.

P.S. Please note that the schema names should not have any capital letters as this will make the shp2pgsql to fail

Connect PostgreSQL with GisServer

In previous post we covered the topics of how to install and use PostgreSQL, Geoserver and Leaflet. Now it is time to combine those components. To do so we will have to connect the database to the server.

The first step is to create the workspace that we are going to use on GeoServer. Simply on the left pane of the browser UI, go to Data category and click on Workspaces. Click to add new workspace. In my case is the 

Then click on the Stores category on the left pane and the click Add new Store to connect the DB to the server. Essentially we connect a schema of the database to the Server. Select the PostGIS option and then define the details and the credentials. In my case I selected the newly created workspace and the schema GreeceData of the database.

As soon as you are done with this step you will be prompt to the New layer screen. If not, just click on the Layers button of the left pane. If everything went well so far, the layer should already be there for you to select it. In my case this is Perfectures (misspelled of course…).

Once again define the required details. I can only comment here the Bounding Boxes to be set. Click on the “Compute from data” button and you will get the correct values. Basically you are done. The result can be checked on the Layer Preview selection of the left pane. But I prefer to use the Leaflet code created on this previous post. Just amend the code of the fetched layer and you are ready to go! Your first Full Stack Web GIS solution!

Add shapefile data to PostgreSQL

There are several ways to add data to PostgreSQL. For now I will focus to the easier way. The one that should a GIS dummy work it out. First to do so is to install QGIS (if not already there). Use the QGIS download page and follow the steps of the installation. It is very straightforward.

Before doing anything else you got to install the DB manager plugin which you will use later on. Navigate to Plugins/Manage and Install plugins. Search for DB manager and install it. Done.

First step is to add the shapefile data to QGIS (in my case GreekPerfectures.shp). Open QGIS and add the shapefile to the environment. Second step is to create the PostGIS connection into QGIS. To do so click the PostGIS icon from the Manage layers toolbar. Click on new and enter the PostGIS database which you want to use.

Test the connection and you are ready. Press OK and then press connect. Before adding the data make sure that you have a new schema for importing the data. I personally almost never use the public schema. You can create a new one in the pgAdmin by navigating to the database and right-click the Schemas/Create/Schema.

Close the Add PostGIS layers menu and open the DB Manger. You should be able to view the connected database and the schema you created (in my case GreeceData). Click on the import layer/file button. Select the shapefile you have imported (in my case GreekPerfectures.shp).

You are done! You can check the imported data on the pgAdmin or even better view them in the QGIS. Open again the Add PostGIS layers menu. Connect and select the added data. Finally click Add and close the window. You should be able to view the data fetched from the Database directly.