I want to get information of buildings by the road side only. Assuming that the building table has multi-polygon geometry and the road table has multi-linestring geometry, what would be a pgSQL Query to achieve the goal?

Thanks a bunch.

  • 1
    multilinestring does not guarantee that how linestring behaves. i would recommend using linestring type if possible. – simplexio Jan 16 '13 at 12:40
  • that's how my customer wants it, I dont have much choice, but are you actually telling me that I can convert multilinestring to linestring using a pgSQL query? – Kourosh Jan 16 '13 at 13:12
  • 1
    in practise one multiline string can contain for example two line which are parallel of eachother. or are non-contineus. So problems is that if you use multilinestring you cant trust answers or road directions. so you have two options merge multilinestrings into longest possible line or keep multilinestring but be sure that you use only valid data. I personally see that using only linestrings is part of data validation. This might be problem in you use case or not. I have done some work with road data for different use case and for me it was only option. – simplexio Jan 16 '13 at 13:54

Do you know at what distance the buildings are from the roads side?

You could use the st_dwithin function (selecting everthing within a certain distance):

 buildings.identificatie, .buildings.geom 
 buildings, road
 St_DWithin(buildings.geom, road.geom, 200)

This way you select the buildings within 200 m from the road. I am not sure how it works with multi-polygons and multi-linstrings.

|improve this answer|||||

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.