2

I have approx 73,000 locations. For each location, I need to know which of the other locations is within a 1km radius from it. I would like this result to be in a table (so perhaps using a PostGIS spatial database). This is so that I can create edges between these all locations that are within 1km of each other as part of a network analysis study.

0

You can use a distance matrix. Most GIS programs have a tool to generate one.

To find the records that are within 1km of their nearest neighbor, you would set both the input and target point layers as the point layer you have referenced.

Set the measurement unit to km, set the algorithm to match to the nearest 2 points, save to a location, and run the tool. I say match to the nearest 2 point because, at least in QGIS, the tool will match a point to itself which would yield a false result. If the GIS you are using matches to a point to itself, the input and target IDs will be the same in the result. All of these can be removed. Of the remaining records, all the input IDs with a distance less than 1km are the records you are looking for.

To get this data into your original file you could use a table join.

1

If I understand you right this should be a job for a spatial database.

PostGIS for instance.

Then you can get a table with all combinations of locations that is 1km or less from each other.

The query could look something like:

SELECT 
a.id aid, 
a.geom ageom, 
b.id bid, 
b.geom bgeom, 
ST_Distance(a.geom, b.geom) dist 
FROM location_table a, location_table b 
WHERE a.id != b.id 
AND ST_DWithin(a.geom, b.geom,1000);
  • Thank you, this is almost working, but as I haven't used PostGis before and am fairly new to programming, I'm not certain I am replacing the name of my table in the correct parts of the code. The table name is "locationstable". I want to compare it to itself, but I am getting an error saying that "table name "locationstable" is specified more than once". Could you rewrite your code with my table name in it? – Mitchell Jun 27 '17 at 11:00
  • you need the a and the b after the table name. – pLumo Jun 27 '17 at 11:23
  • Yes, as @RoVo says, the a and b are aliases for the table name, which makes it possible for the database to handle the same table multiple times. You can write "location_table AS a", which might make it more clear – Nicklas Avén Jun 27 '17 at 12:15

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.