def test_wkt(self):
""" unit test to verify the DatastoreQuery to PostgresQuery to SQL translation for PostGIS WKT """
wkt = 'POINT(-72.0 40.0)'
buf = 0.1
# PostgresQueryBuilder - WKT (no buffer)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.overlaps_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Intersects(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.contains_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Contains(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.within_geom(qb.RA_GEOM_LOC,wkt,0.0))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Within(geom_loc,ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'))")
# PostgresQueryBuilder - WKT (with buffer)
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.overlaps_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Intersects(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.contains_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Contains(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.within_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Within(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
qb = DatastoreQueryBuilder()
qb.build_query(where=qb.equals_geom(qb.RA_GEOM_LOC,wkt,buf))
pqb = PostgresQueryBuilder(qb.get_query(), 'test')
self.assertEquals(pqb.get_query(),"SELECT id,doc FROM test WHERE ST_Equals(geom_loc,ST_Buffer(ST_GeomFromEWKT('SRID=4326;POINT(-72.0 40.0)'), 0.100000))")
def find_by_query(self, query, access_args=None):
"""
Find resources given a datastore query expression dict.
@param query a dict representation of a datastore query
@retval list of resource ids or resource objects matching query (dependent on id_only value)
"""
qual_ds_name = self._get_datastore_name()
query_ds_sub = query["query_args"].get("ds_sub", None)
query_format = query["query_args"].get("format", "")
pqb = PostgresQueryBuilder(query, qual_ds_name)
if self.profile == DataStore.DS_PROFILE.RESOURCES and not query_ds_sub:
table_alias = qual_ds_name if query_format != "complex" else "base"
pqb.where = self._add_access_filter(access_args,
qual_ds_name,
pqb.where,
pqb.values,
add_where=False,
tablealias=table_alias)
if self.profile == DataStore.DS_PROFILE.RESOURCES:
pqb.where = self._add_deleted_filter(
pqb.table_aliases[0],
query_ds_sub,
pqb.where,
pqb.values,
with_deleted=query["query_args"].get("with_deleted",
False) is True)
with self.pool.cursor(**self.cursor_args) as cur:
exec_query = pqb.get_query()
cur.execute(exec_query, pqb.get_values())
rows = cur.fetchall()
log.info("find_by_query() QUERY: %s (%s rows)", cur.query,
cur.rowcount)
query_res = {}
query["_result"] = query_res
query_res["statement_gen"] = exec_query
query_res["statement_sql"] = cur.query
query_res["rowcount"] = cur.rowcount
id_only = query["query_args"].get("id_only", True)
if query_format == "complex" and pqb.has_basic_cols:
# Return format is list of lists
if id_only:
res_vals = [[self._prep_id(row[0])] + list(row[1:])
for row in rows]
else:
res_vals = [[self._persistence_dict_to_ion_object(row[1])] +
list(rows[2:]) for row in rows]
elif query_format == "complex":
res_vals = [list(row) for row in rows]
else:
if id_only:
res_vals = [self._prep_id(row[0]) for row in rows]
else:
res_vals = [
self._persistence_dict_to_ion_object(row[-1])
for row in rows
]
return res_vals
