Spatial joins syntax question

[pdickson2]

Hello. Casey and I are attempting to sum buildings by subward from our building layer. We found a similar script from a post gis forum for calculating racial data. Here is what we've put into the DB Manager:

SELECT builds.subward as sw, sum(builds.flood) as floods, count(*) from builds.osm_id as build FROM builds as builds Join subwards as subward_id ON st_intersects(builds.way, subward_id.geom) GROUP BY subward

We're getting an error with the 'from' line relating to its syntax. We've played around with it but aren't sure what the issues is. Please help.

[josephholler]

I think you're getting the particular error about from because there are two from clauses. Try replacing count(*) from builds.osm_id as build with count(builds.osm_id) as buildcnt

But in the bigger picture, it looks like the goal is to count how many buildings are in each subward. A few observations:

• It's more efficient to figure out if a point is inside a subward than a polgon, so converting buildings to points with st_centroid() will speed things up.
• It may be easier to think of adding a column to buildings to indicate which subward each building belongs to. Then create a view of buildings that groups buildings by subward. Then join that information back to subwards via attributes.