I am new in PostGIS and want to learn. I faced the query where I have to find one point in two different layers or table. I created a query like this but it is not giving the desired result:

SELECT b.name as "Boroughs_Name", r.name as "Restaurant_Name", l.lsoa11nm as "Lsoa_Name"
FROM italian_restaurants as r, .london_boroughs as b, .london_lsoa as l
WHERE r.name= 'Pasta House' and st_intersects(b.geom, r.geom) or st_intersects(b.geom, l.geom)
  • 2
    could you please clarify what results you expect? – ThingumaBob Aug 18 at 6:59
  • the expected result should be the restaurant is located on one Lsoa and the Lsoa is located in two Boroughs – Mohammad Mosum Aug 18 at 10:09

Try joining the tables:

select resturant.*, lsoa.somecolumn, borough.somecolumn
from resturant inner join lsoa on st_intersects(resturant.geom, lsoa.geom)
inner join borough on st_intersects(lsoa.geom, borough.geom)
where resturant.name = 'Pasta house'
  • Thanks, BERA, I did necessary change in naming but still not working – Mohammad Mosum Aug 18 at 12:16
  • What do you mean by not working – BERA Aug 18 at 12:21
  • still receiving error massage – Mohammad Mosum Aug 18 at 12:25
  • And the error says what? – BERA Aug 18 at 12:28
  • No Result: blank output select r.name as restaurant_Name, l.lsoa11nm as Lsoa_name, b.name as borough_name from "Assign05".italian_restaurants as r inner join "Assign05".london_lsoa as l on st_intersects(r.geom, l.geom) inner join "Assign05".london_boroughs as b on st_intersects(l.geom, b.geom) where r.name = 'Pasta house' – Mohammad Mosum Aug 18 at 12:39

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.