There is the big and exhaustive PostGIS which can be installed into your PostgreSQL database and will provide you with lots of geometry functions and types you can use for your complicated geometry problems.
But what if you need, like I needed, to find out wether a point is inside a described polygon or not and you don’t want to go into all the trouble of installing PostGIS?
Well, good news. As I was happy to learn, PostgreSQL has native support for some geometry types and operators.
For instance, it supports the type polygon, which has the following syntax:
( (x1,y1) , (x2,y2), ... , (xn,yn))
It also supports the following operator which means “contains”
So, let’s put this two together and make a functional example:
-- postgresql demo for point-in-polygon with native support create table test_polygon ("id" serial, "data" polygon NOT NULL PRIMARY KEY ("id)); insert into test_polygon (data) values ('((1,1),(12,1),(12,12),(1,12))'); -- example of query which will validate a point as being inside the polygon select * from test_polygon where data @> '(3,4)'; -- example of query which will invalidate a point as being inside the polygon select * from test_polygon where data @> '(24,14)';
The code is simple and powerful. If you combine it with an online tool for generating a list of coordinates for a polygon drew on a map, you can easily create systems which will compute if a GPS point is inside that map polygon.
As for PostgreSQL’s native support for geometry stuff, (which I believe is great), you can read more on the manual here: http://www.postgresql.org/docs/9.3/static/datatype-geometric.html and here: http://www.postgresql.org/docs/8.2/static/functions-geometry.html.