def insert_raster(self, path=None, srid=4326): """Import Raster into Postgis Table Uses raster2pgsql -I -C -s <SRID> <PATH> <SCHEMA>.<DBTABLE> | psql -d <DATABASE> The sql processed by raster2pgsql is run as psql -U postgres -d <gisdb> -f <elev>.sql """ if not path: path = Engine.format_data_dir(self) raster_sql = "raster2pgsql -M -d -I -s {SRID} \"{path}\" -F -t 100x100 {SCHEMA_DBTABLE}".format( SRID=srid, path=os.path.normpath(path), SCHEMA_DBTABLE=self.table_name()) cmd_string = """ | psql -U {USER} -d {DATABASE} --port {PORT} --host {HOST}""".format( USER=self.opts["user"], DATABASE=self.opts["database"], PORT=self.opts["port"], HOST=self.opts["host"]) cmd_stmt = raster_sql + cmd_string if self.debug: print(cmd_stmt) subprocess.call(cmd_stmt, shell=True, stdout=subprocess.PIPE)
def insert_raster(self, path=None, srid=4326): """Import Raster into Postgis Table Uses raster2pgsql -Y -M -d -I -s <SRID> <PATH> <SCHEMA>.<DBTABLE> | psql -d <DATABASE> The sql processed by raster2pgsql is run as psql -U postgres -d <gisdb> -f <elev>.sql -Y uses COPY to insert data, -M VACUUM table, -d Drops the table, recreates insert raster data """ if not path: path = Engine.format_data_dir(self) raster_sql = "raster2pgsql -Y -M -d -I -s {SRID} \"{path}\" -F -t 100x100 {SCHEMA_DBTABLE}".format( SRID=srid, path=os.path.normpath(path), SCHEMA_DBTABLE=self.table_name()) cmd_string = """ | psql -U {USER} -d {DATABASE} --port {PORT} --host {HOST} > {nul_dev} """.format( USER=self.opts["user"], DATABASE=self.opts["database"], PORT=self.opts["port"], HOST=self.opts["host"], nul_dev=os.devnull) cmd_stmt = raster_sql + cmd_string if self.debug: print(cmd_stmt) Engine.register_tables(self) try: subprocess.call(cmd_stmt, shell=True) except BaseException as e: pass
def insert_vector(self, path=None, srid=4326): """Import Vector into Postgis Table -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION fuzzystrmatch; -- Enable US Tiger Geocoder CREATE EXTENSION postgis_tiger_geocoder; Uses shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <SCHEMA>.<DBTABLE> | psql -U postgres -d <DBNAME>> The sql processed by shp2pgsql is run as psql -U postgres -d <DBNAME>> """ if not path: path = Engine.format_data_dir(self) vector_sql = "shp2pgsql -I -s {SRID} {path} {SCHEMA_DBTABLE}".format( SRID=srid, path=path, SCHEMA_DBTABLE=self.table_name()) cmd_string = """ | psql -U {USER} -d {DATABASE}""".format( USER=self.opts["user"], DATABASE=self.opts["database"]) os.system(vector_sql + cmd_string)
def insert_vector(self, path=None, srid=4326): """Import Vector into Postgis Table -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION fuzzystrmatch; -- Enable US Tiger Geocoder CREATE EXTENSION postgis_tiger_geocoder; Uses shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <SCHEMA>.<DBTABLE> | psql -U postgres -d <DBNAME>> The sql processed by shp2pgsql is run as psql -U postgres -d <DBNAME>> shp2pgsql -c -D -s 4269 -i -I """ if not path: path = Engine.format_data_dir(self) vector_sql = ("shp2pgsql -d -I -W \"{encd}\" -s {SRID}" " \"{path}\" \"{SCHEMA_DBTABLE}\"".format( encd=self.encoding, SRID=srid, path=os.path.normpath(path), SCHEMA_DBTABLE=self.table_name(), )) cmd_string = """ | psql -U {USER} -d {DATABASE} --port {PORT} --host {HOST} > {nul_dev} """.format( USER=self.opts["user"], DATABASE=self.opts["database"], PORT=self.opts["port"], HOST=self.opts["host"], nul_dev=os.devnull, ) cmd_stmt = vector_sql + cmd_string if self.debug: print(cmd_stmt) Engine.register_tables(self) try: subprocess.call(cmd_stmt, shell=True) except BaseException: pass
def insert_raster(self, path=None, srid=4326): """Import Raster into Postgis Table Uses raster2pgsql -I -C -s <SRID> <PATH> <SCHEMA>.<DBTABLE> | psql -d <DATABASE> The sql processed by raster2pgsql is run as psql -U postgres -d <gisdb> -f <elev>.sql """ if not path: path = Engine.format_data_dir(self) raster_sql = "raster2pgsql -M -d -I -s {SRID} {path} -F -t 100x100 {SCHEMA_DBTABLE}".format( SRID=srid, path=path, SCHEMA_DBTABLE=self.table_name()) cmd_string = """ | psql -U {USER} -d {DATABASE}""".format( USER=self.opts["user"], DATABASE=self.opts["database"]) os.system(raster_sql + cmd_string)
def insert_vector(self, path=None, srid=4326): """Import Vector into Postgis Table -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION fuzzystrmatch; -- Enable US Tiger Geocoder CREATE EXTENSION postgis_tiger_geocoder; Uses shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <SCHEMA>.<DBTABLE> | psql -U postgres -d <DBNAME>> The sql processed by shp2pgsql is run as psql -U postgres -d <DBNAME>> """ if not path: path = Engine.format_data_dir(self) vector_sql = "shp2pgsql -d -I -s {SRID} \"{path}\ {SCHEMA_DBTABLE}".format( SRID=srid, path=os.path.normpath(path), SCHEMA_DBTABLE=self.table_name()) cmd_string = """ | psql -U {USER} -d {DATABASE} --port {PORT} --host {HOST}""".format( USER=self.opts["user"], DATABASE=self.opts["database"], PORT=self.opts["port"], HOST=self.opts["host"]) cmd_stmt = vector_sql + cmd_string if self.debug: print(cmd_stmt) subprocess.call(cmd_stmt, shell=True, stdout=subprocess.PIPE)
def insert_raster(self, path=None, srid=4326): """Import Raster into Postgis Table Uses raster2pgsql -Y -M -d -I -s <SRID> <PATH> <SCHEMA>.<DBTABLE> | psql -d <DATABASE> The sql processed by raster2pgsql is run as psql -U postgres -d <gisdb> -f <elev>.sql -Y uses COPY to insert data, -M VACUUM table, -d Drops the table, recreates insert raster data """ if not path: path = Engine.format_data_dir(self) is_cli_extent = hasattr(self, 'opts') and self.opts["bbox"] is_resource_extent = hasattr(self.script.tables[self.table.name], 'extent') is_global_script_extent = hasattr(self.script, 'extent') if any([is_cli_extent, is_resource_extent, is_global_script_extent]): # bounding box array: bbox[xmin, ymax, xmax, ymin] bbox = [] if self.opts["bbox"]: bbox.append(self.opts["bbox"][0]) bbox.append(self.opts["bbox"][1]) bbox.append(self.opts["bbox"][2]) bbox.append(self.opts["bbox"][3]) else: if is_global_script_extent and self.script.extent: bbox.append(self.script.extent["xMin"]) bbox.append(self.script.extent["yMax"]) bbox.append(self.script.extent["xMax"]) bbox.append(self.script.extent["yMin"]) else: if is_resource_extent and self.script.tables[ self.table.name].extent: bbox.append( self.script.tables[self.table.name].extent["xMin"]) bbox.append( self.script.tables[self.table.name].extent["yMax"]) bbox.append( self.script.tables[self.table.name].extent["xMax"]) bbox.append( self.script.tables[self.table.name].extent["yMin"]) bbox = [int(i) for i in bbox] if gdal and bbox and bbox[2] > bbox[0] and bbox[1] > bbox[3]: if self.script.tables[self.table.name].extensions: converted_tif = path[:-3] + "tif" if self.script.tables[ self.table.name].extensions[0] == "bil": conversion = "gdal_translate -co 'COMPRESS=LZW' {path} {converted_tif}".format( path=os.path.normpath(path), converted_tif=converted_tif) os.system(conversion) ds = gdal.Open(converted_tif) info = gdal.Info(converted_tif, format='json') coordinates = info['wgs84Extent']['coordinates'][0] if bbox[0] < coordinates[2][0] and bbox[2] > coordinates[ 0][0] and bbox[1] > coordinates[1][1] and bbox[ 3] < coordinates[0][1]: if bbox[0] < coordinates[0][0]: bbox[0] = coordinates[0][0] if bbox[1] > coordinates[0][1]: bbox[1] = coordinates[0][1] if bbox[2] > coordinates[2][0]: bbox[2] = coordinates[2][0] if bbox[3] < coordinates[2][1]: bbox[3] = coordinates[2][1] i = converted_tif.find(".tif") location_of_cropped_tif = converted_tif[:i] + \ "crop" + converted_tif[i:] ds = gdal.Translate(location_of_cropped_tif, ds, projWin=bbox) ds = None path = location_of_cropped_tif else: print("Bounding Box exceds image boundaries") elif bbox: print( "Invalid value of Extent, bbox[xmin, ymax, xmax, ymin]" ) raster_sql = ('raster2pgsql -Y -M -d -I -l 2 -s {SRID} "{path}"' " -F -t 100x100 {SCHEMA_DBTABLE}".format( SRID=srid, path=os.path.normpath(path), SCHEMA_DBTABLE=self.table_name())) cmd_string = (" | psql -U {USER} -d {DATABASE} " "--port {PORT} --host {HOST} > {nul_dev} ".format( USER=self.opts["user"], DATABASE=self.opts["database"], PORT=self.opts["port"], HOST=self.opts["host"], nul_dev=os.devnull, )) cmd_stmt = raster_sql + cmd_string if self.debug: print(cmd_stmt) Engine.register_tables(self) try: subprocess.call(cmd_stmt, shell=True) except BaseException: pass