cholmes/fiboa

1import streamlit as st2from langchain_openai import ChatOpenAI 3from langchain_community.llms import Ollama4from langchain_community.utilities import SQLDatabase5from langchain.chains import create_sql_query_chain6import geopandas as gpd7 8 9import ibis10from ibis import _11geoparquet = "https://data.source.coop/fiboa/france-ec/zstd-france_eurocrops_2018_fiboa.parquet"12con = ibis.duckdb.connect("duck.db", extensions = ["spatial"])13#con.raw_sql(f'CREATE OR REPLACE VIEW crops AS SELECT *, ST_GEOMFROMWKB(geometry) AS "geometry" FROM read_parquet("{geoparquet}")')14crops = con.read_parquet(geoparquet, "crops").cast({"geometry": "geometry"})15# df = crops.to_pandas()16 17# +18# df = crops.to_pandas()19 20# +21#gdf = gpd.read_parquet("be_vlg.parquet")22#gdf.crs23# -24 25st.set_page_config(26    page_title="fiboa chat tool",27    page_icon="🦜",28)29st.title("FiobaGPT Prototype")30 31# +32# from langchain.chains.sql_database.prompt import PROMPT # peek at the default33from langchain_core.prompts.prompt import PromptTemplate34 35new_prompt = PromptTemplate(input_variables=['dialect', 'input', 'table_info', 'top_k'], 36                        template=37'''38Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query39and return the answer. Only limit for {top_k} when asked for "some" or "examples". 40 41This duckdb database includes full support for spatial queries, so it will understand most PostGIS-type42queries as well.  Remember that you must cast blob column to a geom type using ST_GeomFromWKB(geometry) AS geometry43before any spatial operations. Do not use ST_GeomFromWKB for non-spatial queries.44 45 46If you are asked to "map" or "show on a map", then be select the "geometry" column in your query.47If asked to show a "table", you must not include the "geometry" column from the query results.  48 49Use the following format: return only the SQLQuery to run. DO NOT use the prefix with "SQLQuery:".  50Do not include an explanation.  51 52Pay close attention to use only the column names that you can see in the schema description. Be careful to53not query for columns that do not exist. Also, pay attention to which column is in which table.54 55Tables include {table_info}. The data you should use always comes from the table called "crops".56Only use that table, do not use the "testing" table. Pay close attention to this table schema.57 58Question: {input}59'''60)61# -62 63llm = ChatOpenAI(model="gpt-4o-mini", temperature=0, api_key=st.secrets["OPENAI_API_KEY"])64 65# +66# Create the SQL query chain with the custom prompt67db = SQLDatabase.from_uri("duckdb:///duck.db", view_support=True)68chain = create_sql_query_chain(llm, db, prompt=new_prompt, k= 11)69 70## testing71#user_input = "Show on a map the 10 largest fields?"72#sql_query = chain.invoke({"question": user_input})73#print(sql_query)74# 75 76 77# -78 79 80 81 82# +83import geopandas as gpd84from ibis import _85import re86import leafmap.maplibregl as leafmap87m = leafmap.Map()88 89def as_geopandas(response):90    response = re.sub(";$", "", response)91    sql_query = f"CREATE OR REPLACE VIEW testing AS ({response})"92    con.raw_sql(sql_query)93    gdf = con.table("testing")94    if 'geometry' in gdf.columns:95        gdf = (gdf96               .cast({"geometry": "geometry"})97               .mutate(geometry = _.geometry.convert("EPSG:31370", "EPSG:4326"))98               .to_pandas()99              ).set_crs(epsg=4326, inplace=True) 100        return gdf101    return gdf.to_pandas()102 103 104# -105 106response = "SELECT geometry, area FROM crops ORDER BY area DESC LIMIT 10;"107as_geopandas(response)108#if 'geometry' in gdf.columns:109#    m.add_gdf(gdf)110#    m111#gdf112 113# +114'''115Ask me about fiboa data! Request "a map" to get map output, or table for tabular output, e.g.116 117- "Show a map with the 10 largest fields"118- "Show a table of the total area by crop typology"119- "Compute the perimeters of all fields and determine which have the longest"120 121'''122 123example = "Which are the 10 largest fields?"124with st.container():  125    if prompt := st.chat_input(example, key="chain"):126        st.chat_message("user").write(prompt)127        with st.chat_message("assistant"):128            response = chain.invoke({"question": prompt})129            st.write(response)130            gdf = as_geopandas(response)131            if 'geometry' in gdf.columns:132                m.add_gdf(gdf)133                m.to_streamlit()134            else:135                st.dataframe(gdf)136 137# +138st.divider()139 140'''141Data sources: https://beta.source.coop/fiboa/be-vlg142Software License: BSD143 144'''145