Geocoding - get all records within a boundry

Question

Lets say I have a database of locations (countries, regions, cities, towns) with their lat/long co-ordinates.

E.g. I have the co-ordinates for England 52.16045, -0.70312

Is there a way for me to return all locations within the bounds of England if all I have are lat/long?

Do I need to polygonise the location...if so how would I do that if all I have are lat/longs.

For the record the database is Mysql.

Some guidance would be appreciated.

I'm not sure if you can do this in MySQL. It would be much much easier to do in a programming language like PHP. — dmikester1
Those don't sound like bounds to me, they're center points. You would need the bounding boxes, or at least a search radius. — BenM

ScaisEdge ScaisEdge · Accepted Answer · 2019-01-03T15:58:31

If you have a polygon with the boundary of the state you could use a ST_CONTAINS feature of mysql geometry for find al the points inside the polygon

Assuming you have a table (points) the contain the points and polygons with polygon, and each polygon is based on a polygon.name you could use

SELECT points.col1 
FROM polygons
INNER points ON  ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude)) 
    AND polygons.name = 'Your_name';

the ST_CONTAING just check if a geometry is contained inside one other

Geocoding - get all records within a boundry

1 Answers