I am having a problem with postgres' geometric functions. Basically I have a polygon type stored in a postgres database and I want to check if a point that I have is located within this polygon. Postgres has a built-in function for this, but I'm having a bit of trouble with the syntax. Really it shouldn't be that hard to figure out but I'm hoping someone here has either worked with these functions before, or just happens to know. I'm somewhat new to SQL in general so it might just be some generic problem.
Add a comment
|
2 Answers
There is an example of this in the documentation, see http://www.postgresql.org/docs/9.1/static/functions-geometry.html, table 9-30.
As for the syntax, you are probably looking for something along the line of
SELECT * FROM yourtable WHERE yourcolumn @> point('1,1');
Comments
ST_Contains can be used in conjunction with st_geomfromtext to determine if your point is in that polygon. I have a table with the column 'poly' holding a polygon and another table with list of lat/lon values to check are within that polygon. You join the table holding the polygon to the table holding the list on 1=1 (gives you lat/lon/poly on each line returned) and then build the st_ statements.
st_contains(poly, point)
This takes the point defined and checks if it's in the polygon (I called mine 'poly'). It returns 't' if the point is within that polygon.
st_geomfromtext('POINT' st_geomfromtext('POINT('||lon||' ' ||lat ||')')
This takes a lat and lon value and translates it to a point.
Put the two together in a where clause:
where st_contains(poly, st_geomfromtext('POINT('||lon||' ' ||lat ||')')) = 't'
Then it's just a matter of building out the select clause...I could help with that, but you neglect schema in your question.
