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