S
Supabase•2w ago
jkwok678

Unable to get data using rpc in JS library, but able to in sql editor

I'm looking to use postgis to store a simple table with a name and point value. Should I add the postgis extension onto the public table? I've seen tutorials where people create a gis schema and the location table lives there. But when I've tried to use the js client library. like 
const { data, error } = await supabase
      .schema("gis")
      .rpc("nearby_locations", {
        user_lat: location?.latitude, 
        user_long: location?.longitude, 
        radius_meters: 5000
      });
const { data, error } = await supabase
      .schema("gis")
      .rpc("nearby_locations", {
        user_lat: location?.latitude, 
        user_long: location?.longitude, 
        radius_meters: 5000
      });
I get zero results, even though it works when I run the method This is what the function looks like 
  select
    l.id,
    l.name,
    l.address,
    ST_Y(l.point::geometry) as lat,
    ST_X(l.point::geometry) as long,
    ST_Distance(
      l.point,
      ST_SetSRID(ST_MakePoint(user_long, user_lat), 4326)::geography
    ) as dist_meters
  from gis.locations l
  where ST_DWithin(
    l.point,
    ST_SetSRID(ST_MakePoint(user_long, user_lat), 4326)::geography,
    radius_meters
  )
  order by dist_meters;
  select
    l.id,
    l.name,
    l.address,
    ST_Y(l.point::geometry) as lat,
    ST_X(l.point::geometry) as long,
    ST_Distance(
      l.point,
      ST_SetSRID(ST_MakePoint(user_long, user_lat), 4326)::geography
    ) as dist_meters
  from gis.locations l
  where ST_DWithin(
    l.point,
    ST_SetSRID(ST_MakePoint(user_long, user_lat), 4326)::geography,
    radius_meters
  )
  order by dist_meters;
in the gis schema running 
select * from gis.nearby_locations(51.552188, -0.364495, 5000);
select * from gis.nearby_locations(51.552188, -0.364495, 5000);
in the sql editor on supabase returns a result.
3 Replies
garyaustin
garyaustin•2w ago
Almost always RLS if you don't get an error. Try impersonation in the SQL editor (bottom right) and see what happens.
jkwok678
jkwok678OP•2w ago
My other question was about DB design, where should a location table exist? Should it be in a separate schema than public? Is there a rule people should follow?
garyaustin
garyaustin•2w ago
Totally up to you. RLS protects and depends on if you have data that is similar to all your other public tables. I only use custom schemas for data needing extra security by removing grants to the schema.

Did you find this page helpful?