[GIS] Find points that lie within a set of coordinates

extentspointpostgissql

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

SELECT * FROM myTable WHERE 
ST_Within(the_geom, GeometryFromText ('POLYGON((75 20,80 30,90 22,85 10,75 20))', 4326))

<-- replace coordinates as necessary

Related Question