1

I have pretty large dataset (>10M records) with a geography points (id, latitude and longitude), and need to find closest points from this set. Data itself is not updated very often but I need quick response. And I'm looking for something fast. Unfortunately I'm not experienced GIS user.

I tried to use PostGIS with this setup. Made this schema:

CREATE TABLE place (
   id int,
   name varchar(100),
   latitude float,
   longitude float,
   gpoint geometry
);
SELECT AddGeometryColumn ('public','place','gpoint',4326,'POINT',2);
UPDATE place SET geometry = ST_PointFromText('POINT(' || longitude || ' ' || latitude || ')', 4326);
CREATE INDEX idx_geo ON place USING gist(gpoint);

And tried to call data from database using this query:

SELECT * FROM place 
ORDER BY gpoint <-> ST_GeogFromText('POINT(44.509648 40.184822)') 
LIMIT 1;

But speed is not "close to realtime", about 30 seconds on my work laptop.

I've tried to make some optimizations: VACUUM ANALYZE, updated to latest Postgres and Postgis, use clustered table, may be something else (my stack already overloaded with all this tries). Last what I did I moved to another server with much more memory and space, but it increased speed only 2 times. Didn't tried yet to put everything in memory, but honestly I don't know yet how to make it right. Have other databases with a lot of data inside on this sever.

After all I'm not sure that I'm moving right direction. Do I use right storage and database. For some reasons I think that it is should be something trivial. But after few days searching I didn't found solution in the internet.

I want something fast. Under 1 second response time. Is it possible to get this numbers using Postgris or can you recommend to switch to something else? Since this is my pet project, so I want to use something with cheap cost of ownership.

  • possible duplicate of gis.stackexchange.com/questions/14456/… – Ian Turton Jun 18 '17 at 9:55
  • iant, that one you linked to is old and predates built-in KNN support in PostGIS. Though I do feel this question has been asked several times since it is a common one. Can't find at moment. – LR1234567 Jun 18 '17 at 18:05
3

try ST_SetSRID(st_makepoint(36,50),4326) or ST_PointFromText('POINT(36 50)', 4326)) instead ST_GeogFromText('POINT(44.509648 40.184822)')

more info https://boundlessgeo.com/2011/09/indexed-nearest-neighbour-search-in-postgis

On my test data (20000000 points, SRID 4326, gist index) - 78msec

  • 2
    or ST_GeomFromText('POINT(44.509648 40.184822)',4326) – LR1234567 Jun 18 '17 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.