Wednesday, December 12, 2012

Geoserver batch create layers from Postgis

Goal:

Goal is to automatically slurp up all tables in the Postgresql Postgis database in the schema "geodata" and create the geoserver layers.  Then we want to assign a SLD to the layer. Finally create a layer group.

Prerequisites:

  1. Postgresql PostGIS database exists
  2. Geoserver Workspace exists (demo_wrkspc)
  3. Geoserver Datastore exists (temp_datastore)
  4. Text file list of all table names (tablelist.txt)
  5. curl is installed
  6. SLD style is created and located in a workspace
  7. OS: ran on CentOS 6.3

 Code:

my bash script saved as upload_db.sh
while read p; do
  curl -u username:password -v -XPOST -H 'Content-Type:text/xml' -d '<featureType><name>'$p'</name></featureType>' http://localhost:8080/geoserver/rest/workspaces/demo_wrkspc/datastores/temp_datastore/featuretypes;
done < tablelist.txt

tablelist.txt is a list of the table names in my database here are 3 tables i have in my list.  (originally i had 75 tables)

water_lines
road_lines
grass_polys 

 Next asign an SLD to a layer

  • nameOfWorkspace  = the name of your workspace
  • nameOfSLD = the name of the SLD you have created
  • defaultStyle true  will check the check box style is enabled
  • advertised false will unselect the check box advertised
  • nameOfWorkspace:NameOfLayer   this is layer you want to assign the sld to  
curl -u username:password -v -XPUT -H 'Content-Type:text/xml' -d '<layer><defaultStyle><workspace>nameOfworkspace</workspace><name>nameOfSLD</name></defaultStyle><enabled>true</enabled><advertised>false</advertised></layer>' http://localhost:8080/geoserver/rest/layers/nameOfWorkspace:NameOfLayer

Bonus create a layer group
  • @ug01.xml is an xml file looks like this
<layerGroup>
  <name>groupLayerName</name>
  <workspace>nameOfWorkspace</workspace>
  <layers>
    <layer>layerName1</layer>
    <layer>
layerName2</layer>
    <layer>
layerName3</layer>
    <layer>
layerName4</layer></layerGroup> 

curl -u username:password -XPOST -d @ug01.xml -H 'Content-type: text/xml' http://localhost:8080/geoserver/rest/layergroups

Reference:

  1. Blog OpenGeo 
  2. Geoserver Rest Doc

3 comments:

  1. Hello!

    I have a PostGIS database with several tables, and GeoServer 2.3.0 under WinXP.
    I need to publish to geoserver the polygons from my tables. But the admin of geoserver only let me one layer at time. How can I make to publish all the datas to layers?

    Thanks in advance.

    Daniel

    ReplyDelete
    Replies
    1. Hi Daniel,
      sorry but the "Admin" page cannot do batch adding of all postgis geo tables. You will need to use the Geoserver API as I described above with the tools above to do it. Sorry but this method described above is under Ubuntu or CentOS Linux.

      You would need to write the "bash" script above into a windows .bat (batch) file and install "Curl" for windows.


      cheers
      Michael

      Delete
    2. Something like this (this has only been tested on my pc.. so watch out!):

      @echo off
      REM -----------------------------------------------------
      REM Created by S.S. Brandsma (sbrandsma - at - gmail)
      REM This script can be used to import all shapefiles in current directory and subdirectories into a postgresql database
      REM A new database will be created that is specified as the first parameter. The script will first attemt to drop this database!
      REM Each shapefile will be a new table in the new database and will automatically be published by using the Geoserver rest API
      REM
      REM This script can really harm your databases if you don't know what you're doing.
      REM Please only use when you fully understand the script!
      REM Disclaimer: I'm not responsible for any damage caused by this script
      REM
      REM -----------------------------------------------------

      IF "%1"=="" GOTO usage

      REM first parameter specified is database to be created
      set pgsql_binpath=D:\Program Files\PostgreSQL\9.1\bin

      REM You can get curl for win here: http://curl.haxx.se/download.html
      set curl_binpath=D:\curl

      REM -----------------------------------------------------
      REM Geoserver STUFF
      REM -----------------------------------------------------
      set geoserver_host=http://localhost:8080
      set geoserver_workspace=test
      set geoserver_database=%1
      set geoserver_username=admin
      set geoserver_password=geoserver

      REM -----------------------------------------------------
      REM POSTGRESQL STUFF
      REM -----------------------------------------------------
      set pgsql_username=postgres
      set pgsql_password=postgres
      set pgsql_port=5432
      set pgsql_host=localhost
      set pgsql_srid=4326

      REM template_postgis_20 for PostGis 2.0
      REM template_postgis for PostGis 1.6 or older (?)
      set pgsql_template=template_postgis

      REM Usually you don't need to change the following executables
      set shp2pgsql="%pgsql_binpath%\shp2pgsql.exe"
      set pgsql="%pgsql_binpath%\psql.exe"
      set createdb="%pgsql_binpath%\createdb.exe"
      set dropdb="%pgsql_binpath%\dropdb.exe"
      REM -----------------------------------------------------

      SET /P ANSWER=This script will delete [%1] postgresql database but you'll have to delete the geoserver datastore yourselves!! Continue (y/n)?
      if /i {%ANSWER%}=={y} (goto :yes)
      if /i {%ANSWER%}=={yes} (goto :yes)
      goto :no

      :no
      echo Ok. Nothing is deleted.
      goto :exit

      :yes
      echo Deleting %1 database

      REM drop database
      %dropdb% --host %pgsql_host% --username %pgsql_username% --port %pgsql_port% %1

      REM create new database
      %createdb% --host %pgsql_host% --username %pgsql_username% --port %pgsql_port% --template %pgsql_template% %1

      for /f "delims=" %%f IN ('dir /b /s *.shp') do %shp2pgsql% -D -W "latin1" -I -s %pgsql_srid% "%%f" %1_%%~nf > %%~nf.sql
      for /f "delims=" %%f IN ('dir /b /s *.sql') do %pgsql% -q -f "%%f" -p %pgsql_port% --host=%pgsql_host% --username=%pgsql_username% -d %1

      REM create geoserver datastore
      %curl_binpath%\curl -u %geoserver_username%:%geoserver_password% -v -XPOST -H Content-type:text/xml -d "%1localhost%pgsql_port%%1public%pgsql_username%%pgsql_password%postgis" %geoserver_host%/geoserver/rest/workspaces/%geoserver_workspace%/datastores

      REM publish all layers in geoserver
      for /f "delims=" %%f IN ('dir /b /s *.shp') do %curl_binpath%\curl -u %geoserver_username%:%geoserver_password% -v -XPOST -H Content-Type:text/xml -d "%1_%%~nf" %geoserver_host%/geoserver/rest/workspaces/%geoserver_workspace%/datastores/%1/featuretypes

      rem cleanup of sql files
      for /f "delims=" %%f IN ('dir /b /s *.sql') do del %%f
      goto done

      :done
      echo Done!
      goto :exit

      :usage
      echo Usage: %0 [database]
      echo The database specified will be created or overwritten!!

      :exit

      Delete