Help script to publish postgis tables to geoserver

addLayersToGeoserver.py

#!/usr/bin/python3

import os
import sys
import psycopg2
import subprocess
import urllib.request
import json

POSTGRES_DBNAME = os.getenv("POSTGRES_DBNAME")
POSTGRES_USER = os.getenv("POSTGRES_USER")
POSTGRES_HOST = os.getenv("POSTGRES_HOST")
POSTGRES_PASSWORD = os.getenv("POSTGRES_PASSWORD")
POSTGRES_PORT = os.getenv("POSTGRES_PORT")

GEOSERVER_DATASTORE = os.getenv("GEOSERVER_DATASTORE")
GEOSERVER_WORKSPACE = os.getenv("GEOSERVER_WORKSPACE")
GEOSERVER_REST = os.getenv("GEOSERVER_REST")
GEOSERVER_USER = os.getenv("GEOSERVER_USER")
GEOSERVER_PASSWORD = os.getenv("GEOSERVER_PASSWORD")

MAX_LAYERS = os.getenv("MAX_LAYERS")


CKAN_API = "https://datos.gob.mx/ckan-admin/api/3/action/package_show?id="
DGM_API = "https://api.datos.gob.mx/v1/resources?id="


def main():
    # create connection
    conn = initConnection()
    cursor = conn.cursor()


    # get postgis tables name
    cursor.execute( "select relname from  pg_stat_user_tables;" )
    tables = cursor.fetchall()



    boundingBoxQuery = "SELECT ST_XMin(ST_Extent(the_geom)) AS min_x,\
                    ST_XMax(ST_Extent(the_geom)) AS max_x,\
                    ST_YMin(ST_Extent(the_geom)) AS min_y,\
                    ST_YMax(ST_Extent(the_geom)) AS max_y FROM \"{}\""

    skip = ["spatial_ref_sys"]
    counter = 0
    for table in tables:
        layerName = table[0]

        # layer to process
        if layerName not in skip:
            print("Layer {count} - {layerName}... ".format(count=str(counter+1), layerName=layerName), end="", flush=True)

            # build api resource id
            resourceId = layerName.split("__")[0].replace("_","-")


            # get bounding bounds
            cursor.execute( boundingBoxQuery.format(layerName) )
            boundingBox = cursor.fetchall()[0]

            # validate bounding box
            if isValidLayer(boundingBox):

                # get DGM information
                with urllib.request.urlopen(DGM_API + resourceId) as dgm_response:
                    dgm_result = json.loads(dgm_response.read().decode("utf-8"))

                    if len(dgm_result["results"]) >= 1:
                        dgm_resource = dgm_result["results"][0]

                        if len(layerName.split("__")) > 1:
                            dgm_resource["name"] = dgm_resource["name"] + " - " + layerName.split("__")[1]

                        # get CKAN information
                        with urllib.request.urlopen(CKAN_API + dgm_resource["package-id"]) as ckan_response:
                            ckan_resource = json.loads(ckan_response.read().decode("utf-8"))["result"]

                            processFeatureType(layerName, dgm_resource, ckan_resource)
                            print("OK")
                            counter += 1

                    else:
                        print("No se encontro el recurso", json.dumps(dgm_result))
            else:
                print("Extension del layer no valido")


            if MAX_LAYERS is not None and int(MAX_LAYERS) <= counter:
                break


    print("Done, layers created: " + str(counter))


def processFeatureType(layerName, dgm_resource, ckan_resource):
    layerXml = "<featureType>";

    layerXml += "<name>{}</name>".format(dgm_resource["name"])

    if "description" in dgm_resource:
        layerXml += "<abstract>{}</abstract>".format(dgm_resource["description"])


    # define tags
    tags = []
    if "organization" in dgm_resource and dgm_resource["organization"] is not None:
        tags.append(dgm_resource["organization"])

    if "tags" in ckan_resource:
        for tag in ckan_resource["tags"]:
            if tag["state"] == "active":
                tags.append(tag["display_name"])

    # add tags to new layer
    if len(tags) > 0:
        layerXml += "<keywords>"
        for tag in tags:
            layerXml += "<string>{}</string>".format(tag)
        layerXml += "</keywords>"

    layerXml += "</featureType>"

    subprocess.call( "curl -v -u {user}:{password} -X POST -H \"Content-type: text/xml\" -d \"{layerXml}\" {geoserver_rest}/workspaces/{workspace}/datastores/{datastore}/featuretypes".format(user=GEOSERVER_USER, password=GEOSERVER_PASSWORD, layerXml=layerXml, workspace=GEOSERVER_WORKSPACE, datastore=GEOSERVER_DATASTORE, geoserver_rest=GEOSERVER_REST) , shell=True)





def initConnection():
    try:
        conn = psycopg2.connect(dbname=POSTGRES_DBNAME, user=POSTGRES_USER, host=POSTGRES_HOST, password=POSTGRES_PASSWORD, port=POSTGRES_PORT)
        return conn
    except:
        print("Error connecting to postgis.", True)
        sys.exit()


def isValidLayer(boundingBox):
    minx = boundingBox[0]
    maxx = boundingBox[1]
    miny = boundingBox[2]
    maxy = boundingBox[3]

    if minx < -180.0000 or minx > 180.0000:
        return False

    if maxx < -180.0000 or maxx > 180.0000:
        return False

    if miny < -90.0000 or miny > 90.0000:
        return False

    if maxy < -90.0000 or maxy > 90.0000:
        return False

    return True


main()