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
