In need to calculate the average number of direct neighbors in pre-selected cities in PostGIS. So far, I have only found solutions to select neighbors based on distance (e.g. all neighbors within 1km from my focal point). What I need are really the direct neighbors (so no other points in between) irrespective of the distance. I have a table where I can select coordinates by cities, e.g. select lat, lng, geom from mytable where city = 'Munich' (I have also created a geom column with gist index). Ideally, I want to calculate for each row in the table (selected by cities) the number of direct neighbors that are also located within the same city.

  • 'direct neighbors' can be many things, really; a four/six house wall-to-wall block typical for many older European cities would be most fitting to your description, but that's still the minority of neighborhood relations of house footprints. you'd need to define a threshold, and then using ST_DWithin is most likely what you want. even german 'Kataster' data has still gaps, despite the current efforts in optimizing/normalizing official data... – geozelot Nov 6 '18 at 11:15
  • this comes down very critically to how you define "direct neighbors (so no other points in between) " – Ian Turton Nov 6 '18 at 11:16
  • @Ian: My (non-technical) definition would be the following: The direct neighbor has different latitude/longitude, but the location is directly reachable from my location without crossing another location. Does this help? – Florian Seliger Nov 6 '18 at 11:20
  • are your "locations" areas or points – Ian Turton Nov 6 '18 at 11:21
  • 1
    ...I fear you will find this to be a game of good measures/educated guesses; 'crossing' another location, that is, a Point geometry between two others, is like winning the lottery, if your locations are not in a perfectly aligned grid. a somewhat more robust way would be to find all points within a defined proximity (for better performance) and check if other points lie within a threshold along the direct line connecting each point pair. but then we're back at defining a threshold. – geozelot Nov 6 '18 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.