def transaction_search(context, data_dict, timeout):
fields = []
fields[:] = [
field.get('id') for field in db._get_fields(context, data_dict)
]
try:
select_columns = u', '.join(
[u'"{0}"'.format(field_id) for field_id in fields])
sql_string = u'SELECT {0} FROM "{1}"'.format(select_columns,
data_dict['resource_id'])
return format_to_array_of_dict(
context['connection'].execute(sql_string), fields)
except DBAPIError, e:
if e.orig.pgcode == db._PG_ERR_CODE['query_canceled']:
raise db.ValidationError({'query': ['Search took too long']})
raise db.ValidationError({
'query': ['Invalid query'],
'info': {
'statement': [e.statement],
'params': [e.params],
'orig': [str(e.orig)]
}
})
context['connection'].close()
def publish(self):
"""
Checks and generates the 'Geometry' column in the table for Geoserver to work on.
Resource in datastore database is checked for Geometry field. If the field doesn't exists then calculates the
geometry field value and creates it in the table.
"""
# Get the connection parameters for the datastore
conn_params = {
'connection_url': self.connection_url,
'resource_id': self.resource_id
}
engine = db._get_engine(conn_params)
connection = engine.connect()
try:
# This will fail with a ProgrammingError if the table does not exist
fields = db._get_fields({"connection": connection}, conn_params)
except ProgrammingError as ex:
raise toolkit.ObjectNotFound(
toolkit._("Resource not found in datastore database"))
# If there is not already a geometry column...
if not True in set(col['id'] == self.geo_col for col in fields):
# ... append one
fields.append({'id': self.geo_col, 'type': u'geometry'})
self.clean_fields(connection, fields)
# SQL to create the geometry column
sql = "SELECT AddGeometryColumn('public', '%s', '%s', 4326, 'GEOMETRY', 2)" % (
self.resource_id, self.geo_col)
# Create the new column
trans = connection.begin()
connection.execute(sql)
trans.commit()
# Update values in the Geometry column
sql = "UPDATE \"%s\" SET \"%s\" = geometryfromtext('POINT(' || \"%s\" || ' ' || \"%s\" || ')', 4326)"
sql = sql % (self.resource_id, self.geo_col, self.lng_col,
self.lat_col)
trans = connection.begin()
connection.execute(sql)
trans.commit()
return True
return True
def publish(self):
"""
Checks and generates the 'Geometry' column in the table for Geoserver to work on.
Resource in datastore database is checked for Geometry field. If the field doesn't exists then calculates the
geometry field value and creates it in the table.
"""
# Get the connection parameters for the datastore
conn_params = {"connection_url": self.connection_url, "resource_id": self.resource_id}
engine = db._get_engine(conn_params)
connection = engine.connect()
try:
# This will fail with a ProgrammingError if the table does not exist
fields = db._get_fields({"connection": connection}, conn_params)
except ProgrammingError as ex:
raise toolkit.ObjectNotFound(toolkit._("Resource not found in datastore database"))
# If there is not already a geometry column...
if not True in set(col["id"] == self.geo_col for col in fields):
# ... append one
fields.append({"id": self.geo_col, "type": u"geometry"})
self.clean_fields(connection, fields)
# SQL to create the geometry column
sql = "SELECT AddGeometryColumn('public', '%s', '%s', 4326, 'GEOMETRY', 2)" % (
self.resource_id,
self.geo_col,
)
# Create the new column
trans = connection.begin()
connection.execute(sql)
trans.commit()
# Update values in the Geometry column
sql = 'UPDATE "%s" SET "%s" = geometryfromtext(\'POINT(\' || "%s" || \' \' || "%s" || \')\', 4326)'
sql = sql % (self.resource_id, self.geo_col, self.lng_col, self.lat_col)
trans = connection.begin()
connection.execute(sql)
trans.commit()
return True
return True
def __init__(self, context, data_dict):
baseGeoserverUrl = data_dict['geoserver']
resource_id = data_dict['resource_id']
layer_name = data_dict['layer_name']
workspace_name = data_dict['workspace_name']
if baseGeoserverUrl is None:
print "using default geoserver :"+baseGeoserverUrl
baseGeoserverUrl = 'http://*****:*****@class": "dataStore",
"name": "datastore",
"href": baseGeoserverUrl + "/workspaces/" + workspace_name + "/datastores/datastore.json"
}
def publish(self):
"""
Checks and generates the 'Geometry' column in the table for Geoserver to work on.
Resource in datastore database is checked for Geometry field. If the field doesn't exists then calculates the
geometry field value and creates it in the table.
"""
pkg = toolkit.get_action('package_show')(None, {
'id': self.package_id
})
extras = pkg.get('extras', [])
for extra in extras:
key = extra.get('key', None)
if key == self.descriptor_name:
schema_descriptor = json.loads(extra.get('value'))
break
obs = []
geom = []
for member in schema_descriptor.get("members"):
if member.get('resource_type') == 'observed_geometries':
geom_fields = member.get('fields')
for ids in member.get('resource_name'):
geom.append(ids)
if member.get('resource_type') == 'observations':
obs_fields = member.get('fields')
for ids in member.get('resource_name'):
obs.append(ids)
if self.join_key is None:
for geom_field in geom_fields:
for obs_field in obs_fields:
if geom_field.get('field_id').lower() == obs_field.get('field_id').lower():
self.join_key = geom_field.get('field_id').lower()
if len(geom) != 1 and len(obs) == 0:
raise toolkit.ObjectNotFound(toolkit._("Mutliple geometry files found"))
# Get the connection parameters for the datastore
conn_params = {
'connection_url': self.connection_url,
'resource_id': geom[0]
}
engine = db._get_engine(conn_params)
connection = engine.connect()
try:
# This will fail with a ProgrammingError if the table does not exist
fields = db._get_fields({
"connection": connection
}, conn_params)
except ProgrammingError as ex:
raise toolkit.ObjectNotFound(toolkit._("Resource not found in datastore database"))
# If there is not already a geometry column...
if not True in set(col['id'] == self.geo_col for col in fields):
# ... append one
fields.append({
'id': self.geo_col,
'type': u'geometry'
})
self.clean_fields(connection, fields)
# SQL to create the geometry column
sql = "SELECT AddGeometryColumn('public', '%s', '%s', 4326, 'GEOMETRY', 2)" % (geom[0], self.geo_col)
# Create the new column
trans = connection.begin()
connection.execute(sql)
trans.commit()
# Update values in the Geometry column
sql = "UPDATE \"%s\" SET \"%s\" = st_geometryfromtext('POINT(' || \"%s\" || ' ' || \"%s\" || ')', 4326)"
sql = sql % (geom[0], self.geo_col, self.lng_col, self.lat_col)
trans = connection.begin()
connection.execute(sql)
trans.commit()
selectsql = "SELECT "
for fields in geom_fields:
if fields.get('field_type').lower() == 'date':
if fields.get('date_format') is not None:
postgresdate = self.convertIsoToPostgres(fields.get('date_format'))
else:
postgresdate = self.convertIsoToPostgres('default')
selectsql += "to_timestamp(CAST(\"" + geom[0] + "\".\"" + self.clean_name(fields.get('field_id'), '_') + "\" as text), \'" + postgresdate + "\') as \"geometry." + self.clean_name(
fields.get('field_id'), '_') + "\", "
else:
selectsql += "\"" + geom[0] + "\".\"" + self.clean_name(fields.get('field_id'), '_') + "\" as \"geometry." + self.clean_name(fields.get('field_id'), '_') + "\", "
if fields.get('field_id').lower() == self.join_key:
geom_key = fields.get('field_id')
selectsql += "\"" + geom[0] + "\".\"" + self.geo_col + "\" as \"geometry." + self.geo_col + "\", "
for fields in obs_fields:
if fields.get('field_type').lower() == 'date':
if fields.get('date_format') is not None:
postgresdate = self.convertIsoToPostgres(fields.get('date_format'))
else:
postgresdate = self.convertIsoToPostgres('default')
selectsql += "to_timestamp(CAST(observations.\"" + self.clean_name(fields.get('field_id'), '_') + "\" as text), \'" + postgresdate + "\') as \"geometry." + self.clean_name(
fields.get('field_id'), '_') + "\", "
else:
selectsql += "observations.\"" + self.clean_name(fields.get('field_id'), '_') + "\" as \"observations." + self.clean_name(fields.get('field_id'), '_') + "\", "
if fields.get('field_id').lower() == self.join_key:
obs_key = fields.get('field_id')
selectsql = selectsql[:-2] + " "
selectsql += "FROM "
selectsql += "public.\"" + geom[0] + "\" "
selectsql += "INNER JOIN ("
for entry in obs:
selectsql += "SELECT * FROM public.\"" + entry + "\" UNION ALL "
selectsql = selectsql[:-11] + ") as observations "
selectsql += "ON public.\"" + geom[0] + "\".\"" + geom_key + "\" = observations.\"" + obs_key + "\""
sql = "DROP MATERIALIZED VIEW IF EXISTS \"%s\"; CREATE MATERIALIZED VIEW \"%s\" AS " + selectsql
# sql = "DROP VIEW IF EXISTS \"_%s\"; CREATE VIEW \"_%s\" AS " + selectsql
sql = sql % (self.getName(), self.getName())
trans = connection.begin()
connection.execute(sql)
trans.commit()
connection.close()
return True
def publish(self):
"""
Checks and generates the 'Geometry' column in the table for Geoserver to work on.
Resource in datastore database is checked for Geometry field. If the field doesn't exists then calculates the
geometry field value and creates it in the table.
"""
# Get the connection parameters for the datastore
conn_params = {
'connection_url': self.connection_url,
'resource_id': self.resource_id
}
engine = db._get_engine(conn_params)
connection = engine.connect()
try:
# This will fail with a ProgrammingError if the table does not exist
fields = db._get_fields({
"connection": connection
}, conn_params)
except ProgrammingError as ex:
raise toolkit.ObjectNotFound(toolkit._("Resource not found in datastore database"))
# If there is not already a geometry column...
if not True in set(col['id'] == self.geo_col for col in fields):
# ... append one
fields.append({
'id': self.geo_col,
'type': u'geometry'
})
self.clean_fields(connection, fields)
# SQL to create the geometry column
sql = "SELECT AddGeometryColumn('public', '%s', '%s', 4326, 'GEOMETRY', 2)" % (self.resource_id, self.geo_col)
# Create the new column
trans = connection.begin()
connection.execute(sql)
trans.commit()
# Update values in the Geometry column
sql = "UPDATE \"%s\" SET \"%s\" = st_geometryfromtext('POINT(' || \"%s\" || ' ' || \"%s\" || ')', 4326)"
sql = sql % (self.resource_id, self.geo_col, self.lng_col, self.lat_col)
trans = connection.begin()
connection.execute(sql)
trans.commit()
pkg = toolkit.get_action('package_show')(None, {
'id': self.package_id
})
extras = pkg.get('extras', [])
for extra in extras:
key = extra.get('key', None)
if key == self.descriptor_name:
schema_descriptor = json.loads(extra.get('value'))
break
description = schema_descriptor.get("members")[0]
sql = "DROP MATERIALIZED VIEW IF EXISTS \"" + self.view_name() + "\"; CREATE MATERIALIZED VIEW \"" + self.view_name() + "\" AS SELECT "
for fields in description.get('fields'):
if fields.get('field_type').lower() == 'date':
if fields.get('date_format') is not None:
postgresdate = self.convertIsoToPostgres(fields.get('date_format'))
else:
postgresdate = self.convertIsoToPostgres('default')
sql += "to_timestamp(CAST(\"" + self.table_name() + "\".\"" + self.clean_name(fields.get('field_id'), '_') + "\" as text), \'" + postgresdate + "\') as \"" + self.clean_name(
fields.get('field_id'), '_') + "\", "
else:
sql += "\"" + self.table_name() + "\".\"" + self.clean_name(fields.get('field_id'), '_') + "\" as \"" + self.clean_name(fields.get('field_id'), '_') + "\", "
sql += "\"" + self.table_name() + "\".\"" + self.geo_col + "\" as \"" + self.geo_col + "\", "
sql = sql[:-2] + " "
sql += "FROM \"" + self.table_name() + "\""
trans = connection.begin()
connection.execute(sql)
trans.commit()
connection.close()
return True
def publish(self):
"""
Checks and generates the 'Geometry' column in the table for Geoserver to work on.
Resource in datastore database is checked for Geometry field. If the field doesn't exists then calculates the
geometry field value and creates it in the table.
"""
# Get the connection parameters for the datastore
conn_params = {
'connection_url': self.connection_url,
'resource_id': self.resource_id
}
engine = db._get_engine(conn_params)
connection = engine.connect()
try:
# This will fail with a ProgrammingError if the table does not exist
fields = db._get_fields({"connection": connection}, conn_params)
except ProgrammingError as ex:
raise toolkit.ObjectNotFound(
toolkit._("Resource not found in datastore database"))
# If there is not already a geometry column...
if not True in set(col['id'] == self.geo_col for col in fields):
# ... append one
fields.append({'id': self.geo_col, 'type': u'geometry'})
self.clean_fields(connection, fields)
# SQL to create the geometry column
sql = "SELECT AddGeometryColumn('public', '%s', '%s', 4326, 'GEOMETRY', 2)" % (
self.resource_id, self.geo_col)
# Create the new column
trans = connection.begin()
connection.execute(sql)
trans.commit()
# Update values in the Geometry column
sql = "UPDATE \"%s\" SET \"%s\" = st_geometryfromtext('POINT(' || \"%s\" || ' ' || \"%s\" || ')', 4326)"
sql = sql % (self.resource_id, self.geo_col, self.lng_col,
self.lat_col)
trans = connection.begin()
connection.execute(sql)
trans.commit()
pkg = toolkit.get_action('package_show')(None, {'id': self.package_id})
extras = pkg.get('extras', [])
for extra in extras:
key = extra.get('key', None)
if key == self.descriptor_name:
schema_descriptor = json.loads(extra.get('value'))
break
description = schema_descriptor.get("members")[0]
sql = "DROP MATERIALIZED VIEW IF EXISTS \"" + self.view_name(
) + "\"; CREATE MATERIALIZED VIEW \"" + self.view_name(
) + "\" AS SELECT "
for fields in description.get('fields'):
if fields.get('field_type').lower() == 'date':
if fields.get('date_format') is not None:
postgresdate = self.convertIsoToPostgres(
fields.get('date_format'))
else:
postgresdate = self.convertIsoToPostgres('default')
sql += "to_timestamp(CAST(\"" + self.table_name(
) + "\".\"" + self.clean_name(
fields.get('field_id'), '_'
) + "\" as text), \'" + postgresdate + "\') as \"" + self.clean_name(
fields.get('field_id'), '_') + "\", "
else:
sql += "\"" + self.table_name() + "\".\"" + self.clean_name(
fields.get('field_id'),
'_') + "\" as \"" + self.clean_name(
fields.get('field_id'), '_') + "\", "
sql += "\"" + self.table_name(
) + "\".\"" + self.geo_col + "\" as \"" + self.geo_col + "\", "
sql = sql[:-2] + " "
sql += "FROM \"" + self.table_name() + "\""
trans = connection.begin()
connection.execute(sql)
trans.commit()
connection.close()
return True
