What command to use in PostGIS to get same result as ArcGIS?

Question

I have two shapefiles and i'm trying to do simple spatial analysis. When I want to know which polygon belongs to other polygon in other shapefile I always use select by location and in Spatial selection method i choose contain the source layer feature.

Now i have these two shapefiles in postgis and i'm trying to do the same using SQL Query but i got these results:

Select * from state where ST_Contains(geom, (select geom from region where REGION_N='CAPITAL_REGION'));

=0 results, in ArcMap i got 1 result.

I also tried

ST_ContainsProperly

=0 results

When i tried

ST_Intersects

= 3 results, the same in ArcMap. So it looks like intersects works but "ST_Contains" doesn't work for some reason.

Where could be with SQL problem?

Answer 1

I could reproduce thew issue with your shapefile data. I used ogr2ogr for converting the shapefiles into PostGIS tables and your query finds nothing.

Select * from state where ST_Contains(geom, (select geom from region where REGION_N='CAPITAL_REGION'));

Next I opened your shapefiles with OpenJUMP and made some tests. Both layers have some topology errors (ring self-intersections) which can make trouble for spatial functions but in this case I think that the issue comes from very little differences in the coordinates of corresponding vertices in those two shapefiles. Here is an example:

Layer: region
Feature ID: 50
[      0:44] POINT (103.86042442121341 1.343437997354843)
[      0:45] POINT (103.86043696024463 1.3434034315038)

Layer: State
Feature ID: 48
[    7:0:45] POINT (103.86042442121337 1.3434379973548598)
[    7:0:46] POINT (103.86043696024463 1.3434034315038228)

The coordinates differ in 12th coordinate place or so which is very very little but they still differ. When PostGIS is making computations I believe that it finds that some vertices of CAPITAL_REGION are outside the polygon of STATE_1 and therefore ST_Contains if "false".

What I do usually in cases like this is to reduce the accuracy of vertices first to some reasonable measure. Six or seven decimals could be a good start (see https://en.wikipedia.org/wiki/Decimal_degrees).

The main reason for the trouble is perhaps that vertices which have the same coordinates in ArcMap get written with slightly different values into "State" and "regions" shapefiles. Or perhaps ArcMap is handling internally coordinates with some tolerance but PostGIS is using the full accuracy in analysis.