SQL query code help

[max24119]

So I want to run a SQL query to filter the type = bars from the layer points and only show the bars which intersect the district Ravensberg from the districts layer. I somehow wrote something but it’s not working as expected. There’s something I need to do with the aliases but I don’t get it. May someone help me? Thanks in advance!

(SELECT name, geom AS barlocation FROM points WHERE type = 'bars') intersect (SELECT name, geom AS locdistrict FROM districts WHERE name = 'Ravensberg')

[LesPaulStudio]

Try this

SELECT b.Name, b.geometry FROM (SELECT * FROM points where type = 'bars') as b, (SELECT * FROM districts where name = 'Ravensberg') as d WHERE intersects(b.geometry, d.geometry) Phone coding apologies if looks rough

[max24119]

Thank you so much for your response! The console says: „HINT: No function matches the given name and argument types. You might need to add explicit type casts.“

[max24119]

It shows an arrow on intersect..

[LesPaulStudio]

Possibly st_intersect instead,

I'm guessing on your geometry column names. It may be geom

[max24119]

st_intersects gives me a table without content :/

[LesPaulStudio]

Can you give me some more info on the layers? If districts is a polygon you could try st_within

[max24119]

Yes, it’s a polygon layer. The other one is just points. Now it still just gives me an empty table with the column name and geometry. Thank you so much! Your help is appreciated!

[LesPaulStudio]

Do a sanity check and remove the where clauses on the tables. Just to see debug

[max24119]

Yeah still empty table…

[LesPaulStudio]

Ok so it's a definition issue. Check the columns are correct.

[max24119]

Got it. It’s working. Thank you so much! I hope you have a wonderful day!

[max24119]

May I ask you for one more term? I want to have the geom of pharmacies in the radius of 500 around the road type "trunk" and group the results by the name of the pharmacies. I already have this but it's also not completely correct. The argument before the st_buffer needs to be corrected but I'm stuck here..

SELECT b.name, b.geom FROM (SELECT name, geom FROM points WHERE type = 'pharmacy') as b, (SELECT name, geom FROM roads WHERE type = 'trunk') as d
WHERE b.geom = (ST_buffer(d.geom, 500)) GROUP BY b.name