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:

20 mile radius from Cardiff showing Weston-Super-Mare

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.

  • 1
    well, your approach is bound to having issues, and I fear you can only introduce others when trying to improve on it. I suggest to have a look into proper routing mechanisms, (with OSM data), effectively implementing a distance matrix yourself, in-DB. – geozelot Apr 16 '19 at 12:39
  • 1
    What @ThingumaBob says, you could use pgRouting, as graph search is the most effective way to calculate actual road distance, not as the crow flies. However, as you are already using Elasticsearch, you can apparently combine this with Neo4J, which is a graph database, and do similar routing searches in that, eg, neo4j.com/blog/journey-planning-why-i-love-cypher. – John Powell Apr 16 '19 at 15:32

Baseline data, land and schools, see the screenshot below enter image description here

1) Run the script:

create table land_byf as SELECT ST_Intersection((ST_Dump(geom)).geom, ST_Buffer((ST_SetSRID(ST_MakePoint(30.30, 59.92),4326)),0.1)) as geom from land;

Result: see the screenshot below enter image description here

2) Select only those landfill polygons, in which we have specified the search point, for which we will run the script:

create table land_byf_int as SELECT geom from land_byf WHERE ST_Intersects(geom,(ST_SetSRID(ST_MakePoint(30.30, 59.92),4326)));

See the result in the screenshot below enter image description here

3) Select only those schools that fall into the lands that is integrated, for which we run:

create table land_byf_pnt as SELECT a.geom FROM schools a JOIN land_byf_int b ON ST_Within(a.geom,b.geom);

The result, see the screenshot below enter image description here


SELECT (MIN(ST_Distance((ST_SetSRID(ST_MakePoint(30.30, 59.92),4326)), geography(land_byf_pnt.geom)))) as MinDistance FROM land_byf_pnt;

Add the names of semantic fields, if necessary ...

Hello to all students from me, let them study well and respect each other: -)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.