1

In this example:

i-want-to-display-in-postgis-files-that-are-less-than-10-000meter-and-their-calc

I am looking for the recording lines (the cards in postgis), which are at a distance of 10 km. Then I want to display this in a view and add pagination to it; whether the result returned is 5 records or 20,000 is not important.

Now, I want to know the 13 (whatever) rows of records (the cards) that are close to A (lon, lat).

I could use the same query but:

  1. If let's admit I put

    SELECT ST_Distance (geog, ST_MakePoint (49.9, 6.7) :: geography, true) FROM data WHERE ST_DWithin (geog, ST_MakePoint (49.9,6.7), 10000, true);

I am obliged to specify 10,000 meters to St_DWithin. If I look for the 13 lines of records in a very large city, it is very likely to have a hundred of them within 1km, but in a small town it is unlikely.

In addition, in my database, the registration lines, so the files are grouped by theme. So nothing tells me that if I look for cards with a certain theme, even in a big city the 9th card can be located after 10 km.

For me I have 2 approaches:

The first is the query I'm currently using, which is very long, between 800ms and 1 second:

 SELECT  *, ST_DistanceSphere( st_point(lon, lat) , st_point(6.5, 48.7)) as result  from data  +
              where
        id_poi != '%s
          order by 13 limit 13

If I follow the advice from my previous post I have therefore modified the query like this:

SELECT  *, ST_Distance(geog , st_point(6.5, 48.7)) as result  from data 
               where
        id_poi != '%s'
          order by 13 limit 13

but the performances are not there.

This is a bit normal, I have about 410,000 rows to sort and I ask postgis to calculate the distances of ALL the files then to order them then to limit the result to 13.

So my question, how to get the 13 record cards closest to a given lon and lat?

for example

thank you

Camel4488
  • 323
  • 1
  • 3
  • 9
  • Hi there. I'm not really sure I understood your issue. The queries aren't using any index (therefore slow) because you're running a full scan in the table (without a `where` clause). Question: how would you deal with ties? I mean, what if there are 20 tows with equal size (e.g. 1km), would you want to display all of them? or just cut to 13 and simply ignore the other 7? – Jim Jones Jul 27 '21 at 08:15
  • In the previous query I wanted to display the result whether it is 10 or 20,000 whatever. but in my question, I want to display the 13 files closest to a given point, if possible in order of distance. Take the example of an e-commerce, on its shoe page, it will display similar shoes, or of the same color. I want to do the same. Example a restaurant file, I want to display the 13 other closest restaurants, then the 20 restaurants which are organic. So I don't know the distance – Camel4488 Jul 27 '21 at 08:19

1 Answers1

1

You can apply the distance function using the operator <-> directly in the ORDER BY clause. Doing so the planner will use the gist index:

EXPLAIN (ANALYSE,COSTS OFF)
SELECT * FROM data
ORDER BY geog <-> ST_Point(6.5, 48.7) 
LIMIT 13;

                            QUERY PLAN
----------------------------------------------------------------------
Limit (actual time=15.019..15.213 rows=13 loops=1)
  ->  Index Scan using idx_data_point on data (actual time=15.017..15.210 rows=13 loops=1)
        Order By: (geog <-> '0101000020E61000000000000000001A409A99999999594840'::geography)
Planning Time: 0.372 ms
Execution Time: 15.309 ms

Demo: db<>fiddle

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • ok select * , ST_Distance(geog , ST_Point(6.5, 48.7)) FROM data ORDER BY geog <-> ST_Point(6.49413, 49.181476), 15 LIMIT 13 the order 15 corresponds to the result of the distance, on my pgadmin, with my 410,000 lines, it takes about 1 seconds. without calculating st_distance and order by 15 (result) it takes about 300 ms – Camel4488 Jul 27 '21 at 08:30
  • https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e260962fe9dd91ca07dc44ab9db5c47f – Camel4488 Jul 27 '21 at 08:32
  • If we can not do better, the sort part distance is the icing on the cake but you have just divided my request part 4 (the processing time) – Camel4488 Jul 27 '21 at 08:33
  • @Camel4488 your last query is slow because you added a new element to the order by clause (`5`), so the index can no longer help you. If you want to re-order the result of the order by, consider a subquery or just rethink the index strategy. Adding a `DESC` might also deliver what you want, but perhaps your index won't work either: `ORDER BY geog <-> ST_Point(6.49413, 49.181476) DESC` – Jim Jones Jul 27 '21 at 08:41
  • 1
    ok i will look later for the subquery (i don't know i have to watch) but even with desc it exceeds 1 second. It does not matter for the moment the result suits me thank you very much! I still have 2 to improve but I am looking first then I will post them, you are very good advice and I thank you very much! – Camel4488 Jul 27 '21 at 08:50
  • 1
    at worst a simple ArrayList in spring boot for example, can also sort it – Camel4488 Jul 27 '21 at 08:51
  • Hey thank you again for your help! I just thought of you! I had a very long processing time on the query ... I dropped the table and forgot to put the index back ... I have a question that I posted here: https://stackoverflow.com/questions/68686879/how-do-i-search-for-restaurants-in-a-region-in-a-country – Camel4488 Aug 06 '21 at 19:55