Finding all polygons inside of a polygon

Question

How to find all the polygons inside of a polygon? I tried ST_Intersects and ST_Contains but they are booleans variables. I have a list of polygons in WKT format, so I am trying to pull all the polygons given a region in their WKT format.

Does anyone have an idea how to approach this?

select geom from polygon where 
ST_Intersects(ST_GeographyFromText
('POLYGON((210000 2400000, 300000 2300000, 330000 3708400, 210000 2400000))'), 
polygon.geom);

What is the end result you are looking for? Have you looked at ST_Intersection? — kflaw, Jun 07 '16 at 14:25
The result of your ST_geografyFromText is "POLYGON((120 -60,120 -40,-120 40,120 -60))" . Is it correct ? This cast is probably not what you want... — WKT, Jun 07 '16 at 14:34
@kflaw the end result should be a list of polygons in WKT formats that are inside of the given polygon. — moffittime, Jun 07 '16 at 14:36
now I tried : ST_Intersection(ST_GeomFromText('POLYGON((210000 2400000, 300000 2300000, 330000 3708400, 210000 2400000))'),geom) from polygon and I am getting all GeometryCollection Empty. I checked the data and make sure those number in the polygon would intersect with a least one polygon. Is there something I did wrong? — moffittime, Jun 07 '16 at 16:06
I think ST_intersection is not what you want. What is the CRS of these coordinates ? — WKT, Jun 07 '16 at 17:17
I posted an answer below, which should work for you if both of your datasets are in the same coordinate system — kflaw, Jun 07 '16 at 17:35
@moffittime It can't. Look at bounds here: http://spatialreference.org/ref/epsg/wgs-84/ — WKT, Jun 07 '16 at 18:08
@WKT thank you! I just realize my CRS isn't set it correctly — moffittime, Jun 07 '16 at 20:10
ST_Intersects or ST_Contains will give different results. ST_Intersects will also return polygons that are only partially contained, or even just touch. ST_Contains is more restrictive. — amball, Jun 09 '16 at 07:51

kflaw · Accepted Answer · 2016-06-07T22:33:01.733

2

You are missing ST_AsText, as @WKT mentions above. This should work for you:

select ST_AsText(geom) from polygon
where ST_Intersects(ST_GeographyFromText
('POLYGON((210000 2400000, 300000 2300000, 330000 3708400, 210000 2400000))'), 
polygon.geom);

edited Jun 07 '16 at 22:33

answered Jun 07 '16 at 15:18

kflaw

1,416
17
41

why the downvote? I tested this with my own data (using different coordinates) and it worked. – kflaw Jun 07 '16 at 18:45

Finding all polygons inside of a polygon

1 Answers1

Linked