SQL Query error: ambiguous column name geometry

Question

When I try a simple query

SELECT "Water_Service_Area_Boundaries_Non_Cadastral"."Network", count(*), sum(st_length(geometry))
FROM "W_Mains_DSC_ExclAbandoned", "Water_Service_Area_Boundaries_Non_Cadastral"
WHERE ST_Intersects("Water_Service_Area_Boundaries_Non_Cadastral", "W_Mains_DSC_ExclAbandoned")
GROUP BY "Water_Service_Area_Boundaries_Non_Cadastral"."Network"

I get this error

Query preparation error on PRAGMA table_info(_tview): ambiguous column name: geometry

When I try it without the st_length it works to create a simple group with counts.

Also just the following works fine

 Select sum(st_length(geometry)), count(*)
 From W_Mains_DSC_ExclAbandoned

Examples of the subset of the query that work independently but not when strung together

Just the total length

example1

Just the group

example2

When I simply add the st_length

example3

I have tried with .geometry in the ST_Intersects and this gives correct counts but gives the pragma geometry error when the sum is added

example4

• Mapperz: ST_Length requires projection information (SRID) see gis.stackexchange.com/questions/35462/…
• she_weeds: Your first query references two tables with the column geometry. You need to specify which geometry column you want to perform ST_length() on. Just like you specified the table for the Network column
• GeorgeC: What I don't understand then is why these 2 queries with the issues work when run by themselves. I'll add screen grabs to the question.
• GeorgeC: @she_weeds sorry I just realised that without the .geometry it wasn't giving an error but also wasn't giving a proper value for the counts so the intersect wasn't working. The issue with running the length of the lines in the network area still gives the same issue though.

Answer 1

In my opinion you do not need any SRID because you are not working with ST_Length_Spheroid().

What @she_weeds is pointing on is indeed correct.

SELECT ws."Network", count(ws."Network"), sum(st_length(ws.geometry))
FROM "Water_Service_Area_Boundaries_Non_Cadastral" AS ws, "W_Mains_DSC_ExclAbandoned" AS wmain
WHERE ST_Intersects(ws.geometry, wmain.geometry)
GROUP BY ws."Network"

• GeorgeC: Thanks -so it needed to know that it's the geometry of one of the input datasets we need the length for.