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.

  • 1
    count("Apt_Bldg_Footprints".'ROWID' AS BLDG_COUNT, is missing a closing ) – Nathan W Jun 4 '15 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 Dec 6 '16 at 21:55
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)
  • 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 Jun 8 '15 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 Jun 9 '15 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 Jun 9 '15 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'

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.