Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create extension postgis;

create table
  public.submitted_images (
    user_id uuid not null,
    created_at timestamp with time zone not null default clock_timestamp(),
    bucket_id text null,
    pattern text null,
    location geography (point) not null,
    constraint submitted_images_pkey primary key (created_at)--,
   -- constraint submitted_images_pattern_fkey foreign key (pattern) references pattern (label),
  --  constraint submitted_images_user_id_fkey foreign key (user_id) references auth.users (id)
  ) tablespace pg_default;

insert into public.submitted_images(user_id,pattern,location)
select gen_random_uuid(),gen_random_uuid(),
ST_GeogFromText('SRID=4326;POINT(' || 360*random()-180 || ' ' || 180*random()-90 || ')')
from generate_series(1,1e5);

create index submitted_images_geo_index
  on public.submitted_images
  using GIST (location);

CREATE EXTENSION



CREATE TABLE



ERROR:  could not extend file "base/5/17456": No space left on device
HINT:  Check free disk space.


explain analyze verbose
select pattern, st_distance(location, st_point(13,17)::geography) as dist_meters
  from public.submitted_images
  where st_dwithin(location,st_point(13,17)::geography,20)
  order by location <-> st_point(13,17)::geography;

ERROR:  relation "public.submitted_images" does not exist
LINE 3:   from public.submitted_images
               ^


explain analyze verbose
select 
    pattern,
    st_distance(location, st_point(13,17)::geography) as dist_meters
  from public.submitted_images
  where st_distance(location, st_point(13,17)::geography) < 20
  order by location <-> st_point(13,17)::geography;

ERROR:  relation "public.submitted_images" does not exist
LINE 5:   from public.submitted_images
               ^