0

[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?

  • Did you tried with 'ST_Point' ? postgis.net/docs/ST_Point.html – s.k Jul 16 '17 at 20:17
  • Oh! No, I didn't realise that was a thing :) Will try right now. – Andrea Raimondi Jul 16 '17 at 20:33
  • Can you please post it as an answer? I will accept it :) – Andrea Raimondi Jul 16 '17 at 20:39
  • 3
    You're not really ready for an answer yet, because you have several serious SQL errors that need to be fixed. 1) cossing the native and PostGIS geometry types, 2) Not using ST_SetSRID to establish your GCS, 3) not casting to geography to make the ST_Distance operate in geodetic units, and 4) Failure to use the spatial index by using the ST_DWithin function. This question is likely a duplicate of a number of similar questions. See also gis.stackexchange.com/questions/79961/… – Vince Jul 16 '17 at 22:08
  • I wish i could say that you are entirely correct... but I can't, because the ST_POINT pointed me to the right "rabbit hole" to explore. I will update the original post with my findings. – Andrea Raimondi Jul 18 '17 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.