0

I have a table called cartlocations:- cartlocations table description

Now I have point geometry of latitude and longitude value, say 23.4877191,86.6927571, I want to find all the geometries which are within a distance of 1000 meters from this given point. I tried the following query:

select locationid 
from cartlocations 
where ST_DWithin(ST_GeomFromText('POINT(23.4877191 86.6927571)'),location,1000);

But I was getting this error- HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Improve this question
1
  • Check if your location column are actual PostGIS geometries. If it is not in the geometry_columns table, then you did not set it up correctly.
    – bugmenot123
    Commented Jan 10, 2018 at 9:19

2 Answers 2

Reset to default
3

The real issue is as @bugmenot noticed in the other part of the message "HINT: No function matches the given name and argument types."

The "location" field in the database is of the native PostgreSQL POINT type https://www.postgresql.org/docs/9.3/static/datatype-geometric.html. However, ST_DWithin requires that geometries are PostGIS geometries, either "geometry" or "geography" type https://postgis.net/docs/ST_DWithin.html.

Your query should work if you first cast the "location" field into geometry with location::geometry. It will still not get the result that you want because distance "1000" will be interpreted to be in the same units as the coordinates and 1000 degrees wrap the whole world almost three times. It would be better to work with the PostGIS geography type instead because then the distance unit of ST_DWithin is always meters.

Improve this answer
3
  • Thank for the great explanation! I was actually looking for the type conversion and your response served the exact purpose. I'm actually new to using postgis, nevertheless this idea worked. But for converting distance from meter to degree I would always have to then multiply the required distance in meters with (1/11132.5) to get the degree equivalent. Can you suggest some other method?How to work with PostGIS geography? My final query came out to be - select locationid from cartlocations where ST_DWithin(ST_GeomFromText('POINT(23.4877191 86.6927571)'),location::geometry,100*(1/11132.5));
    – Souvik Haldar
    Commented Jan 10, 2018 at 10:25
  • It should work by using ST_GeogFromText instead of ST_GeomFromText and using cast to ::geography. The speed may be poor. If you have some critical use for this I would consider to create a new, spatially indexed table which has the "location" field natively as geography. I guess that you know that 1/11132.5 gives right result only on top of the Equator.
    – user30184
    Commented Jan 10, 2018 at 12:09
  • Thank you so much @user30184, but I'm when I'm trying to cast the location (which is of type point) to geography I'm getting this error- ERROR: cannot cast type point to geography LINE 1: ...gFromText('POINT(23.4877191 86.6927571)'),location::geograph...
    – Souvik Haldar
    Commented Jan 10, 2018 at 14:44
1

"No function" means the ST_DWithin doesn't exists, Can you see under Function(10**)? Else

Please make sure you added postgis extension. Query: Create extension postgis;

Improve this answer
6
  • Yes the extension has been created properly and all the points in the table are well within 1000 meters. @Aaron
    – Souvik Haldar
    Commented Jan 10, 2018 at 7:44
  • 2
    Your answer is incorrect because you only looked at the first two words of the message. It actually says that there is no function of said name with the argument types passed. I assume OP does not have actual geometries in their location column.
    – bugmenot123
    Commented Jan 10, 2018 at 9:20
  • Thanks for the review! I rectified my mistake by casting it to the proper form. But now the issue is, the distance is given in degree so either I have to convert my required distance which is given in meters to degree (by multiplying it by 1/11132.5) or cast all the values to the type geography. But while doing so I'm unable to cast the point value to geography, the error being- ERROR: cannot cast type point to geography LINE 1: ...gFromText('POINT(23.4877191 86.6927571)'),location::geograph...
    – Souvik Haldar
    Commented Jan 10, 2018 at 14:50
  • Maybe it works as select location::geometry::geography
    – user30184
    Commented Jan 10, 2018 at 15:47
  • @user30184 Oh I'm immensely thankful! It worked! So the final query is select locationid from cartlocations where ST_DWithin(ST_GeogFromText('POINT(23.4877191 86.6927571)'),location::geometry::geography,1000);
    – Souvik Haldar
    Commented Jan 10, 2018 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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