Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
The preferred method is to post in one place only. If that place is inappropriate or doesn't work out, it can easily be migrated. I'm not going to take any action, because GIS is where your question should be, but I would urge you to delete the cross post on SO. –  whuber Jul 14 '11 at 13:56
1  
@whuber .. done. –  Ankur Jul 14 '11 at 14:06
    
Does -- select GeometryFromText('POLYGON((75 20,80 30,90 22,85 10,75 20))',4326) -- work? –  Sean Jul 14 '11 at 14:45
    
I am not sure what you mean. I tried a variety of different variations of what you said and they didn't work –  Ankur Jul 14 '11 at 14:48
    
What's the "point" column your referencing in ST_MakePoint(point) –  underdark Jul 14 '11 at 14:51

2 Answers 2

up vote 7 down vote accepted
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

share|improve this answer
    
thanks, trying now. –  Ankur Jul 14 '11 at 14:06
    
sorry for the stupid question, but what is 'the_geom' ... am I supposed to alias the SELECT * FROM myTable query and does that value become 'the_geom' –  Ankur Jul 14 '11 at 14:09
    
Sorry, of course it's the column being searched. I would call it db_column or something like that, but postgis docs think otherwise ... it makes sense when you know what it is. –  Ankur Jul 14 '11 at 14:15
    
When I run this, I am getting an error that says GeometryFromText does not exist. I'm using postgis 2.0. I also tried st_geomfromtext. –  picardo Apr 8 at 18:15

Sounds like you want the BBOX (Bounding Box) from your points - so ST_Extent would be favourable.

BBOX2D

http://www.bostongis.com/postgis_extent_expand_buffer_distance.snippet

would provide the http://postgis.net/docs/ST_Extent.htm page but the server is having issues

share|improve this answer
    
re:server ... I know, I've been trying to look at Google's cache copies :) –  Ankur Jul 14 '11 at 14:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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