I've got a database of points (in this case, schools) and in my app, users search for the nearest ones to them. Under the hood, we're currently using ElasticSearch to filter by latlng (using Geo Distance, which gets the distance as the crow flies. For the majority of places, this works fine, but in some coastal areas, this will pick up places that are impossible to get to, in the example below, a radius of 20 miles will pick up schools in Weston-Super-Mare, in reality 55 miles:
I initially decided to use the Google Maps Distance Matrix API to filter my inital as the crow flies search, but there's a limit of 25 destinations per query, and as the requests will be dynamic and user-facing, it's not practical to parcel these requests up into small pieces and pop in a background job.
Is there any way to carry out these calculations while accounting for bodies of water on a database level? The schools are stored in a Postgres database, so I thoughts about using PostGIS and some kind of point in polygon query, but I have no idea where to start looking.