[GIS] Performing spatial join in Spatialite (QspatiaLite for QGIS)

qspatialitequeryspatial-joinspatialite

I'm trying to spatially join and summarize the attributes of a set of buildings to a set of containing parcels. There are (potentially) many buildings to each one parcel.

Here is the query as I'm attempting to run it:

SELECT "MF_with_Zones_atts".'Geometry' AS Geometry,
"MF_with_Zones_atts".'PKUID' AS ID,
"Apt_Bldg_Footprints".'STATE_ID' AS STATE_ID,
"MF_with_Zones_atts".'JURIS_CITY' AS JURISDICTION,
"MF_with_Zones_atts".'MAIL_CITY' AS CITY,
"MF_with_Zones_atts".'STATE' AS STATE,
"MF_with_Zones_atts".'ZIPCODE' AS ZIP,
"MF_with_Zones_atts".'COUNTY' AS COUNTY,
"MF_with_Zones_atts".'UNITS' AS UNITS,
"MF_with_Zones_atts".'UNIT_TYPE' AS UNIT_TYPE,
"MF_with_Zones_atts".'ZONE' AS ZONE,
"MF_with_Zones_atts".'CMP_' AS CMP,
"MF_with_Zones_atts".'OVRLY' AS OVERLAY,
"Apt_Bldg_Footprints".'BLDG_USE' as BLDG_USE,
"MF_with_Zones_atts".'ZoneMaxDU' as ZoneMaxDU,
"MF_with_Zones_atts".'ZoneMaxFAR' as ZoneMaxFAR,

count("Apt_Bldg_Footprints".'ROWID' AS BLDG_COUNT,

sum("Apt_Bldg_Footprints".'BLDG_SQFT') as BLDG_SQFT,
sum("Apt_Bldg_Footprints".'UNITS_RES') as BLDG_UNITS_RES,
sum("Apt_Bldg_Footprints".'Bldg_Ftprt') as BLDG_FOOTPRINT,
max("Apt_Bldg_Footprints".'NUM_STORY') as NUM_STORY,
max("Apt_Bldg_Footprints".'AVG_HEIGHT') as AVG_HEIGHT,
max("Apt_Bldg_Footprints".'MAX_HEIGHT') as MAX_HEIGHT,

FROM "MF_with_Zones_atts", "Apt_Bldg_Footprints"
WHERE Contains("MF_with_Zones_atts", "Apt_Bldg_Footprints")
GROUP BY "MF_with_Zones_atts".'ADDRESS'
ORDER BY "MF_with_Zones_atts".'ADDRESS'

The error messages I get are:

First:

"The SQL query seems to be invalid. near "AS": syntax error

Then:

Unable to read temp table: MF_w_Footprints.tmp Operation cancelled

So, the question is, what am I doing wrong?

Both layers have been imported into a new sqlite database as geometry tables, and I'm using the QspatiaLite plugin for QGis to try to make this happen.

Best Answer

In addition to NathanW's comment, the Contains expression must use the geometry columns:

WHERE ST_Contains("MF_with_Zones_atts".geometry, "Apt_Bldg_Footprints".geometry)
Related Question