~qeef/damn-deploy

ref: 79e841286dcbc0b4219c649c3d6ba53c47472d94 damn-deploy/damndb/72_st_area_split_custom_square_size.sql -rw-r--r-- 1.4 KiB
79e84128Jiri Vlasak Merge branch 'add/custom-square-size' 4 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
CREATE FUNCTION public.st_area_split(ar public.geometry, ssh DOUBLE PRECISION, ssw DOUBLE PRECISION, OUT public.geometry) RETURNS SETOF public.geometry
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$
DECLARE
    jmax INTEGER := 32;
    imax INTEGER := 32;
    srid INTEGER := 4326;
    ss_def geometry;
BEGIN
    CASE ST_SRID(ar) WHEN 0 THEN
        ar := ST_SetSRID(ar, srid);
        RAISE NOTICE'SRID Not Found.';
    ELSE
        RAISE NOTICE'SRID Found.';
    END CASE;

    IF ssw < 0.004 THEN
        ssw := 0.004;
        jmax := floor((ST_XMax(ar) - ST_XMin(ar)) / ssw) + 1;
    END IF;
    IF ssh < 0.004 THEN
        ssh := 0.004;
        imax := floor((ST_YMax(ar) - ST_YMin(ar)) / ssh) + 1;
    END IF;
    ss_def := ST_GeomFromText(
        FORMAT(
            'POLYGON((0 0, 0 %s, %s %s, %s 0, 0 0))',
            ssh, ssw, ssh, ssw
        ),
        srid
    );
    RETURN QUERY WITH foo AS (
        SELECT
            ST_Translate(
                ss_def,
                ST_XMin(ar) + j * ssw,
                ST_YMin(ar) + i * ssh
            ) AS ss
        FROM
            generate_series(0, imax) AS i,
            generate_series(0, jmax) AS j
    )
    SELECT
        ST_Intersection(ss, ar)
    FROM
        foo
    WHERE
        ST_intersects(ss, ar)
    ;
END;
$$;


ALTER FUNCTION public.st_area_split(ar public.geometry, OUT public.geometry) OWNER TO damnuser;