def test_write_postgis_in_chunks(self, df_mixed_single_and_multi):
"""
Tests that writing a LinearRing.
"""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "geomtype_tests"
try:
write_postgis(
df_mixed_single_and_multi,
con=engine,
name=table,
if_exists="replace",
chunksize=1,
)
# Validate row count
sql = "SELECT COUNT(geometry) FROM {table};".format(table=table)
row_cnt = engine.execute(sql).fetchone()[0]
# Validate geometry type
sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format(
table=table)
geom_type = engine.execute(sql).fetchone()[0]
assert row_cnt == 3
assert geom_type.upper() == "LINESTRING"
except AssertionError as e:
raise e
finally:
engine.dispose()
def test_write_postgis_in_chunks(self, engine_postgis,
df_mixed_single_and_multi):
"""
Tests writing a LinearRing works.
"""
engine = engine_postgis
table = "geomtype_tests"
write_postgis(
df_mixed_single_and_multi,
con=engine,
name=table,
if_exists="replace",
chunksize=1,
)
# Validate row count
sql = "SELECT COUNT(geometry) FROM {table};".format(table=table)
row_cnt = engine.execute(sql).fetchone()[0]
assert row_cnt == 3
# Validate geometry type
sql = "SELECT DISTINCT GeometryType(geometry) FROM {table} ORDER BY 1;".format(
table=table)
res = engine.execute(sql).fetchall()
assert res[0][0].upper() == "LINESTRING"
assert res[1][0].upper() == "MULTILINESTRING"
assert res[2][0].upper() == "POINT"
def test_write_postgis_mixed_geometry_types(self,
df_mixed_single_and_multi):
"""
Tests that writing a mix of single and MultiGeometries is possible.
"""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "geomtype_tests"
try:
write_postgis(df_mixed_single_and_multi,
con=engine,
name=table,
if_exists="replace")
# Validate geometry type
sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format(
table=table)
res = engine.execute(sql).fetchall()
geom_type_1 = res[0][0]
geom_type_2 = res[1][0]
assert geom_type_1.upper() == "LINESTRING", (
"Geometry type should be 'LINESTRING',",
"found: {gt}".format(gt=geom_type_1),
)
assert geom_type_2.upper() == "MULTILINESTRING", (
"Geometry type should be 'MULTILINESTRING',",
"found: {gt}".format(gt=geom_type_1),
)
except AssertionError as e:
raise e
finally:
engine.dispose()
def test_write_postgis_linear_ring(self, df_linear_ring):
"""
Tests that writing a LinearRing.
"""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "geomtype_tests"
try:
write_postgis(df_linear_ring,
con=engine,
name=table,
if_exists="replace")
# Validate geometry type
sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format(
table=table)
geom_type = engine.execute(sql).fetchone()[0]
assert geom_type.upper() == "LINESTRING"
except AssertionError as e:
raise e
finally:
engine.dispose()
def test_write_postgis_geometry_collection(self, df_geom_collection):
"""
Tests that writing a mix of different geometry types is possible.
"""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "geomtype_tests"
try:
write_postgis(df_geom_collection,
con=engine,
name=table,
if_exists="replace")
# Validate geometry type
sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format(
table=table)
geom_type = engine.execute(sql).fetchone()[0]
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
assert geom_type.upper() == "GEOMETRYCOLLECTION"
assert df.geom_type.unique()[0] == "GeometryCollection"
except AssertionError as e:
raise e
finally:
engine.dispose()
def test_write_postgis_append_when_table_exists(self, df_nybb):
"""
Tests that appending to existing table produces correct results when:
if_replace='append'.
"""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "nybb"
try:
orig_rows, orig_cols = df_nybb.shape
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
write_postgis(df_nybb, con=engine, name=table, if_exists="append")
# Validate
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
new_rows, new_cols = df.shape
# There should be twice as many rows in the new table
assert new_rows == orig_rows * 2, (
"There should be {target} rows,",
"found: {current}".format(target=orig_rows * 2,
current=new_rows),
)
# Number of columns should stay the same
assert new_cols == orig_cols, (
"There should be {target} columns,",
"found: {current}".format(target=orig_cols, current=new_cols),
)
except AssertionError as e:
raise e
finally:
engine.dispose()
def test_write_postgis_append_when_table_exists(self, engine_postgis,
df_nybb):
"""
Tests that appending to existing table produces correct results when:
if_replace='append'.
"""
engine = engine_postgis
table = "nybb"
orig_rows, orig_cols = df_nybb.shape
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
write_postgis(df_nybb, con=engine, name=table, if_exists="append")
# Validate
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
new_rows, new_cols = df.shape
# There should be twice as many rows in the new table
assert new_rows == orig_rows * 2, (
"There should be {target} rows,",
"found: {current}".format(target=orig_rows * 2, current=new_rows),
)
# Number of columns should stay the same
assert new_cols == orig_cols, (
"There should be {target} columns,",
"found: {current}".format(target=orig_cols, current=new_cols),
)
def test_write_postgis_3D_geometries(self, engine_postgis, df_3D_geoms):
"""
Tests writing a geometries with 3 dimensions works.
"""
engine = engine_postgis
table = "geomtype_tests"
write_postgis(df_3D_geoms, con=engine, name=table, if_exists="replace")
# Check that all geometries have 3 dimensions
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
assert list(df.geometry.has_z) == [True, True, True]
def test_write_postgis_default(self, engine_postgis, df_nybb):
"""Tests that GeoDataFrame can be written to PostGIS with defaults."""
engine = engine_postgis
table = "nybb"
# If table exists, delete it before trying to write with defaults
drop_table_if_exists(engine, table)
# Write to db
write_postgis(df_nybb, con=engine, name=table, if_exists="fail")
# Validate
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
def test_write_postgis_sqlalchemy_connection(self, engine_postgis, df_nybb):
"""Tests that GeoDataFrame can be written to PostGIS with defaults."""
with engine_postgis.begin() as con:
table = "nybb_con"
# If table exists, delete it before trying to write with defaults
drop_table_if_exists(con, table)
# Write to db
write_postgis(df_nybb, con=con, name=table, if_exists="fail")
# Validate
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, con, geom_col="geometry")
validate_boro_df(df)
def test_write_postgis_uppercase_tablename(self, engine_postgis, df_nybb):
"""Tests writing GeoDataFrame to PostGIS with uppercase tablename."""
engine = engine_postgis
table = "aTestTable"
# If table exists, delete it before trying to write with defaults
drop_table_if_exists(engine, table)
# Write to db
write_postgis(df_nybb, con=engine, name=table, if_exists="fail")
# Validate
sql = 'SELECT * FROM "{table}";'.format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
def test_append_with_different_crs(self, engine_postgis, df_nybb):
"""
Tests that the warning is raised if table CRS differs from frame.
"""
engine = engine_postgis
table = "nybb"
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
# Reproject
df_nybb2 = df_nybb.to_crs(epsg=4326)
# Should raise error when appending
with pytest.raises(ValueError, match="CRS of the target table"):
write_postgis(df_nybb2, con=engine, name=table, if_exists="append")
def test_row_order(self, engine_postgis, df_nybb):
"""
Tests that the row order in db table follows the order of the original frame.
"""
engine = engine_postgis
table = "row_order_test"
correct_order = df_nybb["BoroCode"].tolist()
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
# Check that the row order matches
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
assert df["BoroCode"].tolist() == correct_order
def test_write_postgis_replace_when_table_exists(self, engine_postgis, df_nybb):
"""
Tests that replacing a table is possible when: if_replace='replace'.
"""
engine = engine_postgis
table = "nybb"
# Ensure table exists
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
# Overwrite
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
# Validate
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
def test_append_before_table_exists(self, engine_postgis, df_nybb):
"""
Tests that insert works with if_exists='append' when table does not exist yet.
"""
engine = engine_postgis
table = "nybb"
# If table exists, delete it before trying to write with defaults
drop_table_if_exists(engine, table)
write_postgis(df_nybb, con=engine, name=table, if_exists="append")
# Check that the row order matches
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
def test_write_postgis_fail_when_table_exists(self, engine_postgis, df_nybb):
"""
Tests that uploading the same table raises error when: if_replace='fail'.
"""
engine = engine_postgis
table = "nybb"
# Ensure table exists
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
try:
write_postgis(df_nybb, con=engine, name=table, if_exists="fail")
except ValueError as e:
if "already exists" not in str(e):
raise e
def test_write_postgis_linear_ring(self, engine_postgis, df_linear_ring):
"""
Tests that writing a LinearRing.
"""
engine = engine_postgis
table = "geomtype_tests"
write_postgis(df_linear_ring, con=engine, name=table, if_exists="replace")
# Validate geometry type
sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table} ORDER BY 1;".format(
table=table
)
geom_type = engine.execute(sql).fetchone()[0]
assert geom_type.upper() == "LINESTRING"
def test_write_postgis_without_crs(self, engine_postgis, df_nybb):
"""
Tests that GeoDataFrame can be written to PostGIS without CRS information.
"""
engine = engine_postgis
table = "nybb"
# Write to db
df_nybb = df_nybb
df_nybb.crs = None
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
# Validate that srid is -1
target_srid = engine.execute(
"SELECT Find_SRID('{schema}', '{table}', '{geom_col}');".format(
schema="public", table=table,
geom_col="geometry")).fetchone()[0]
assert target_srid == 0, "SRID should be 0, found %s" % target_srid
def test_write_postgis_fail_when_table_exists(self, df_nybb):
"""
Tests that uploading the same table raises error when: if_replace='fail'.
"""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "nybb"
try:
write_postgis(df_nybb, con=engine, name=table, if_exists="fail")
except ValueError as e:
if "already exists" in str(e):
pass
else:
raise e
finally:
engine.dispose()
def test_write_postgis_to_different_schema_when_table_exists(
self, engine_postgis, df_nybb):
"""
Tests writing data to alternative schema.
"""
engine = engine_postgis
table = "nybb"
schema_to_use = "test"
sql = "CREATE SCHEMA IF NOT EXISTS {schema};".format(
schema=schema_to_use)
engine.execute(sql)
try:
write_postgis(df_nybb,
con=engine,
name=table,
if_exists="fail",
schema=schema_to_use)
# Validate
sql = "SELECT * FROM {schema}.{table};".format(
schema=schema_to_use, table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
# Should raise a ValueError when table exists
except ValueError:
pass
# Try with replace flag on
write_postgis(df_nybb,
con=engine,
name=table,
if_exists="replace",
schema=schema_to_use)
# Validate
sql = "SELECT * FROM {schema}.{table};".format(schema=schema_to_use,
table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
def test_write_postgis_replace_when_table_exists(self, df_nybb):
"""
Tests that replacing a table is possible when: if_replace='replace'.
"""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "nybb"
try:
write_postgis(df_nybb, con=engine, name=table, if_exists="replace")
# Validate
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
except ValueError as e:
raise e
finally:
engine.dispose()
def test_write_postgis_default(self, df_nybb):
"""Tests that GeoDataFrame can be written to PostGIS with defaults."""
engine = connect_engine("test_geopandas")
if engine is None:
raise pytest.skip()
table = "nybb"
# If table exists, delete it before trying to write with defaults
drop_table_if_exists(engine, table)
try:
# Write to db
write_postgis(df_nybb, con=engine, name=table, if_exists="fail")
# Validate
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
finally:
engine.dispose()
def test_write_postgis_to_different_schema(self, engine_postgis, df_nybb):
"""
Tests writing data to alternative schema.
"""
engine = engine_postgis
table = "nybb"
schema_to_use = "test"
sql = "CREATE SCHEMA IF NOT EXISTS {schema};".format(schema=schema_to_use)
engine.execute(sql)
write_postgis(
df_nybb, con=engine, name=table, if_exists="replace", schema=schema_to_use
)
# Validate
sql = "SELECT * FROM {schema}.{table};".format(
schema=schema_to_use, table=table
)
df = read_postgis(sql, engine, geom_col="geometry")
validate_boro_df(df)
def test_write_postgis_mixed_geometry_types(self, engine_postgis,
df_mixed_single_and_multi):
"""
Tests that writing a mix of single and MultiGeometries is possible.
"""
engine = engine_postgis
table = "geomtype_tests"
write_postgis(df_mixed_single_and_multi,
con=engine,
name=table,
if_exists="replace")
# Validate geometry type
sql = "SELECT DISTINCT GeometryType(geometry) FROM {table} ORDER BY 1;".format(
table=table)
res = engine.execute(sql).fetchall()
assert res[0][0].upper() == "LINESTRING"
assert res[1][0].upper() == "MULTILINESTRING"
assert res[2][0].upper() == "POINT"
def test_write_postgis_geometry_collection(
self, engine_postgis, df_geom_collection
):
"""
Tests that writing a mix of different geometry types is possible.
"""
engine = engine_postgis
table = "geomtype_tests"
write_postgis(df_geom_collection, con=engine, name=table, if_exists="replace")
# Validate geometry type
sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table} ORDER BY 1;".format(
table=table
)
geom_type = engine.execute(sql).fetchone()[0]
sql = "SELECT * FROM {table};".format(table=table)
df = read_postgis(sql, engine, geom_col="geometry")
assert geom_type.upper() == "GEOMETRYCOLLECTION"
assert df.geom_type.unique()[0] == "GeometryCollection"
