sql - How do I search for restaurants in a region in a country?

Question

I have restaurants for which I have the following information in postgis:

Last name
longitude
latitude
address.

The address is divided into several tables with the foreign key notios so that I have a country table, a region table, etc.

I want to know the restaurants located in a region of a country (eg France).

So I have 2 solutions:

search in the country table, then region, then display the list of restaurants attached to the region or use the postgis polygon system.

I would like to have your opinion on which is the most efficient / the most relevant.

currently I use this query (which in my opinion can be improved):

      "select  *
 from data where  ST_Intersects(st_point(lon, lat) , 
" +
                        " ST_GeomFromGeoJSON('
" +
                       " %polygone* 
"+
                        "')
" +
                        ") = 'true' order by name";

Here the polygon object with the list of points (too long to write I name it% polygon for this question)

Thanking you.

Answer 1

You are building the point and the polygon - instead of having proper geometry column - which is inefficient.

You haven't talked about spatial index, so the intersect operation will be slow.

You haven't specified the complexity of the polygons: if they have many many vertices, an intersect operation will be slow. This can be improved by subdividing them first.

So technically speaking, using indexed and eventually subdivided polygons make such operation blazing fast.

BUT, addresses are typically not perfect. Some may be incomplete, other may be just wrong (ex: someone entered the neighboring town name). Selecting by geometry will give different results than selecting by attribute, so you may want to analyze your data quality before deciding, and to carefully compare the result discrepancies between both methods.