0

I have a table of boundary polygons, a table of points and a table of other_points

I want to work out the average distance between points contained within and other_points for each polygon.

So far I have this query which works but is not very efficient, I limit distances to within 10,000 meters (data is in SRID 27700). I am using POSTGIS to do this, I ultimately need to get the data into Postgresql.

I am sure it can be done better as this will take many hours to compute. Any ideas how?

WITH POINTS AS (

    SELECT  point_id,
            polygon_id,
            point_geometry as wkb_geometry 
   FROM boundary 
   LEFT JOIN points

  ), DISTANCES AS (

    SELECT 
        distinct ON (ogc_fid) ogc_fid,
        polygon_id,
        round(ST_Distance(B.wkb_geometry, D.wkb_geometry)::NUMERIC,1) as distance
    FROM
       POINTS B, other_points  D
    WHERE ST_DWithin(B.wkb_geometry, D.wkb_geometry,10000)
    ORDER BY ogc_fid,polygon_id,ST_Distance(B.wkb_geometry, D.wkb_geometry)

)
SELECT distinct polygon_id,
       count(polygon_id) over(partition by polygon_id),
       round(avg(distance) over(partition by polygon_id)::NUMERIC, 1),
       FROM DISTANCES
   WHERE DISTANCES IS NOT NULL
  • Please use alias.column everywhere, having to guess what table comes from which table is no fun. For now all I can do is refer you to this answer: gis.stackexchange.com/a/71607/16594 that contains the most important part you need to fix. – Jakub Kania Aug 13 '15 at 18:04
1

OK so here is the much improved query thanks to the useful pointer given by Jakub

WITH POINTS AS (

    SELECT  point_id,
            polygon_id,
            point_geometry as wkb_geometry 
    FROM boundary 
    LEFT JOIN points

 ), DISTANCES AS (

      SELECT polygon_id, point_geometry <-> other_point_geometry as distance FROM
      (
        SELECT
              distinct on(point_id) point_id,
              polygon_id,
              point_geometry,
              (SELECT wkb_geometry FROM other_points ORDER BY wkb_geometry<->B.wkb_geometry LIMIT 1) AS other_point_geometry
        FROM points B
      ) AS FOO
 )
  SELECT distinct polygon_id,
         count(polygon_id) over(partition by polygon_id),
         round(avg(distance) over(partition by polygon_id)::NUMERIC, 1),
         FROM DISTANCES
         WHERE DISTANCES IS NOT NULL
  • You are still missing the ON clause of the LEFT JOIN between boundary and points. – Jakub Kania Aug 16 '15 at 10:33

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.