Last active
June 26, 2018 16:53
-
-
Save leodc/d7f9228884f87bf803d419ad65d3fbf2 to your computer and use it in GitHub Desktop.
Help script to publish postgis tables to geoserver
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment