kill pg connection

PostgreSQL 9.2 and above:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND pid <> pg_backend_pid();

PostgreSQL 9.1 and below:

SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB'
  AND procpid <> pg_backend_pid();

Logging in pgsql

edit postgresql.conf and change the following:

log_statement = 'all'
log_min_duration_statement = 0
logging_collector = on
#log_directory = 'pg_log'

make sure pg_log is available relative to the data dir

restart pgsql service

PostGIS to GeoJSON

SELECT
    row_to_json(feature_collection)
FROM (
    SELECT
        'FeatureCollection'::text as type,
        case
            when
                array_agg(feature_collection_data) is null
            then
                array_to_json(ARRAY[]::varchar[]) 
            else
                array_to_json(array_agg(feature_collection_data))
        end as features
    FROM (
        SELECT
            'Feature'::text as type,
            ST_AsGeoJSON({geomCol})::json as geometry,
            row_to_json((SELECT row FROM (SELECT {properties}) as row)) as properties
        FROM
            {table} as row_data
        WHERE
            {where}
    )  as feature_collection_data
)  as feature_collection;";

Notes:

{geomCol} - geom column to select from or geometry returning call such as ST_GeomFromText('POINT(' || lon || ' ' || lat || ')' ,4326)

{properties} - a comma separated list of columns to select from; can be aliased

{table} - a table to select from

{where} - a where clause if needed

WebAPI file upload

[HttpPost]
 [HttpPost]
[Route("shpupload")]
public async Task UploadShp()
{
    if (!Request.Content.IsMimeMultipartContent())
    {
        return new NegotiatedContentResult<string$(HttpStatusCode.UnsupportedMediaType,
            "Content is not supported", this);
    }

    var subFolder = Guid.NewGuid().ToString();
    var fullPath = Path.Combine(@"f:\temp", subFolder);

    if (!Directory.Exists(fullPath))
    {
        Directory.CreateDirectory(fullPath);
    }

    try
    {
        var streamProvider = new MultipartFormDataStreamProvider(fullPath);
        await Request.Content.ReadAsMultipartAsync(streamProvider)
            .ContinueWith(o =>
            {

                if (o.IsCanceled || o.IsFaulted)
                    throw new HttpResponseException(HttpStatusCode.InternalServerError);

                foreach (var fileData in streamProvider.FileData)
                {
                    File.Move(fileData.LocalFileName, Path.Combine(fullPath, fileData.Headers.ContentDisposition.FileName.Replace("\"", "")) );
                }

            });

        //first process the files
        //for (var f = 0; f < HttpContext.Current.Request.Files.Count; f++ )
        //{
        //    var file = HttpContext.Current.Request.Files[f];
        //    file.SaveAs(Path.Combine(fullPath, file.FileName));
        //}

        //and finally take care of the extra form data if any
        //HttpContext.Current.Request.Form[""];
        //...


    }
    catch (Exception ex)
    {
        return InternalServerError();
    }

    return Ok();
}