Find polygon containing point from lat/lng table

user_2078412 0
Viewed 254

I have a table, Regions:

| id | name     |
|----|----------|
| 1  | Jersey   |
| 2  | Scotland |
...

and a table of RegionPoints (which define the bounding box for each region):

| id | regionid | lat   | lng   |
|----|----------|-------|-------|
| 1  | 1        | 49.27 | -2.27 |
| 2  | 1        | 49.27 | -1.99 |
| 3  | 1        | 49.15 | -2.27 |
| 4  | 1        | 49.15 | -1.99 |
...

Given a latitude and longitude, I want to find the regions which contain the given point.

From my understanding, I need to aggregate by regionid, then use ST_ConcaveHull, followed by ST_Contains using the latitude and longitude to query for, however my concern is that with a large number of regions, computing a concave hull for each will be very inefficient.

This is my first time using PostGIS, so a bit stuck.

postgis postgresql
1 Answers

Yes, computing the concave hull "on the fly" can be inefficient with lots of data.

Also, you won't be able to use a spatial index, as your geometries are built at query time.

Using ST_MakeEnvelope instead of ST_ConcaveHull will mitigate the problem.

However, as JGH suggests, you should store those coordinates as polygons in the Regions table, using ST_MakeEnvelope to create them.

Than build a spatial index and use ST_Contains to get the regions that contains a point.

user_57033320
Related