CODEWITHSUNDEEP
postgresqlruby-on-rails-4postgis
newBike
newBike

Reputation: 15022

UndefinedFunction: ERROR: function st_distance(geography, geometry, numeric) does not exist

I wanna to order by distance, but I got the error

UndefinedFunction: ERROR:  function st_distance(geography, geometry, numeric) does not exist

by .order("ST_Distance(lonlat, ST_Geomfromtext('#{point}'), #{radius}) ")

If I removed the above line , it works fine.

What's wrong with it ?

model

  scope :nearby, lambda { |radius_in_km, lon, lat|
    point = GEOG_FACTORY.point(lon, lat)
    radius = radius_in_km.to_f*1000
    where("ST_DWithin(lonlat, ST_GeomFromText('#{point}'), #{radius} ) ")
    .order("ST_Distance(lonlat, ST_Geomfromtext('#{point}'), #{radius}) ")
  }

more detail error log

PG::UndefinedFunction: ERROR:  function st_distance(geography, geometry, numeric) does not exist
LINE 1: ...4028 6.530438999999999)'), 100000.0 ) )  ORDER BY ST_Distanc...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
: SELECT  "weather_logs".* FROM "weather_logs"  WHERE (ST_DWithin(lonlat, ST_GeomFromText('POINT (101.3034028 6.530438999999999)'), 100000.0 ) )  ORDER BY ST_Distance(lonlat, ST_Geomfromtext('POINT (101.3034028 6.530438999999999)'), 100000.0) , "weather_logs"."datetime" ASC LIMIT 20000
Completed 200 OK in 161ms (Views: 0.2ms | ActiveRecord: 5.4ms)

Upvotes: 0

Views: 4576

Answers (1)

John Powell
John Powell

Reputation: 12581

You are mixing geography and geometry types, that is what the error message means. If you look at the ST_DWithin docs, you will see that the signature are ST_DWithin (geometry, geometry, distance) or ST_DWithin (geography, geography, distance).

I don't know much about Ruby, so am not sure if there is a GEOM_Factory equivalent of the GEO_Factory you have used, but if you use ST_GeogFromText instead of ST_GeomFromText in your where and order by clauses, then you will be dealing with two geographies, which should solve your issue.

As your coordinates are in lat/lon, 4326, it is appropriate to use the geography datatype. If you want to know more about the practicalities of geometry vs geography, see this gis.stackexchange question.

Upvotes: 3

Related Questions