Implement LengthOf function and add unit tests

[ardieorden]

Resolves #9

[ardieorden]

Slight issue, it turns out there's no ST_BUFFER in BQGIS right now. At worst, I can use ST_Buffer on Spatialite or PostGIS. At best, I can figure out a way to make things work with ST_WITHIN on BQGIS.

Edit: For now, I'll just make LengthOf throw a ValueError when it's using BigQueryCore.

[ljvmiranda921]

The original SQL Query from @ardieorden :+1:

WITH lois as (
    SELECT
        osm_id,
        wkb_geometry
    FROM
        gis_osm_roads_free_1 as source
    WHERE
        fclass = 'residential'
    ), buff as (
    SELECT
        ROW_NUMBER() OVER() as fid,
        ST_Buffer(ST_Transform(target.wkb_geometry, 3857),
                  50) AS wkb_geometry,
        wkt as target_wkt
    FROM
        sample_points as target
    ), clip as (
    SELECT
        buff.fid,
        ST_Intersection(lois.wkb_geometry,
                        ST_Transform(buff.wkb_geometry, 4326)) AS geom,
        ST_Length(ST_Intersection(ST_Transform(lois.wkb_geometry, 3857),
                                  buff.wkb_geometry)) AS len
    FROM
        lois,
        buff
    WHERE
        ST_Intersects(lois.wkb_geometry, ST_Transform(buff.wkb_geometry, 4326))
    ), sum_length as (
    SELECT
        fid,
        SUM(len) AS sum_len
    FROM
        clip
    GROUP BY
        fid
    )
SELECT
    buff.fid,
    sum_length.sum_len,
    buff.target_wkt AS wkt
FROM
    sum_length
FULL OUTER JOIN
    buff
ON sum_length.fid = buff.fid

The output:

fid,sum_len,wkt
"1",,POINT (121.0042183 14.6749145)
"2",53.4468864754619,POINT (121.0052375 14.6767411)
"3",59.5566724816342,POINT (121.009712 14.68067)
"4",11.6853167366082,POINT (121.0093311 14.6799482)
"5",96.674652702483,POINT (121.0073296 14.6783498)
"6",190.208865653597,POINT (121.0073185 14.6775968)
"7",43.839783001508,POINT (121.0043212 14.676227)
"8",101.091090620938,POINT (121.0052642 14.6770913)
"9",87.9060682109418,POINT (121.0068593 14.67908)
"10",100.518986552454,POINT (121.0022639 14.675585)