[UPDATE]
What I have now is light years better than I did before. With the suggestion to look at ST_POINT, I restarted my googling and the query looks now like so:
SELECT "ID" from "CityBottle".message_locations where location= "CityBottle"."fnMakePoint"( :Longitude,:Latitude)
MakePoint is a function defined like so:
BEGIN
RETURN CAST(ST_SetSRID(ST_Point(Longitude, Latitude),4326) As geography);
END;
It is worth noting that I did notice the function ST_MAKEPOINT, but I am at the prototype stage right now and the above was good enough (plus, I would probably still wrap that into a custom function to protect the queries...).
Now, the main contention I have is "how do I define a reasonable index"? I mean, the points are in their own table and I am accessing the whole thing via a view to list the messages (the table has a query only to make sure that the point isn't already in the table...).
Thus, I'll need some kind of index that will work properly so that I can use ST_DWithin.
Can I just create an index on the field? Is there anything specific I need to do for better performance?
[ORIGINAL]
What I need is a "where am I?" kind of thing listing points within a certain radius from the current POINT(long,lat) .
My understanding is that you do this with ST_DISTANCE and saying that it has to be, for example, less than a certain amount.
My first attempt has been this:
select* from "CityBottle".message_locations ml where ST_DISTANCE(ml.location,POINT(12.0,13,0)) < 100
which has yielded this error message:
ERROR: function point(numeric, integer, integer) does not exist
LINE 1: ...essage_locations ml where ST_DISTANCE(ml.location,POINT(12.0...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Suggestions?
geographyto make theST_Distanceoperate in geodetic units, and 4) Failure to use the spatial index by using theST_DWithinfunction. This question is likely a duplicate of a number of similar questions. See also https://gis.stackexchange.com/questions/79961/what-is-difference-between-st-dwithin-and-st-distance-for-proximity-search-in-po – Vince Jul 16 '17 at 22:08