> Matthias,
> This is a different approach but it might be useful. This is untested,
> but hopefully it will give you the idea:
> select
> floor((st_x(a.the_geom)-b.minx)/1000)::integer as col,
> floor((st_y(a.the_geom)-b.miny)/1000)::integer as row,
> count(*) as cnt
> from
> (
> select floor(min(st_x(the_geom)))::integer as minx,
> floor(min(st_y(the_geom)))::integer as miny
> from points ) as b,
> points a
> group by col, row
> order by cnt desc;
> With the col, row, values and the count you can create the polygons
> after the fact.
> -Steve W
Thanks for your idea Steve! Now it runs very very fast: ~500000 points with 10m
polygons in ~30 seconds
DELETE FROM point_fishnet;
INSERT INTO point_fishnet(the_geom, count)
(
SELECT ST_SetSRID(
ST_MakePolygon(
ST_MakeLine(
ARRAY[
ST_MakePoint(minx + col * 10.0,
miny + row * 10.0),
ST_MakePoint(minx + (col + 1) *
10.0, miny + row * 10.0),
ST_MakePoint(minx + (col + 1) *
10.0, miny + (row + 1) * 10.0),
ST_MakePoint(minx + col * 10.0,
miny + (row + 1) * 10.0),
ST_MakePoint(minx + col * 10.0,
miny + row * 10.0)
])
), 900913
) AS the_geom,
cnt
FROM
(
SELECT floor((ST_X(points.the_geom) - min.minx) /
10.0)::integer AS col,
floor((ST_Y(points.the_geom) - min.miny) /
10.0)::integer AS row,
count(*) AS cnt
FROM
(
SELECT
floor(ST_XMin(the_geom))::integer AS minx,
floor(ST_YMin(the_geom))::integer AS miny
FROM gars_900913_convex_hull
) AS min,
SELECT the_geom
FROM points
GROUP BY col, row
ORDER BY cnt desc
) AS koords,
(
SELECT floor(ST_XMin(the_geom))::integer AS minx,
floor(ST_YMin(the_geom))::integer AS miny
FROM area_convex_hull
) AS min
);
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
