def test_ST_Equal_Column_WKTElement(self, geometry_table):
expr = func.ST_Equals(geometry_table.c.geom, WKTElement('POINT(1 2)'))
eq_sql(
expr, 'ST_Equals("table".geom, '
'ST_GeomFromText(:ST_GeomFromText_1, :ST_GeomFromText_2))')
assert expr.compile().params == {
u'ST_GeomFromText_1': 'POINT(1 2)',
u'ST_GeomFromText_2': -1
}
def test_ST_Equal_Column_WKTElement(self, geometry_table):
expr = func.ST_Equals(geometry_table.c.geom,
WKTElement('SRID=3857;POINT(1 2 3)',
extended=True))
eq_sql(expr,
'ST_Equals("table".geom, '
'ST_GeomFromEWKT(:ST_GeomFromEWKT_1))')
assert expr.compile().params == {
u'ST_GeomFromEWKT_1': 'SRID=3857;POINT(1 2 3)',
}
def test_ST_Equal_WKTElement_WKTElement(self):
expr = func.ST_Equals(WKTElement('SRID=3857;POINT(1 2 3)',
extended=True),
WKTElement('SRID=3857;POINT(1 2 3)',
extended=True))
eq_sql(expr, 'ST_Equals('
'ST_GeomFromEWKT(:ST_GeomFromEWKT_1), '
'ST_GeomFromEWKT(:ST_GeomFromEWKT_2))')
assert expr.compile().params == {
u'ST_GeomFromEWKT_1': 'SRID=3857;POINT(1 2 3)',
u'ST_GeomFromEWKT_2': 'SRID=3857;POINT(1 2 3)',
}
def test_ST_Equal_WKTElement_WKTElement(self):
expr = func.ST_Equals(WKTElement('POINT(1 2)'),
WKTElement('POINT(1 2)'))
eq_sql(expr, 'ST_Equals('
'ST_GeomFromText(:ST_GeomFromText_1, :ST_GeomFromText_2), '
'ST_GeomFromText(:ST_GeomFromText_3, :ST_GeomFromText_4))')
assert expr.compile().params == {
u'ST_GeomFromText_1': 'POINT(1 2)',
u'ST_GeomFromText_2': -1,
u'ST_GeomFromText_3': 'POINT(1 2)',
u'ST_GeomFromText_4': -1,
}
def test_ST_Equal_Column_WKTElement(self):
from sqlalchemy import func
from geoalchemy2.elements import WKTElement
table = _create_geometry_table()
expr = func.ST_Equals(table.c.geom, WKTElement('POINT(1 2)'))
eq_sql(
expr, 'ST_Equals("table".geom, '
'ST_GeomFromText(:ST_GeomFromText_1, :ST_GeomFromText_2))')
eq_(expr.compile().params, {
u'ST_GeomFromText_1': 'POINT(1 2)',
u'ST_GeomFromText_2': -1
})
def test_ST_Equal_WKTElement_WKTElement(self):
from sqlalchemy import func
from geoalchemy2.elements import WKTElement
expr = func.ST_Equals(WKTElement('POINT(1 2)'),
WKTElement('POINT(1 2)'))
eq_sql(
expr, 'ST_Equals('
'ST_GeomFromText(:ST_GeomFromText_1, :ST_GeomFromText_2), '
'ST_GeomFromText(:ST_GeomFromText_3, :ST_GeomFromText_4))')
eq_(
expr.compile().params, {
u'ST_GeomFromText_1': 'POINT(1 2)',
u'ST_GeomFromText_2': -1,
u'ST_GeomFromText_3': 'POINT(1 2)',
u'ST_GeomFromText_4': -1
})
def geom_duplicate(table):
"""
Return DataFrame with all records that have identical, stacked geometry.
Parameters
----------
table : sqlalchemy.ext.declarative.DeclarativeMeta
Table ORM class to diagnose.
Returns
-------
df : pandas.DataFrame
"""
# Create table aliases to cross join table to self.
table_a = aliased(table)
table_b = aliased(table)
# Get primary key of table and table aliases.
pk = class_mapper(table).primary_key[0]
table_a_pk = getattr(table_a, pk.name)
table_b_pk = getattr(table_b, pk.name)
# Query rows with duplicate geometries.
with db.session() as sess:
dups = sess.query(
table_a_pk.label('a'), table_b_pk.label('b')
).filter(
table_a_pk < table_b_pk,
func.ST_Equals(table_a.geom, table_b.geom)
)
rows = sess.query(table).filter(
or_(
pk.in_(dups.selectable.with_only_columns([table_a_pk])),
pk.in_(dups.selectable.with_only_columns([table_b_pk]))
)
)
# Convert query to DataFrame.
df = io.db_to_df(rows)
return df
