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