Radius search database function

[nealf]

Figure out the best way to implement a radius search (e.g. parks within 5 miles) through the PostgREST API. Will probably require using a function. Ideally also needs to allow for filtering based on amenities.

[nealf]

Looks like the best way to do this is going to be through a function/stored procedure. PostgREST requires named variables in functions in order to work properly, so have to make sure we do that. I think we'll probably want to pass lat, lng, and radius in miles (0 defaults to no limit/return everything).

The SELECT statement in the function will probably just use the PostGIS function ST_Distance. We'll want to return the results as geojson, and I think I've got a function somewhere that turns all of the row results into a single json document...I'll try to track that down.

[nealf]

Now that I think about it, if our function just returns regular rows, I think PostgREST will convert them to json, though I haven't confirmed that for functions. So let's try that first, because the alternative is trying to modify the following query that takes the json rows and creates one big json document :-)

SELECT row_to_json(featcoll) FROM 
            (SELECT 'FeatureCollection' As type, array_to_json(array_agg(feat)) As features FROM 
                (SELECT 'Feature' As type, ST_AsGeoJSON(geom)::json As geometry,
                    row_to_json((SELECT l FROM (
            SELECT countyfp AS fips, name
             ) As l)) As properties 
                    FROM geo.us_counties AS g
                    WHERE statefp = '51')
                AS feat)  
            AS featcoll;

[nealf]

Here's a tutorial that might be helpful in returning a table from the function: http://www.postgresqltutorial.com/plpgsql-function-returns-a-table/

[nealf]

Here's an example javascript request to our new endpoint:

var settings = {
  "async": true,
  "crossDomain": true,
  "url": "https://parks.api.codefornrv.org/rpc/get_radius_miles",
  "method": "POST",
  "headers": {
    "accept": "application/json",
    "content-type": "application/x-www-form-urlencoded",
    "cache-control": "no-cache",
    "postman-token": "17d1e514-8278-2df4-0c80-d201ec4963ee"
  },
  "data": {
    "latitude": "37.2341",
    "longitude": "-80.4139",
    "radius_size": "1"
  }
}

$.ajax(settings).done(function (response) {
  console.log(response);
});

[jhawley]

That works, thanks!