4

I'm experiencing an issue in counting and aggregate a set of points by one of their features (in this case the "group") that are contained in a polygon.

DATA

I have a table extracted from a csv that is like this into a table :

csv ( 
      id integer, 
      group character varying,
      lat float,
      long float
    ) 

  id |    group     |   lat   |   long    |
-----+--------------+---------------------+
  1  |      A       |   ...   |    ...    |
  2  |      A       |   ...   |    ...    |
  3  |      C       |   ...   |    ...    |
  4  |      C       |   ...   |    ...    |      
  5  |      D       |   ...   |    ...    |    
  6  |      B       |   ...   |    ...    |

and I have also a table with a set of adjacent polygons that should contain some of the points.

boundary (
          gid integer,
          geom geometry(POLYGON,4326)
         )

 gid |         geom          |   
-----+-----------------------+
  1  |      polygon(...)     |
  2  |      polygon(...)     |
  3  |      polygon(...)     |

DESIRED OUTPUT

My intention is to create a "stats" table with contained the details of how many points are "inside" / "outside" the merged polygons and aggregate these points by "group"

stats (
       s_id integer,
       group character varying,
       inside integer,
       outside integer
      )

  s_id |    group     |   inside   |   outside    |
-------+--------------+---------------------------+
   1   |      A       |     ...    |      ...     |
   2   |      B       |     ...    |      ...     |
   3   |      C       |     ...    |      ...     |
   4   |      D       |     ...    |      ...     |      

WHAT I DID (wrong)

just for the "inside" column

UPDATE stats
SET inside = agg.total
FROM (
      --- get the *group* name and count all the *points*
      SELECT points.group, count ( points.geom ) total 
      FROM ( 
             ---  Creating the Points from the *CSV* table
             SELECT group, ST_SetSRID(St_Point(long,lat), 4326) geom
             FROM csv
           ) AS points,
           ( 
             --- merge all the bounduaries together 
             SELECT ST_SetSRID(St_Union(geom), 4326) geom
             FROM bounduaries
           ) AS bounduary
      --- check wich point is inside the bounduary
      WHERE St_Within(points.geom,bounduary.geom)
      --- group each selected point by it group
      GROUP BY points.group
      ) agg 
--- update according the group
WHERE stats.group = agg.group

when I'm running this full query the server continues to run like a sort of infinite loop, but if I'm running a small part of it I'm getting results in few seconds. There are a couple of millions of points, but all the previews task that I did were performed pretty fast.

THE QUESTION

Where I'm doing it wrong? Someone, could explain it to me?

THE EXPLAIN

Update on stats  (cost=107640.76..107661.08 rows=11 width=124)
  ->  Hash Join  (cost=107640.76..107661.08 rows=11 width=124)
        Hash Cond: (stats.group = (agg.group)::bpchar)
        ->  Seq Scan on stats  (cost=0.00..17.40 rows=740 width=82)
        ->  Hash  (cost=107640.73..107640.73 rows=3 width=44)
              ->  Subquery Scan on agg  (cost=107640.67..107640.73 rows=3 width=44)
                    ->  HashAggregate  (cost=107640.67..107640.70 rows=3 width=18)
                          ->  Nested Loop  (cost=10.45..105221.63 rows=241903 width=18)
                                ->  Seq Scan on csv  (cost=0.00..60024.78 rows=1562678 width=0)
                                ->  Nested Loop  (cost=10.45..29570.08 rows=1 width=18)
"                                      Join Filter: ((st_setsrid(st_point(lng,lat), 4326) && (st_setsrid(st_union((geom)::geometry), 4326))) AND _st_contains((st_setsrid(st_union((geom)::geometry), 4326)), st_setsrid(st_point(lng,lat), 4326)))"
                                      ->  Seq Scan on csv  (cost=0.00..4249.80 rows=92880 width=18)
                                      ->  Aggregate  (cost=10.45..10.46 rows=1 width=32)
                                            ->  Seq Scan on bounduary  (cost=0.00..10.30 rows=30 width=32)
9
  • Please show your explain statement Apr 11 '17 at 15:27
  • I am guessing that the St_union in the sub query is preventing the spatial index from being used in St_within. But need to see explain to be sure Apr 11 '17 at 15:30
  • First, thank you. Second, I don't understand when you are saying"show explain statement". Isn't clear, the way I explained it before?
    – entalpia
    Apr 11 '17 at 15:35
  • @Stefano They mean EXPLAIN so something like EXPLAIN ANALYZE query_here
    – SaultDon
    Apr 11 '17 at 15:44
  • Sorry, I get it just know. Ok I'll put the explain in the post.
    – entalpia
    Apr 11 '17 at 15:45
1

Using the comments that I've received on the post I figure it out how to solve it. @John suggested me to use CTE (Common Table Expression) and trick the query analyzer that was freezing before. I've used as a reference this two posts, how to use it and what is a CTE. After that, it works fine!

WITH points AS ( SELECT group, ST_SetSRID(St_Point(lng, lat), 4326) AS geom
                 FROM csv
                ),
     bounduary AS ( SELECT ST_SetSRID(St_Union(geom), 4326) AS geom
                    FROM boundaries
                )
UPDATE stats
SET inside = agg.total
FROM  (
       SELECT  points.group as group, coalesce(count( points.pt ),0) as num
       FROM points,border
       WHERE St_Within(points.geom,border.geom)
       GROUP BY points.group
      ) agg
WHERE agg.group = stat.group;

EXPLAIN

GroupAggregate  (cost=486420.42..486426.72 rows=200 width=40)
  Group Key: points.group
  CTE points
    ->  Seq Scan on csv  (cost=0.00..70237.24 rows=1721749 width=34)
  CTE border
    ->  Aggregate  (cost=4.14..4.15 rows=1 width=32)
          ->  Seq Scan on bonduaries  (cost=0.00..4.11 rows=11 width=32)
  ->  Sort  (cost=486420.42..486421.85 rows=574 width=64)
        Sort Key: points.group
        ->  Nested Loop  (cost=0.00..486394.11 rows=574 width=64)
"              Join Filter: ((border.geom ~ points.geom) AND _st_contains(border.geom, points.geom))"
              ->  CTE Scan on bonduary  (cost=0.00..0.02 rows=1 width=32)
              ->  CTE Scan on points  (cost=0.00..34434.98 rows=1721749 width=64)
2
  • Well done, so the explain shows index scans now? Apr 12 '17 at 19:29
  • It shows some Scans but as newbie, It's difficult for me tell you which is the one you are talking about. It appears to scan thru 'csv' and 'bounduaries' and it does not show any hash like the first query.
    – entalpia
    Apr 12 '17 at 19:55

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.