p23

fiboa/app.py

@@ -42,7 +42,7 @@ and return the answer. Only limit for {top_k} when asked for "some" or "examples
 This duckdb database includes full support for spatial queries, so it will understand most PostGIS-type
 queries as well.  Remember that you must cast blob column to a geom type using ST_GeomFromWKB(geometry) AS geometry
 before any spatial operations. Do not use ST_GeomFromWKB for non-spatial queries.
-If you are asked to "map" or "show on a map", then be sure to select the "geometry", "area", and "crop" columns in your query.
+If you are asked to "map" or "show on a map", then be select the "geometry" column in your query.
 If asked to show a "table", you must not include the "geometry" column from the query results.
 
 Use the following format: return only the SQLQuery to run. DO NOT use the prefix with "SQLQuery:".  
@@ -59,7 +59,7 @@ There is no other column related to area information, especially not total_area
 If you need to compute the total area, do it manually, with a SUM of the area column. You should always use the 'area' column - never use a 'total_area' column.
 The column "perimeter" is in the unit meters, you may need to convert it to other units, e.g. kilometers.
 The column "collection" contains the country codes for the Baltic states:
-"ec_lv" for Latvia, "ec_lt" for Lithuania, "ec_es" for Estonia.
+"ec_lt" for Latvia, "ec_lv" for Lithuania, "ec_es" for Estonia.
 Be sure to always include the collection with the right country for any query about a specific country, including it in the WHERE clause.
 
 If the user asks for 'percent' of crops or fields for one of the countries you must always calculate the percentage manually, by summing up the area manually. You total number of hectares to calculate the percentage from is 1583923 for Lithuania,  1788859 for latvia and  973945 for Estonia. If they don't specify a country use 4346727. If you use one of these be sure to always include the right collection in the where clause.
@@ -94,15 +94,15 @@ chain = create_sql_query_chain(llm, db, prompt=new_prompt, k=100)
 Ask me about fiboa data (here: all baltic states)!
 Request "a map" to get map output, or table for tabular output, e.g.
 
-- Show a table of the top ten crops in the Baltics.
-- Show a map with the 10 largest sugar beet fields.
-- What is the percent of oats in each country?
+- Show a map with the 10 largest sugar beet fields
 - Show a map with the largest field in Estonia
-- What are the quantiles of field size for Latvia?
+- Show a table of the top ten crops
+- What are the top ten crops that have a field size over 10 hectares?
+- Compute the total area of all fields in km² and compute the percentage the total area of the baltic states (175015 km²)
 
 '''
 
-example = "How many berry fields are there in each country?"
+example = "Which are the 10 largest fields?"
 with st.container():  
     if prompt := st.chat_input(example, key="chain"):
         st.chat_message("user").write(prompt)

fiboa/query.py

@@ -8,7 +8,7 @@ from ibis import _
 
 def execute_prompt(con, chain, prompt):
     response = chain.invoke({"question": prompt})
-    st.write(response.replace("testing", "crops"))
+    st.write(response)
     gdf = as_geopandas(con, response)
 
     if len(gdf) == 0:
@@ -28,8 +28,7 @@ def execute_prompt(con, chain, prompt):
         })
 
 def as_geopandas(con, response):
-    #import code; code.interact(local=locals())
-    response = re.sub(";$", "", response).replace("testing", "crops")
+    response = re.sub(";$", "", response)
     sql_query = f"CREATE OR REPLACE VIEW testing AS ({response})"
     con.raw_sql(sql_query)
     gdf = con.table("testing")
@@ -48,4 +47,4 @@ def as_geopandas(con, response):
         if dtype.startswith("datetime64"):
             gdf[col] = gdf[col].astype(str)
     
-    return gdf
+    return gdf

questions.md

@@ -5,7 +5,7 @@
 * What's the total area of devoted to agriculture in Latvia? 
 * Is the average field size larger in lithuania or latvia?
 * How many fields are there that are under 1 hectare? 
-* Show a map with the 10 largest fields
+* Show a map with the 10 largestfields
 * What percent of fields are under 2 hectares?
 * Show a map with the largest field in Estonia
 * Show a map with the ten largest fields
@@ -18,6 +18,8 @@
 
 
 
+
+
 ## More coding / prompting needed 
 
 ### Maps with more