SQL Query for Point-In-Polygon Using Postgresql

SQL query for point-in-polygon using PostgreSQL

The polygon is a fundamental Postgres type which PostGIS builds on top of. You enable the geometry columns with the PostGIS function select AddGeometryColumn(...). Otherwise you are working with straight polygons:

=> create table gt (id int, space polygon);
=> insert into gt values (1, '((2,2),(3,4),(3,6),(1,1))');
INSERT 0 1
=> select point(space) from gt where id = 1;
    point    
-------------
 (2.25,3.25)
(1 row)

This is the center point of the polygon

=> select circle(space) from gt where id = 1;
             circle             
--------------------------------
 <(2.25,3.25),1.93994028704315>
(1 row)

This is the minimum bounding circle of the polygon, expressed as a Postgres circle type. All the geometric operators are documented here: http://www.postgresql.org/docs/8.3/interactive/functions-geometry.html The base polygon does not have any projection data, SRID, etc., so if it works with PostGIS it is probably just defaulting to presets and getting lucky. But of course there are tons of cases where you simply need geometry on a sub-geospatial scale.

For a list of points, is the point inside any polygon or not

Just use the function ST_Contains with both geometries. Depending on your use case, check ST_Overlaps, ST_Touches or ST_Intersects

Select all points contained in the polygons

SELECT ST_AsText(poly.geom), ST_AsText(poi.geom)
FROM poi,poly
WHERE ST_Contains(poly.geom,poi.geom);

                st_astext                 |                st_astext                 
------------------------------------------+------------------------------------------
 POLYGON((30 10,40 40,20 40,10 20,30 10)) | POINT(28 27)
 POLYGON((30 10,40 40,20 40,10 20,30 10)) | POINT(24.1330003363259 37.3570074902601)
 POLYGON((35 15,38 30,20 35,35 15))       | POINT(28 27)
 POLYGON((35 15,38 30,20 35,35 15))       | POINT(34.3933104907027 18.7290161619684)
(4 Zeilen)

Retrieve a boolean value stating if the points are contained in any polygon in the foreign table

SELECT 
  id, 
  EXISTS (SELECT * FROM poly WHERE ST_Contains(poly.geom,p.geom)) 
FROM poi p;

 id | exists 
----+--------
  1 | t
  2 | f
  3 | t
  4 | f
  5 | f
  6 | t
(6 Zeilen)

Further reading:

• Subquery Expressions

How to search for all points inside all polygons in Postgres

It appears that you are using PostgreSQL's built-in point and polygon types, but you want to use a PostGIS function ST_Crosses that takes the PostGIS geometry type as arguments.

If so, start here: SQL query for point-in-polygon using PostgreSQL

Note that PostGIS is an add-on to PostgreSQL. You may not have it installed, and may need to install it to get the ST_Crosses function (and a lot more).

Find all polygons containing a point in PostGIS

You need to use array_agg in conjunction with ST_Contains, see aggregate functions for more on array_agg and similar functions. If you combine this with a group by on your points table, you will get an array of all the polygons containing those points, eg,

SELECT pl.id, array_agg(ar.id ORDER BY ar.id) as area_list
FROM areas ar, places pl 
WHERE ST_Contains(ar.geom, pl.geom) 
GROUP BY pl.id
ORDER BY pl.id; 

Note, you can put an Order By inside array_agg, so that the area IDs always appear in the same order. I have called both table's primary keys, id, and both table's geometries, geom, so you might have to adjust for your set up.

---

Related Topics