2

I am having issues with what seems to be a very simple spatial query with a sub-query in Spatialite. I am trying to find all the buildings (poly) that intersect with a service area (poly), but only buildings that have a specific subtype code and with stories greater than a value (see query below). I am assuming it is a syntax error, but cannot seem to figure it.

SELECT b.pkid, b.subtype, b.stories
FROM bldg AS b, service_area AS a
  (SELECT b.subtype, b.stories
   FROM bldg
   WHERE subtype IN ('x', 'y', 'z') AND stories > 16)
WHERE ST_Intersects(b.geometry, a.geometry)

ERROR

Query execution error on CREATE TEMP VIEW _tview AS SELECT b.pkid, b.subtype, b.stories
FROM bldg AS b, service_area AS a
  (SELECT b.subtype, b.stories
   FROM bldg
   WHERE subtype IN ('x', 'y', 'z') AND stories > 16)
WHERE ST_Intersects(b.geometry, a.geometry): 1 - near "(": syntax error

EDIT

Query to select desired subtype and buildings > 16 stories enter image description here

ST_Intersects query - buildings and service area (buildings shorter than 16) enter image description here

I did get the following query to work, but its not a subquery and not the most optimal:

 SELECT b.pkid, b.subtype, b.stories
 FROM bldg AS b, service_area AS a
 WHERE ST_Intersects(b.geometry, a.geometry) AND b.subtype IN ('x', 'y', 'z') AND b.stories > 16
4
  • What results do you actually get and what where you expecting? Can you show a sample of the data in bldg and service_area? Use something like SELECT stories, subtype, AsEWKT(geometry) FROM bldg WHERE stories > 16 LIMIT 5 and SELECT AsEWKT(geometry) FROM service_area LIMIT 5 and put those into the question.
    – BradHards
    Aug 12, 2017 at 4:45
  • When I run the query it results in an error. What I am expecting is two things: 1) only buildings that have a story > 16 and have a subtype code of x, y or z 2) the results from 1 (sub-selection) that intersect with the service area Aug 12, 2017 at 5:18
  • What error? We can't see your screen, and we know you are deep in it, but please help us to help you. Also, where did subtype_de come from, and where is the geometry information I asked for?
    – BradHards
    Aug 12, 2017 at 7:27
  • @BradHards I added images with the two specific queries that you requested. I also added the error. The subtype_de, which I fixed, is the actual column name. I cleaned the column names, table names, and attributes to be cleaner and easier to read. Aug 12, 2017 at 13:35

1 Answer 1

1

You are missing a "," in your FROM before "("

0

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.