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


Just the group


When I simply add the st_length


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


  • ST_Length requires projection information (SRID) see gis.stackexchange.com/questions/35462/…
    – Mapperz
    May 13, 2020 at 1:43
  • 4
    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
    – she_weeds
    May 13, 2020 at 1:49
  • 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
    May 13, 2020 at 3:52
  • @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.
    – GeorgeC
    May 13, 2020 at 4:26

1 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"
  • Thanks -so it needed to know that it's the geometry of one of the input datasets we need the length for.
    – GeorgeC
    May 13, 2020 at 5:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.