Finding the closest number in the rails app

Question

Give two parameters that correspond to the two attributes of the object, as you can find 20 entries in the database that are closest to these two numbers.

You have parameters x and y. An object also has these attributes. For instance. x = 1 and y = 9999. You need to find the entry that is closest to x and y.

Answer 1

It depends on how you determine the distance between the two points. If you use a two-dimensional Cartesian coordinate system, this SQL statement will work:

  SELECT id, x, y FROM points ORDER BY SQRT (POWER ((Xx), 2) + POWER ((Yy), 2)) ASC LIMIT 20;

Where X, Y are the inputs.

Answer 2

It looks like you are using geolocation data. If the database backend is Postgres, check to see if you have or can install PostGIS extensions. This gives you very quick tools that give you searches such as “searching for the closest thing to this point”, “searching for everything inside this circle”, “searching for everything within this square”, etc.

http://postgis.refractions.net/

You would do something like this:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometrycolumn] gist_geometry_ops); 

Then you can do something like this - find everything within 100 meters of the point:

 SELECT * FROM GEOTABLE WHERE GEOM && GeometryFromText('BOX3D(900 900,1100 1100)',-1) AND Distance(GeometryFromText('POINT(1000 1000)',-1),GEOM) < 100; 

Examples from the manual .