1

I don't think my query is optimized.

I want to sort the files (which each contain 1 lon and 1 lat) which are at a certain distance from a point and display the calculation of the distance between a file and the given point.

 SELECT  *, ST_DistanceSphere( st_point(lon, lat) , st_point(49.9, 6.7)) as result  from data
  where
  ST_DistanceSphere( st_point(lon, lat) , st_point(49.9 ,6.7)) < 10000

so I want to retrieve the list of points which are at a distance less than 10,000 meters AND that the result column contains the calculated distance. it takes more than 1 second to return the result.

After either it is not optimized, or in postgis there is another way to get the answer faster ...

Could you please help me optimize this query?

my table:

        drop table if exists data cascade;
        CREATE TABLE data(
idSERIAL PRIMARY KEY,
        lon float,
        lat float,
      
        );

thank you

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Camel4488
  • 323
  • 1
  • 3
  • 9
  • besides an index, why do you don't show a create table and explain of your query – nbk Jul 26 '21 at 15:32
  • 1
    This post may solve your problem: https://gis.stackexchange.com/questions/77072/return-all-results-within-a-30km-radius-of-a-specific-lat-long-point –  Jul 26 '21 at 15:34
  • nbk: I don't know if adding an index to lon and lat will solve my speed problem – Camel4488 Jul 26 '21 at 15:36
  • anamul: the 4326 will go faster than my request? I had tried at the beginning I was not convinced, then I may have made a mistake – Camel4488 Jul 26 '21 at 15:37
  • SELECT *, ST_DistanceSphere( ST_SetSRID(st_point(lon, lat), 4326) , ST_SetSRID(st_point(6.49413, 49.181477), 4326)) as result FROM data WHERE ST_DWithin(ST_SetSRID(ST_MakePoint(lon, lat), 4326), ST_SetSRID(ST_MakePoint(6.49413, 49.181476), 4326), 10) order by result limit 10 response times are even worse than before – Camel4488 Jul 26 '21 at 18:30

1 Answers1

2

The easiest and fastest way to do it would be to store the coordinates as geometry or geography instead of separated coordinate pairs:

ALTER TABLE data ADD COLUMN geog geography (point,4326);
UPDATE data SET geog = ST_MakePoint(lon,lat);

After that, create a gist index over this new column:

CREATE INDEX idx_data_point ON data USING gist (geog) ;

Use ST_DWithin for querying distances, as ST_DistanceSphere or ST_Distance will not use the spatial index!

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);
  • Note: the true in the functions ST_DWithin and ST_Distance means use_spheroid=true, which is the default for geography parameters.

Demo: db<>fiddle

See also: Getting all Buildings in range of 5 miles from specified coordinates

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • ok I had already used that ... but I had not put the index on the geog =) so here is my request: SELECT *, ST_DistanceSphere(st_point(lon, lat), ST_Point(7.5, 49.9)) as result FROM data WHERE ST_DWithin(geog,ST_MakePoint(7.5 ,49.9),10000,true) suddenly, according to your answer, I think it is still possible to improve the execution because I use the St_distanceSphere to know the distance but it will be maybe better to reuse the geog, how to calculate the distance of the shot? – Camel4488 Jul 26 '21 at 19:19
  • 1
    @Camel4488 see my last edit: the `true` in the end of st_distance and st_dwithin roughly means "use the spheroid" – Jim Jones Jul 26 '21 at 20:28
  • 1
    I don't know what to tell you other than thanks for splitting me apart ... 3 my runtime! I have other requests to improve, I try first and then I will post them (here or on another post)! thanks a lot!! – Camel4488 Jul 26 '21 at 20:30
  • 1
    @Camel4488 I'm glad it helped. In my other answer I deal in more detail with ST_DWithin and ST_Distance.. https://stackoverflow.com/questions/51889155/getting-all-buildings-in-range-of-5-miles-from-specified-coordinates/51889638#51889638 cheers – Jim Jones Jul 26 '21 at 20:33
  • maybe I'm wrong, but I have the impression (according to pgadmin) that ST_Distance(geog,ST_point(49.9 ,6.7)) goes faster than SELECT ST_Distance(geog,ST_MakePoint(49.9 ,6.7)::geography,true) – Camel4488 Jul 26 '21 at 21:29
  • Interesting fact! Could you add a fiddle with the comparison? I'll check it out in the morning.. now I'm going to hit the sack. Cheers – Jim Jones Jul 26 '21 at 21:41
  • https://dbfiddle.uk/?rdbms=postgres_12&fiddle=75d8037f82e22197fa7e64dc6cf38a11 Difficult to see clearly on pjadmin it is never the same result (=> the result of the execution time) – Camel4488 Jul 26 '21 at 21:55
  • I added more lines: I forgot to specify that I have around 410,000 files to compare ^^ – Camel4488 Jul 26 '21 at 21:58
  • 1
    @Camel4488 I believe that there is no much difference. Even if we repeat the same query, the second one gets faster: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b11fc885e93d42cb6c3b84d167141944 but it's worth taking a closer look :) – Jim Jones Jul 27 '21 at 05:36
  • https://dbfiddle.uk/?rdbms=postgres_12&fiddle=d7f00e22a31d6f1d4f041f60076b7789 unless I'm wrong in replacing UPDATE data SET geog = ST_MakePoint (lon, lat); part UPDATE data SET geog = ST_Point (lon, lat); the result (except reading error on my part) and the best – Camel4488 Jul 27 '21 at 07:24
  • https://stackoverflow.com/questions/68541099/how-to-quickly-get-13-record-line-near-a-point-lon-lat-postgis my question on the same topic as this question, but different – Camel4488 Jul 27 '21 at 08:05