Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
354 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...