MYSQL AGGREGATE UDF FOR UNION GEOMETRIES (USING WKB)

udf_wkb_union.cpp

// g++ -shared -o /tmp/test.so -I/usr/include/mysql /tmp/tmp.cpp -fPIC -lgeos -rdynamic

//CREATE AGGREGATE FUNCTION udf_wkb_union RETURNS STRING SONAME 'udf_wkb_union.so';

//SELECT GeomFromWKB(udf_wkb_union(AsWKB(SHAPE))) FROM new_table;

#ifdef STANDARD
#include <stdio.h>
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;    
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>
#endif
#include <mysql.h>
#include <m_ctype.h>
#include <m_string.h>    

#define BUFFERSIZE 1024    

using namespace std;

#include <geos/geom/Geometry.h>
#include <geos/geom/GeometryFactory.h>
#include <geos/io/WKBWriter.h>
#include <geos/io/WKBReader.h>
#include <geos/io/WKTWriter.h>
#include <geos/io/ParseException.h>


#include <string>
#include <iostream>
#include <sstream>

extern "C" {
    my_bool udf_wkb_union_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
    void udf_wkb_union_deinit( UDF_INIT* initid );
    void udf_wkb_union_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
    void udf_wkb_union_clear( UDF_INIT* initid, char* is_null, char* is_error );
    void udf_wkb_union_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
    char* udf_wkb_union( UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long *length, char* is_null, char* is_error );
}

my_bool  udf_wkb_union_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
    std::cout << "udf_wkb_union_init" << std::endl;
    if (args->arg_count < 1)
    {
        strcpy(message,"wrong number of arguments: udf_wkb_union() requires one arguments");
        return 1;
    }

    if (args->arg_type[0] != STRING_RESULT)
    {
        strcpy(message,"udf_count() requires a wkb for parameter 1");
        return 1;
    }

    initid->maybe_null    = 1;
    initid->max_length    = 16*1024*1024; // 16 MB

    initid->ptr = NULL;

    return 0;
}

void udf_wkb_union_deinit( UDF_INIT* initid )
{
    std::cout << "udf_wkb_union_deinit" << std::endl;
    if (initid != NULL && initid->ptr != NULL) {
        geos::geom::Geometry *buffer = (geos::geom::Geometry*)initid->ptr;
        delete buffer;
        
        initid->ptr = NULL;
    }
}

void udf_wkb_union_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* is_error )
{
    std::cout << "udf_wkb_union_reset" << std::endl;
    udf_wkb_union_clear(initid, is_null, is_error);
    udf_wkb_union_add( initid, args, is_null, is_error );
}

void udf_wkb_union_clear( UDF_INIT* initid, char* is_null, char* is_error )
{    
    std::cout << "udf_wkb_union_clear" << std::endl;
    udf_wkb_union_deinit(initid);
    
    *is_null = 0;
    *is_error = 0;
}

void udf_wkb_union_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* is_error )
{
    //std::cout << "udf_wkb_union_add" << std::endl;
    if (args->args[0] != NULL)
    {
        geos::geom::Geometry* geom = NULL;
        try {
            std::string wkb = std::string(args->args[0], args->lengths[0]);            
            std::istringstream iss(wkb);
            geos::io::WKBReader reader;
            geom = reader.read(iss); 
        } catch (geos::io::ParseException) {
            *is_error = 1;
            return;
        }
        
        if (initid != NULL && initid->ptr != NULL) {
            geos::geom::Geometry *buffer = (geos::geom::Geometry*)initid->ptr;
            try {
                initid->ptr = (char*)buffer->Union(geom);
                if (initid->ptr != (char*)buffer) {
                    // meeh ?
                    delete buffer;
                }
                delete geom;
            } catch (...) {
                std::cout << "union exception" << std::endl;
                //*is_error = 1;
            }
        }
        if (initid != NULL && initid->ptr == NULL) {
            initid->ptr = (char*)geom;
        }
    }

    // udf_wkb_union_deinit(initid);
}

// https://www.falconview.org/svn/FalconView/trunk/public/third_party/geos-3.3.5/tests/unit/operation/union/CascadedPolygonUnionTest.cpp
char* udf_wkb_union( UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long *length, char* is_null, char* is_error )
{
    std::cout << "udf_wkb_union" << std::endl;

    if (initid->ptr == NULL) {
        *is_null = 1;
        *is_error = 1;
        return result;
    }

    if (is_error && *is_error != 0)
    {
        *is_null = 1;
        return result;
    }
    
    
    if (initid != NULL && initid->ptr != NULL) {
        *is_null=0;
        geos::geom::Geometry *buffer = (geos::geom::Geometry*)initid->ptr;
        
        geos::io::WKBWriter writer;
        std::ostringstream oss;
        writer.write(*buffer, oss);
        
        std::string s = oss.str();
        if (s.size() > initid->max_length) {
            *is_null = 1;
            return result;
        }
        memcpy(result, s.data(), s.size());
        *length = s.size();  
    } else {
        *is_null=1;
    }

    return result;
}