1

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

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

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"
| improve this answer | |
  • 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 at 5:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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