The way you have drawn your overflow pipe is what I would call a "vertical standpipe" or a "tundish". It is a common way to control the level in a tank. There are a few aspects to consider in designing them, but probably using Mansari's recommendation of 1 ft/s will get you to a workable design very quickly.
The first thing to realise is that the standpipe will not run full (unless you are controlling the flow externally). If it were to run full then you would need the entrance to be flooded. However, running it this way means that for the overflow rate to vary in order to be able to accommodate any variations in inflow will require the tank level to vary - and this is what you do NOT want.
So this means that you will run the standpipe in part-full mode - and it must therefore be self venting. It also means that the entrance will not be flooded and as you suspected the entrance has to be considered as a circular weir. Sometimes the top of the standpipe is made like a funnel or conical reducer to increase the circumference of the weir.