I have a PostGIS db, and I want to find the points that lie within some region (a bounding box). I have two sets of coordinates, from which I can get all four rectangle points that form the box (if needed). My data column in question is named 'point' and it is also of type point.
- Is there anyway to specify four sets of coordinates (lat/long) and get all the points that lie within the box.
- Or specify two points and let the DB work out the rectangle's corners, and return the points within
Just in case I am not being clear as to what I want to achieve. The equivalent with 'vanilla' sql if I had a lat and long field instead of a point would be:
SELECT * FROM myTable where lat> xMin AND lat < xMax AND long > yMin and long < yMax
UPDATED EDIT:
I am trying underdark's solution. At first I didn't have the ST_MakePoint constuctor (now I do) and I still get a very similar error (just on a different character).
SELECT * FROM myTable WHERE ST_Within(ST_MakePoint(point),GeometryFromText('POLYGON((75 20,80 30,90 22,85 10,75 20))',4326))
and I am getting this error:
ERROR: function st_makepoint(point) does not exist
LINE 1: SELECT * FROM triples WHERE ST_Within(ST_MakePoint(point),Ge...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function st_makepoint(point) 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: 39
EDIT:
In the short term I can solve it with:
SELECT * FROM triples WHERE box '((point1),(point2))' @> point
But I will have to work out why none of the PostGIS functions are not working for me.
Best Answer
<-- replace coordinates as necessary