예제 #1
0
def generateOrders(*args):
    count = None
    if len(args) == 0:
        count = inputCount()
    else:
        count = args[0]

    clientsCount = session.query(Client).count()
    startTime = time.time()
    if len(args) == 0: print('\nRows generating...')

    if int(count) / 5 > clientsCount:
        generateClients(int(int(count) / 5) - clientsCount)

    session.execute(
        f"INSERT INTO \"Order\" (taxes_sum, transaction_date, client_id) "
        f"SELECT generateint(500)::numeric, "
        f"generatedate(), "
        f"getrandomrow('Client')::int "
        f"FROM generate_series(1, {count})")
    session.commit()

    endTime = time.time()

    if len(args) == 0:
        print('Rows generated! Elapsed time: ' + str(endTime - startTime)[:9] +
              's')
        input('\nPress any key to continue...')
예제 #2
0
def generateOrderRelation(*args):
    count = None
    if len(args) == 0:
        count = inputCount()
    else:
        count = args[0]

    if session.query(Product).count() < 10:
        raise Exception(
            'Too few product in the database. Please scrap or parse some data')
    ordersCount = session.query(Order).count()

    startTime = time.time()
    if len(args) == 0: print('\nRows generating...')

    if int(count) / 10 > ordersCount:
        generateOrders(int(int(count) / 10) - ordersCount)

    session.execute(
        f"INSERT INTO \"Link_Product-Order\" (product_id, order_id) "
        f"SELECT getrandomrow('Product')::int, "
        f"getrandomrow('Order')::int "
        f"FROM generate_series(1,{count})")
    session.commit()

    endTime = time.time()

    if len(args) == 0:
        print('Rows generated! Elapsed time: ' + str(endTime - startTime)[:9] +
              's')
        input('\nPress any key to continue...')
예제 #3
0
def update_payment_link_by_id(id, **kwargs):
    try:
        update = payment_links_table.update().values(**kwargs).where(
            payment_links_table.c.id == int(id))
        session.execute(update)
    except Exception as e:
        print(e)
        return redirect(url_for('.create_payment_link'))
예제 #4
0
	def commit_history(self):
		# first delete any old stored history
		# note that if the max lines config variable is shortened that some history will hang around
		# I don't see this as a huge problem
		session.execute('DELETE FROM history LIMIT %d' % len(self.history) )

		# now insert everything we have stored so far
		session.executemany( 'INSERT INTO history VALUES (?,?,?)', self.history)
		conn.commit()
예제 #5
0
    def _handle(self, item):
        values = item.body['values']
        model = get_model(item.attrs['model'])

        try:
            session.execute('insert into %s values %s' % (
                model.__tablename__, ','.join(values)
            ))
            session.commit()
        except Exception as e:
            print(f'Error in bulk insert {item.id}')
예제 #6
0
	def forget_all(self,keyvalue):
		phrase = self.get_phrase_by_text(keyvalue)

		try:
			session.execute('DELETE FROM responses WHERE phrase_id=?',[phrase['id']])
			session.execute('DELETE FROM phrase WHERE phrase_id=?',[phrase['id']] )
			conn.commit()
		except (AttributeError,KeyError):
			raise PhraseNotFound('That phrase does not exist')

		return True
예제 #7
0
def main():
    session.execute('truncate table {}'.format(NogizakaMemberTable.__table__))
    ret = namelist()
    for elem in ret:
        print(elem)
        member = NogizakaMemberTable()
        member.name = elem["name"]
        member.namekana = elem["name_kana"]
        member.birthday = elem["birthday"]
        session.add(member)
        session.commit()
예제 #8
0
	def forget_response(self,keyvalue,response):
		phrase = self.get_phrase_by_text(keyvalue)
		try:
			response = self.get_response_by_text(response, phrase['id'])
			try:
				response_id = response['id']
				session.execute('DELETE FROM responses WHERE id=?',[response_id])
				conn.commit()
			except (AttributeError,KeyError):
				raise ResponseNotFound('That response does not exist')

		except (AttributeError,KeyError):
			raise PhraseNotFound('That phrase does not exist')
def parse2():
    data_dict = parseCsv(
        '../data/csv/DatafinitiElectronicsProductsPricingData.csv', [
            'name', 'brand', 'categories', 'dateAdded', 'manufacturer',
            'primaryCategories', 'prices.amountMin'
        ], 'name')
    # Adding Products to database:
    products_count = 0
    categories_count = 0
    links_count = 0
    for row in data_dict:
        new_product = Product(row['name'], row['brand'], row['manufacturer'],
                              row['dateAdded'],
                              int(float(row['prices.amountMin'])))
        session.add(new_product)
        session.commit()
        products_count += 1
        session.refresh(new_product)

        # Adding categories to database:
        category_names = row['categories'].split(',')

        for category_name in category_names:
            checked_categories = session.query(Category).filter(
                Category.name == category_name).all()
            if len(checked_categories) == 0:
                new_category = Category(category_name,
                                        row['primaryCategories'])
                session.add(new_category)
                session.commit()
                categories_count += 1
                session.refresh(new_category)

                # Adding link:
                ins = links_products_categories.insert().values(
                    product_id=new_product.id, category_id=new_category.id)
                session.execute(ins)
            else:
                ins = links_products_categories.insert().values(
                    product_id=new_product.id,
                    category_id=checked_categories[0].id)
                session.execute(ins)
            links_count += 1
        session.commit()

    print(
        f'Added {products_count} products, {categories_count} categories and {links_count} links.'
    )
    input('\nPress any key to continue...')
예제 #10
0
def getProductsByCategory():
    category = input('Enter category for scrapping: ')
    data = scrape(f'https://www.amazon.com/s?k={category}')
    if not data['products']:
        raise Exception('Null')

    products_count = 0
    categories_count = 0
    links_count = 0
    for raw in data['products']:
        print(f"Get product from {raw['url']}")
        if raw['price'] != None:
            price = int(float(re.findall("\d+\.\d+", raw['price'])[0]))
        else:
            price = random.randint(200, 5000)

        new_product = Product(raw['title'], '', '',
                              datetime.today().strftime('%Y-%m-%d'), price)
        session.add(new_product)
        session.commit()
        products_count += 1
        session.refresh(new_product)

        # Adding categories to database:

        checked_categories = session.query(Category).filter(
            Category.name == category).all()
        if len(checked_categories) == 0:
            new_category = Category(category, category)
            session.add(new_category)
            session.commit()
            categories_count += 1
            session.refresh(new_category)

            # Adding link:
            ins = links_products_categories.insert().values(
                product_id=new_product.id, category_id=new_category.id)
            session.execute(ins)
        else:
            ins = links_products_categories.insert().values(
                product_id=new_product.id,
                category_id=checked_categories[0].id)
            session.execute(ins)
        links_count += 1
    session.commit()
    print(
        f'\nAdded {products_count} products, {categories_count} categories and {links_count} links.'
    )
    input('\nPress any key to continue...')
예제 #11
0
 def setUp(self):
     global engine
     global session
     global insp
     url = os.getenv("PIFPAF_POSTGRESQL_URL")
     if not url:
         self.skipTest("No database URL set")
     engine = sqlalchemy.create_engine(url)
     session = Session(engine)
     insp = inspect(engine)
     session.execute(
         "create table books(id int primary key, name varchar(20), author varchar(20), read boolean);"
     )
     session.commit()
     self.add_data()
예제 #12
0
def get_command_id(command_id):
    """
    Returns as json the command details that have been processed
    ---
    tags: [commands]
    responses:
      200:
        description: Commands returned OK
      400:
        description: Commands not found
    """
    commands = session.query(Command)
    commands = (str(commands))
    commands += " where commands.id={0}".format(command_id)
    result = session.execute(commands)
    json_data = []
    for r in result:
        json_data.append({
            'id': r[0],
            'command_string': r[1],
            'length': r[2],
            'duration': r[3],
            'output': r[4].decode()
        })
    if not json_data:
        return "Commands not found"
    json_data = json.dumps(json_data)
    return jsonify(json.loads(json_data))
예제 #13
0
def create_payment_link():
    insert = sqlalchemy.insert(payment_links_table).values(created=False)
    result = session.execute(insert)
    return redirect(url_for(
        '.title_and_description',
        id=result.lastrowid,
    ))
예제 #14
0
 def getCount(self):
     try:
         return session.execute(
             select([func.count()]).select_from(self.instance)).scalar()
     except Exception as err:
         print("Get count error! ", err)
         raise err
예제 #15
0
파일: fill.py 프로젝트: mms-fcul/pKPDB
def save_residues(tit: pypka.Titration, pid: int) -> pypka.Titration:
    # Save residue-level details
    (
        all_sites,
        _,
        _,
        _,
    ) = tit.getSiteInteractions()

    for site in all_sites:

        residue_details = {
            "pid": pid,
            "residue_number": site.getResNumber(),
            "residue_type": site.res_name,
            "chain": site.molecule.chain,
        }

        res_insert = insert(Residue).values(residue_details)
        res_insert = res_insert.on_conflict_do_update(
            index_elements=["pid", "residue_number", "residue_type", "chain"],
            set_={
                "pid": pid
            },
        ).returning(Residue.resid)
        result = session.execute(res_insert)
        resid = result.fetchall()[0][0]

        site.resid = resid

    session.commit()
    return tit
예제 #16
0
def get_payment_link_by_id(id):
    select = sqlalchemy.select([payment_links_table
                                ]).where(payment_links_table.c.id == int(id))
    result = session.execute(select).fetchone()
    if not result:
        abort(404)
    id, created, title, slug, description, ammount, metadata = (
        session.execute(select).fetchone())
    return {
        'id': id,
        'created': created,
        'title': title,
        'slug': slug,
        'description': description,
        'ammount': ammount,
        'metadata': metadata,
    }
def payment_link(slug):
    try:
        link = session.query(payment_links_table).filter_by(slug=slug).one()
    except sqlalchemy.orm.exc.NoResultFound:
        abort(404)

    if request.method == 'POST':
        insert = sqlalchemy.insert(transactions_table).values(
            ammount=link.ammount,
            metadata=link.metadata,
        )
        session.execute(insert)
        return redirect(url_for('.confirmation', slug=slug))
    return render_template(
        "pay.html",
        link=link,
    )
예제 #18
0
def listar(nome_tabela):
    result = session.execute(nome_tabela.__table__.select())
    con = 1
    res = {}
    for row in result:
        res[con] = dict(row)
        con += 1
    return res
예제 #19
0
 def get(self, poll_id):
     poll = session.execute('SELECT options FROM polls WHERE id = :id', {
         'id': poll_id
     }).fetchone()
     if not poll:
         return 404, 404
     response = []
     for index, option in enumerate(poll[0].split(",")):
         votes = session.execute(
             'SELECT count(*), count(DISTINCT ip) FROM votes WHERE poll_id = :poll_id AND option=:option',
             {
                 'poll_id': poll_id,
                 'option': index
             }).fetchone()
         response.append({
             'name': option,
             'votes': votes[0] if votes else 0,
             'unique_votes': votes[1] if votes else 0
         })
     return response, 201
예제 #20
0
 def add_data(self):
     session.execute(
         "insert into books values(1, 'Sapiens', 'Harari', false);")
     session.execute(
         "insert into books values(2, 'Meluha', 'Amish', true);")
     session.execute(
         "insert into books values(3, 'Harry Potter', 'Rowling', true);")
     session.commit()
예제 #21
0
	def remember(self,keyvalue,response,added_by):
		phrase = self.get_phrase_by_text(keyvalue)
		try:
			phrase_id = phrase['id']
			# we have this phrase, check if we already have this response
			response = self.get_response_by_text(response, phrase_id)
			try:
				response_id = responses['id']
				return response_id
			except (AttributeError,KeyError):
				pass

		except (AttributeError,KeyError):
			phrase_id = str(uuid4())
			session.execute("INSERT INTO phrase (id,added,phrase,added_by) VALUES(?,?,?,?)",
				[phrase_id, datetime.now().isoformat(), keyvalue, added_by.name]
				)

		# insert the response
		response_id = str(uuid4())
		session.execute("INSERT INTO responses(id,phrase_id,response,added,added_by) VALUES(?,?,?,?,?)",
			[response_id, phrase_id, response, datetime.now().isoformat(), added_by.name]
			)
		return response_id
예제 #22
0
def generateClients(*args):
    count = None
    if len(args) == 0:
        count = inputCount()
    else:
        count = args[0]

    startTime = time.time()
    if len(args) == 0: print('\nRows generating...')

    session.execute(
        f"INSERT INTO \"Client\" (name, birthday_date, email) "
        f"SELECT generatestring(10), "
        f"generatedate(), "
        f"concat(generatestring(8), '@', generatestring(5), '.com') "
        f"from generate_series(1, {count})")
    session.commit()

    endTime = time.time()

    if len(args) == 0:
        print('Rows generated! Elapsed time: ' + str(endTime - startTime)[:9] +
              's')
        input('\nPress any key to continue...')
def get_csv_data(rich_transactions):

    column_names = _column_names(rich_transactions)
    subquery = _get_subquery(rich_transactions)

    results = session.execute(subquery.select())

    def datemaker(i):
        return (datetime.utcnow() -
                timedelta(seconds=(i * 2345))).strftime('%d %b %Y at %-I:%M%p')

    rows = [['Ammount', 'Date'] + column_names] + [[
        result.ammount,
        datemaker(index),
    ] + [dict(result).get(column_name) for column_name in column_names
         ] for index, result in enumerate(results.fetchall())]

    return "\n".join(",".join(str(column) for column in row) for row in rows)
def get_transactions_and_column_names(rich_transactions):

    column_names = _column_names(rich_transactions)
    subquery = _get_subquery(rich_transactions)

    results = session.execute(subquery.select())

    def datemaker(i):
        return (datetime.utcnow() -
                timedelta(seconds=(i * 2345))).strftime('%d %b %Y at %-I:%M%p')

    transactions = reversed([[
        {
            'text': '£{:,.2f}'.format(result.ammount)
        },
        {
            'text': datemaker(index)
        },
    ] + [{
        'html': dict(result).get(column_name),
        'classes': '' if dict(result).get(column_name) else 'none',
    } for column_name in column_names]
                             for index, result in enumerate(results.fetchall())
                             ])

    column_names = [
        {
            'text': 'Ammount'
        },
        {
            'text': 'Date'
        },
    ] + [{
        'text': column_name
    } for column_name in column_names]

    return transactions, column_names
def populate():
    for ammount, metadata in ([
        (100, {
            'post': 'Bangkok',
            'fee type': 'Fee 19',
            'country': 'Thailand'
        }),
        (100, {
            'post': 'Bangkok',
            'fee type': 'Fee 19',
            'country': 'Thailand'
        }),
        (100, {
            'post': 'Phuket',
            'fee type': 'Fee 19',
            'country': 'Thailand'
        }),
        (55, {
            'post': 'Phuket',
            'fee type': 'Fee 4',
            'country': 'Thailand'
        }),
        (30, {
            'post': 'Phuket',
            'fee type': 'Fee 6',
            'country': 'Thailand'
        }),
        (100, {
            'post': 'Paris',
            'fee type': 'Fee 19',
            'country': 'France'
        }),
        (100, {
            'post': 'Marseille',
            'fee type': 'Fee 19',
            'country': 'France'
        }),
        (100, {
            'post': 'Ibiza',
            'fee type': 'Fee 19',
            'country': 'Spain'
        }),
        (100, {
            'post': 'Ibiza',
            'fee type': 'Fee 19',
            'country': 'Spain'
        }),
        (100, {
            'post': 'Ibiza',
            'fee type': 'Fee 19',
            'country': 'Spain'
        }),
        (55, {
            'post': 'Ibiza',
            'fee type': 'Fee 4',
            'country': 'Spain'
        }),
        (100, {
            'post': 'Barcelona',
            'fee type': 'Fee 19',
            'country': 'Spain'
        }),
    ] * 3) + [
        (100, {
            'post': 'Washington',
            'fee type': 'Fee 19',
            'country': 'USA'
        }),
        (55, {
            'post': 'Boston',
            'fee type': 'Fee 4',
            'country': 'USA'
        }),
        (100, {
            'post': 'Montreal',
            'fee type': 'Fee 19',
            'country': 'Canada'
        }),
    ]:
        insert = sqlalchemy.insert(transactions_table).values(
            ammount=ammount,
            metadata=json.dumps(metadata),
        )
        session.execute(insert)
    return redirect(url_for('transactions.transactions_index'))
예제 #26
0
 def delete_keys(cls, device_auth_data_id, keys):
     delete_query = cls.__table__.delete().\
         where(cls.device_auth_data_id == device_auth_data_id).\
         where(cls.app_key_hex.in_(keys))
     session.execute(delete_query)
def foo():
    session.execute('SELECT 1')
    return "ok"
예제 #28
0
	def get_response(self,response_id):
		session.execute("SELECT * FROM response WHERE response_id=?",[response_id])
		return self.zip_response(session.fetchone())
예제 #29
0
	def __init__(self,history=50):
		self.history_max_lines = int(history)

		# init history
		session.execute('SELECT * FROM history LIMIT %d ORDER BY added DESC', self.history_max_lines)
		self.history = session.fetchall().reverse() # reverse so we can append rather than insert
예제 #30
0
	def get_phrase_by_text(self,text):
		session.execute("SELECT * FROM phrase WHERE phrase=?",[text])
		return self.zip_phrase(session.fetchone())
예제 #31
0
 def tearDown(self):
     session.execute("drop table books;")
     session.commit()
예제 #32
0
	def get_response_by_text(self,text,phrase_id):
		session.execute("SELECT * FROM response WHERE response=? AND phrase_id=?",[text,phrase_id])
		return self.zip_response(session.fetchone())
예제 #33
0
# iterate over all the word categories and load up each yml file
words = {}
for file_name in listdir('./better_words/words'):
    category, extension = splitext(file_name)
    if extension == '.yml':
        with open('./better_words/words/' + file_name, 'r') as stream:
            words[category] = yaml.load(stream)

words = parse_yaml(words)

# Purge old word data from the DB
session.query(Category).delete()
session.query(Word).delete()
session.query(Suggestion).delete()
session.query(Link).delete()
session.execute('DELETE FROM link_to_word_associations')
session.execute('DELETE FROM suggestion_to_word_associations')

for category, value in words._asdict().iteritems():
    # Create the category, and store its response
    c = Category(slug=category, response=value.response)
    session.add(c)

    # Create each word entry for within the category
    for entry in value.entries:
        prefix = category + '_' + entry.type + '_'

        suggestion_models = []
        for suggestion in entry.suggestions:
            model = Suggestion(id=create_id(suggestion, prefix),
                               word=suggestion)
예제 #34
0
def init_postgis(tmpdir, dataset, key= None):
    print("INITIAILIZING POSTGIS SQL")
    nm = dataset["dataset"]
    dsid = getDatasetId(nm)
    dsname = dsid

    # session.execute(f"UPDATE segment SET hull=null WHERE segment.dsid= {dsid}")
    # session.execute(f"UPDATE segment SET kde_density=null WHERE segment.dsid= {dsid}")

    # cells = sq(Segment).filter(Segment.dsid==dsname).filter(Segment.n_umis>20)
    # valid_cells = pd.Series(cells,index =[ c.id for c in cells])
    # def make_convex_hull_safe(x):
    #     if len(x.umis)<3:
    #         return None
    #     try:
    #         return sp_spatial.ConvexHull([[u.x,u.y]  for u in x.umis] )
    #     except Exception as e:
    #         return None
    # hulls = valid_cells.apply(make_convex_hull_safe)
    # cells_and_hulls = pd.concat([valid_cells.rename("cell"),hulls.rename("hull")],axis=1)

    # for k,r in cells_and_hulls.iterrows():

    #     hull=r.hull
    #     points = hull.points
    #     print(points)
    #     indices = hull.simplices
    #     poly_string = "POLYGON(("+", ".join([f"{p[0]} {p[1]}" for p in hull.points[np.concatenate([hull.vertices,[hull.vertices[0]]])]])+"))"
    #     r.cell.hull = poly_string
    #     session.add(r.cell)
    

    raw_sql = text("""
UPDATE umi 
SET xumi_xy=ST_SetSRID(ST_MakePoint(x, y),4326) 
FROM segment
WHERE segment.id = umi.seg
AND umi.dsid=:name;


UPDATE segment SET hull = ch.hull
FROM(
SELECT umi.seg as seg, ST_ConvexHull(ST_Collect(xumi_xy))  as hull
FROM umi JOIN segment ON segment.id= umi.seg
WHERE segment.n_umis>20 
AND segment.dsid=:name
GROUP BY umi.seg 
) as ch
WHERE ch.seg = segment.id
AND segment.dsid=:name;
""").params(name=dsid)
    session.execute(raw_sql)




    def array2raster(newRasterfn,rasterOrigin,pixelWidth,pixelHeight,array):

        cols = array.shape[1]
        rows = array.shape[0]
        originX = rasterOrigin[0]
        originY = rasterOrigin[1]

        driver = gdal.GetDriverByName('GTiff')
        outRaster = driver.Create(newRasterfn, cols, rows, 1, gdal.GDT_Byte)
        outRaster.SetGeoTransform((originX, pixelWidth, 0, originY, 0, pixelHeight))
        outband = outRaster.GetRasterBand(1)
        outband.WriteArray(array)
        outRasterSRS = osr.SpatialReference()
        outRasterSRS.ImportFromEPSG(4326)
        outRaster.SetProjection(outRasterSRS.ExportToWkt())
        outband.FlushCache()
        
    cells = sq(Segment).filter(Segment.dsid==dsname).filter(Segment.n_umis>20)     
    for i,c in enumerate(cells):
        #f c.id != 300228: continue
        #c0 = c
        print("SETTING")
        coords = np.array([(u.x,u.y) for u in c.umis]).T
        points_kde =  stats.gaussian_kde(coords)
        sf = 20
        r = max(c.eval0, c.eval1)*sf
        
        n = 50
        
        h = c.hull
        if type(h) == type("STR"):
            h = WKBElement(h)

    
        pts = to_shape(h).exterior.xy
        pts_df = pd.DataFrame(pts).T.rename({0:"x",1:"y"},axis="columns")    
        

        xrange=[pts_df.x.min(), pts_df.x.max()]
        yrange=[pts_df.y.min(), pts_df.y.max()]
        
        X,Y = np.meshgrid(np.linspace(xrange[0],xrange[1],n),np.linspace(yrange[0],yrange[1],n))
        grid =np.vstack([X.ravel(), Y.ravel()]).T
        vals = points_kde.evaluate(grid.T)
        vsquare = vals.reshape(n,n)

        array =( vsquare / np.max(vsquare) * 255).astype(int)
        reversed_arr = array[::-1] # reverse array so the tif looks like the array


        rasterOrigin = (xrange[0], yrange[0])
        pixelWidth = (xrange[1] - xrange[0]) / n
        pixelHeight = (yrange[1] - yrange[0]) / n
        
        newRasterfn = 'test.tif'
        array2raster(newRasterfn,rasterOrigin,pixelWidth,pixelHeight,array) # convert array to raster

        hexbits = open(newRasterfn,"rb").read().hex()
        sub = r"\x" + f"{hexbits}"
        query = f"""UPDATE segment SET kde_density=ST_FromGDALRaster('{sub}') WHERE id={c.id}"""
        session.execute(query)
    


    session.commit()

    raw_sql = text("""

UPDATE umi
SET kde_val= ST_NearestValue(kde_density, ST_SetSRID(umi.xumi_xy,4326 ))
FROM segment 
WHERE segment.id = umi.seg
AND umi.dsid=:name;

UPDATE segment SET hull1 = ch.hull
FROM(
SELECT umi.seg as seg, ST_ConvexHull(ST_Collect(xumi_xy))  as hull
FROM umi JOIN segment ON segment.id= umi.seg
WHERE segment.kde_density is not null 
AND umi.kde_val >= 1
AND segment.dsid=:name
GROUP BY umi.seg 
) as ch
WHERE ch.seg = segment.id
AND segment.dsid=:name;

UPDATE segment SET hull128 = ch.hull
FROM(
SELECT umi.seg as seg, ST_ConvexHull(ST_Collect(xumi_xy))  as hull
FROM umi JOIN segment ON segment.id= umi.seg
WHERE segment.kde_density is not null 
AND umi.kde_val >= 128
AND segment.dsid=:name
GROUP BY umi.seg 
) as ch
WHERE ch.seg = segment.id
AND segment.dsid=:name;

UPDATE segment SET hull12 = ch.hull
FROM(
SELECT umi.seg as seg, ST_ConvexHull(ST_Collect(xumi_xy))  as hull
FROM umi JOIN segment ON segment.id= umi.seg
WHERE segment.kde_density is not null 
AND umi.kde_val >= 12
AND segment.dsid=:name
GROUP BY umi.seg 
) as ch
WHERE ch.seg = segment.id
AND segment.dsid=:name;

UPDATE segment SET center = ch.centroid
FROM(
SELECT umi.seg as seg, ST_Centroid(ST_Collect(xumi_xy))  as centroid
FROM umi JOIN segment ON segment.id= umi.seg
WHERE segment.kde_density is not null 
AND segment.dsid=:name
GROUP BY umi.seg 
) as ch
WHERE ch.seg = segment.id
AND segment.dsid=:name;

UPDATE segment SET area12 = ST_Area(hull12);


update segment set rval=(5915587277.0*id)%255/256 WHERE segment.dsid=:name;
update segment set gval=(5915587277.0*id)/256%255/256 WHERE segment.dsid=:name;
update segment set bval=(5915587277.0*id)/256/256%255/256 WHERE segment.dsid=:name;



""").params(name=dsid)
    session.execute(raw_sql)

    raw_sql2 = text("""

UPDATE dataset SET raster_2k_red = ST_MapAlgebra(
    ST_AddBand(
        ST_MakeEmptyRaster(2000,2000, -20, -20, .02, .02, 0, 0, 4326),
        1, '8BUI'::text, 5, 0), 
    images.union_img, '[rast1]*0+[rast2]', null, 'UNION')
FROM (
    SELECT rs.dsid as dsid, ST_Union(resampled, 'SUM' ) as union_img
        FROM  (
            SELECT  ST_MapAlgebra(
                ST_Resample(kde_density, ST_MakeEmptyRaster(2000,2000, -20, -20, .02, .02, 0, 0, 4326),'Bilinear'),
                1,null,FORMAT('[rast] * %s ',segment.rval)
            )  AS resampled,
            dsid
        FROM segment 
        WHERE segment.kde_density IS NOT null
        AND segment.dsid=:name
            )  AS rs
        GROUP BY rs.dsid
) AS images
WHERE images.dsid = dataset.id
AND dataset.id=:name;



UPDATE dataset SET raster_2k_green = ST_MapAlgebra(
    ST_AddBand(
        ST_MakeEmptyRaster(2000,2000, -20, -20, .02, .02, 0, 0, 4326),
        1, '8BUI'::text, 5, 0), 
    images.union_img, '[rast1]*0+[rast2]', null, 'UNION')
FROM (
    SELECT rs.dsid as dsid, ST_Union(resampled, 'SUM'  ) as union_img
        FROM  (
            SELECT  ST_MapAlgebra(
ST_Resample(kde_density, ST_MakeEmptyRaster(2000,2000, -20, -20, .02, .02, 0, 0, 4326),'Bilinear'),
                1,null,FORMAT('[rast] * %s ',segment.gval)
            )  AS resampled,
            dsid
        FROM segment 
        WHERE segment.kde_density IS NOT null
        AND segment.dsid=:name
            )  AS rs
        GROUP BY rs.dsid
) AS images
WHERE images.dsid = dataset.id
AND dataset.id=:name;





UPDATE dataset SET raster_2k_blue = ST_MapAlgebra(
    ST_AddBand(
        ST_MakeEmptyRaster(2000,2000, -20, -20, .02, .02, 0, 0, 4326),
        1, '8BUI'::text, 5, 0), 
    images.union_img, '[rast1]*0+[rast2]', null, 'UNION')
FROM (
    SELECT rs.dsid as dsid, ST_Union(resampled, 'SUM'  ) as union_img
        FROM  (
            SELECT  ST_MapAlgebra(
ST_Resample(kde_density, ST_MakeEmptyRaster(2000,2000, -20, -20, .02, .02, 0, 0, 4326),'Bilinear'),
                1,null,FORMAT('[rast] * %s ',segment.bval)
            )  AS resampled,
            dsid
        FROM segment 
        WHERE segment.kde_density IS NOT null
        AND segment.dsid=:name
            )  AS rs
        GROUP BY rs.dsid
) AS images
WHERE images.dsid = dataset.id
AND dataset.id=:name;





UPDATE dataset SET raster_2k_all = 
       ST_AddBand( ST_AddBand( 
            raster_2k_red,raster_2k_green), raster_2k_blue)
            WHERE dataset.id=:name;

""").params(name=dsid)
    session.execute(raw_sql2)

    raw_sql3 = text("""

UPDATE segment 
SET points = sq.new_geo
FROM (
    SELECT segment.id as seg_id, ST_Collect(umi.xumi_xy) as new_geo
    FROM segment JOIN umi ON umi.seg = segment.id
    WHERE segment.dsid=:name
    GROUP BY segment.id
)  AS sq
WHERE segment.id=sq.seg_id;

UPDATE segment 
SET points_xym_total_reads = sq.new_geo
FROM (
    SELECT segment.id AS seg_id, ST_Collect(ST_MakePointM(ST_X(umi.xumi_xy),ST_Y(umi.xumi_xy),umi.total_reads)) as new_geo
    FROM segment JOIN umi ON umi.seg = segment.id
    WHERE segment.dsid=:name
    GROUP BY segment.id
) AS sq
WHERE segment.id = sq.seg_id;

UPDATE segment 
SET points_xym_kde = sq.new_geo
FROM (
    SELECT segment.id AS seg_id, ST_Collect(ST_MakePointM(ST_X(umi.xumi_xy),ST_Y(umi.xumi_xy),umi.kde_val)) as new_geo
    FROM segment JOIN umi ON umi.seg = segment.id
    WHERE segment.dsid=:name
    GROUP BY segment.id
) AS sq
WHERE segment.id = sq.seg_id;

""").params(name=dsid)
    session.execute(raw_sql3)

    raw_sql4 = text("""

UPDATE segment 
SET points12 = sq.new_geo
FROM (
    SELECT segment.id as seg_id, ST_Collect(umi.xumi_xy) as new_geo
    FROM segment JOIN umi ON umi.seg = segment.id
    WHERE umi.kde_val > 12
    AND segment.dsid=:name
    GROUP BY segment.id
)  AS sq
WHERE segment.id=sq.seg_id;



UPDATE segment 
SET points128 = sq.new_geo
FROM (
    SELECT segment.id as seg_id, ST_Collect(umi.xumi_xy) as new_geo
    FROM segment JOIN umi ON umi.seg = segment.id
    WHERE umi.kde_val > 128
    AND segment.dsid=:name
    GROUP BY segment.id
)  AS sq
WHERE segment.id=sq.seg_id;


""").params(name=dsid)
    session.execute(raw_sql4)

    session.commit()


    def alpha_shape(points, alpha):
        """
        Compute the alpha shape (concave hull) of a set
        of points.
        @param points: Iterable container of points.
        @param alpha: alpha value to influence the
            gooeyness of the border. Smaller numbers
            don't fall inward as much as larger numbers.
            Too large, and you lose everything!
        """
        if len(points) < 4:
            # When you have a triangle, there is no sense
            # in computing an alpha shape.
            return geometry.MultiPoint(list(points)).convex_hull

        coords = np.array([point.coords[0] for point in points])
        tri = Delaunay(coords)
        triangles = coords[tri.vertices]
        a = ((triangles[:,0,0] - triangles[:,1,0]) ** 2 + (triangles[:,0,1] - triangles[:,1,1]) ** 2) ** 0.5
        b = ((triangles[:,1,0] - triangles[:,2,0]) ** 2 + (triangles[:,1,1] - triangles[:,2,1]) ** 2) ** 0.5
        c = ((triangles[:,2,0] - triangles[:,0,0]) ** 2 + (triangles[:,2,1] - triangles[:,0,1]) ** 2) ** 0.5
        s = ( a + b + c ) / 2.0
        areas = (s*(s-a)*(s-b)*(s-c)) ** 0.5
        circums = a * b * c / (4.0 * areas)
        filtered = triangles[circums < (1.0 / alpha)]
        edge1 = filtered[:,(0,1)]
        edge2 = filtered[:,(1,2)]
        edge3 = filtered[:,(2,0)]
        edge_points = np.unique(np.concatenate((edge1,edge2,edge3)), axis = 0).tolist()
        m = geometry.MultiLineString(edge_points)
        triangles = list(polygonize(m))
        return cascaded_union(triangles), edge_points



    for i,c in enumerate(sq(Segment).filter(Segment.dsid== dsid).filter(Segment.n_umis>20).all()):

        shp = to_shape(c.points)
        alpha = .1/np.mean(np.var(np.array([p.coords[0] for p in shp]).T,1))**.5
        if i %20 == 0:
            print(i,alpha)
            
        concave_hull, edge_points = alpha_shape(shp, alpha=alpha)
        if concave_hull.geom_type == 'MultiPolygon':
            concave_hull = max(concave_hull, key=lambda a: a.area)

        wkb_element = from_shape(concave_hull)
        c.hull = wkb_element

        
        shp = to_shape(c.points12)
        alpha = .5/np.mean(np.var(np.array([p.coords[0] for p in shp]).T,1))**.5
        concave_hull, edge_points = alpha_shape(shp, alpha=alpha)
        if concave_hull.geom_type == 'MultiPolygon':
            concave_hull = max(concave_hull, key=lambda a: a.area)

        wkb_element = from_shape(concave_hull)
        c.hull12 = wkb_element
            
        shp = to_shape(c.points128)
        alpha = .5/np.mean(np.var(np.array([p.coords[0] for p in shp]).T,1))**.5
        concave_hull, edge_points = alpha_shape(shp, alpha=alpha)
        if concave_hull.geom_type == 'MultiPolygon':
            concave_hull = max(concave_hull, key=lambda a: a.area)

        wkb_element = from_shape(concave_hull)
        c.hull128 = wkb_element
        
        session.add(c)
    #pTODO] PUT BACK IN COMMITS WHEN THIS CODE IS READY TO RUN!
    #session.commit()










    return 0


    
예제 #35
0
	def get_phrase(self,phrase_id):
		session.execute("SELECT * FROM phrase WHERE id=?",[phrase_id])
		return self.zip_phrase(session.fetchone())