1

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

Successful pass of location to script

3
  • 2
    What projection is your data in postgis stored in? (4326 or 27700?) - looks like a typo in your question.
    – Mapperz
    Apr 5 at 16:35
  • see gis.stackexchange.com/questions/153510/… for doing your nearest distance calculation.
    – Mapperz
    Apr 5 at 16:39
  • @Mapperz I believe it is in 27700 hence the conversion to 4326 in the script
    – WMTS
    Apr 5 at 16:47
3

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
1
  • Perfect Answer, I had to reposition the brackets for it to work being: 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 But this is exactly what I needed
    – WMTS
    Apr 5 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.