Mixed SRID's when using users Lat & Lon coordinates to perform KNN through PostGIS when building points to jsonp

Question

I have managed to create a script that passes the users lat and lon to a postgres query that finds the nearest 5 pubs to a users location.

However, on execution I realised no matter what lat and long I used the closets 5 points would always be within the Isle of Scilly, realising this I realised that the SRID for the script was still 2700 for the British national grid. Upon changing this to 4326 though I received a mixed SRID error

Operation on mixed SRID geometries The script for this is:

//knn query
$knn1 =
    "SELECT jsonb_build_object(
            'type',     'FeatureCollection',
            'features', jsonb_agg(feature))
    FROM (
        SELECT jsonb_build_object(
            'type',       'Feature',
            'geometry',   ST_AsGeoJSON(st_transform(geom,4326),4)::jsonb,
            'properties', to_jsonb(row) #omits#
        ) AS feature
        FROM (SELECT #fields# FROM #table# as K, st_geomfromtext('point(#location#)',4326) ORDER BY st_distance(K.geom, st_geomfromtext('point(#location#)',4326)) LIMIT 5) row) features;";

//27700
//4326
//geomfromtext
//pointfromtext


    //replace placeholders
    $knn2 = str_replace("#table#", $ktable, $knn1);
    $knn3 = str_replace("#fields#", $fields, $knn2);
    $knn4 = str_replace("#location#", $Loc, $knn3);
    $knn5 = str_replace("#omits#", $geom, $knn4);
    ChromePhp::log($knn5);

//execute query
if (!$response = pg_query($conn, $knn5)) {
    ChromePhp::error("*KNN script failed*");
    exit;
}
    
//return data
while ($row = pg_fetch_row($response)) {
    foreach ($row as $i => $attr){
        echo $attr;
    }
}

with #location# replaced with the $Loc variable which is the location passed through using ajax

Image shows ChromePHP logs showing that the lat and long are successfully passed to the script

Answer 1

Your key query is

SELECT #fields# FROM #table# as K, st_geomfromtext('point(#location#)',4326) 
ORDER BY st_distance(K.geom, st_geomfromtext('point(#location#)',4326)) LIMIT 5

Which assumes that your table is in lon/lat (EPSG:4326), but you are in fact using OSGB (EPSG:27700) you need your input point to be in 27700 too, so it becomes:

SELECT #fields# FROM #table# as K, st_transform(st_geomfromtext('point(#location#)',4326), 27700) 
ORDER BY st_distance(K.geom, st_transform(st_geomfromtext('point(#location#)',4326)), 27700) LIMIT 5