I am currently working on some line string data that are very close by and have the same Start and endpoints for the most part. I want to create midpoints or average centroids that will tell me how many of those are grouped up close by.

Using mainly SQL queries i have been able to confirm that the centroids are close enough to intersect with each other using a 15 meter buffer, it is functionnal so far.


feeder_NRO02.ca_pk_uid AS ID,
ST_centroid(feeder_NRO02.geom) AS geom,
1 AS count


My issue is that so far, I haven't been able to count how many times those buffers intersect with each other or create an average centroid.

Any advice? I have QGIS and some basic database tools at my disposal for this.


A test using SpatiaLite. Here line is an input Line layer and the buffer size is set to 15.

SELECT Count(*)
FROM line AS a
WHERE st_intersects(st_buffer(st_centroid(a.geom), 15), st_buffer(st_centroid(a.geom), 15))

which will return only counts how many times those buffers intersect with each other.

If a list of such intersecting pairs is required:

SELECT a.id, b.id, count(*)
FROM   line a, line b 
WHERE  a.id < b.id  AND  st_within(st_centroid(a.geom), st_buffer(st_centroid(b.geom), 15)) = 1
GROUP BY a.id, b.id

In the above example I have used st_within to identify the distance between the centroids so probably this is not a direct answer.

enter image description here

  • 1
    This is a good start and will help a lot, i'm using this to improve my process and i'll post some updates once i start finding some clearer awnsers. Thanks again! – Moreau Colin Oct 9 '17 at 7:33
  • 1
    By enlarging the buffer and counting/grouping by only for "a.id" 's it actually works. I have a lot of points to delete but it works. – Moreau Colin Oct 9 '17 at 9:19
  • @MoreauColin Great! Thanks for your feedback. Grouping only by "a.id" is nice idea. – Kazuhito Oct 9 '17 at 9:29

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.