CODEWITHSUNDEEP
postgresqlpostgis
dc10
dc10

Reputation: 2198

How to get closest geo position within radius?

I am running a rails 4 application with postgis. In order to get closest points I have the following sql

 SELECT * FROM geo_name_cities 
 WHERE ST_DWithin(lonlat, ST_Geomfromtext('POINT(11.75292 47.42321)', 1000))
 ORDER BY ST_Distance(lonlat, ST_Geomfromtext('POINT(11.75292 47.42321)',1000))
 limit 5;

For some reason I do get the following error:

ERROR: function st_geomfromtext(unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add  
explicit type casts.
Character: 57

I don't see a difference to the postgis doc

 ST_GeomFromText('POINT(-71.064544 42.28787)');

Upvotes: 0

Views: 656

Answers (1)

Mike T
Mike T

Reputation: 43692

If PostGIS was installed on to a schema that is not on the search_path (e.g., in postgis), either the functions need to be provided in full (e.g., postgis.st_geomfromtext(...)) or the search_path needs to be updated. This can be done per session:

SET search_path = postgis, public;

To permanently alter the search_path for a database:

ALTER DATABASE my_database SET search_path = postgis, public;

Or for a user:

ALTER USER postgres SET search_path = postgis, public;

Upvotes: 3

Related Questions