2

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.

Improve this question
2
  • 1
    count("Apt_Bldg_Footprints".'ROWID' AS BLDG_COUNT, is missing a closing )
    – Nathan W
    Commented Jun 4, 2015 at 23:47
  • 1
    Single quotes are allowed for identifiers when the context dictates that a literal could not occur, but it might be good to get out of the habit of using them. Use double quotes or bare words instead. Single quotes won't work with PostgreSQL, for example. (When I looked at your question, I didn't realize SQLite allowed them and thought, "how can this work at all?")
    – Rob Skelly
    Commented Dec 6, 2016 at 21:55

2 Answers 2

Reset to default
1

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)
Improve this answer
3
  • I've corrected the code based on your feedback, and am still getting errors. 1st error: --- The SQL query seems to be invalid. near "FROM": syntax error ---
    – G-spatial
    Commented Jun 8, 2015 at 17:41
  • Can you copy/paste your full SQL command here? Also, I see an extra comma at the end of the last "max(...)" line: max("Apt_Bldg_Footprints".'MAX_HEIGHT') as MAX_HEIGHT, <= remove this comma.
    – Micha
    Commented Jun 9, 2015 at 7:01
  • 1
    That must have been it, that extra comma. The query appears to have run successfully now. Thanks everybody for your help!
    – G-spatial
    Commented Jun 9, 2015 at 22:29
0 
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".geometry, "Apt_Bldg_Footprints".geometry)
GROUP BY "MF_with_Zones_atts".'ADDRESS'
ORDER BY "MF_with_Zones_atts".'ADDRESS'
Improve this answer

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.