I have two set of shape data. Let's say file gov.shp contains list of post codes and their boundaries. File post.shp contains list of government area name and their boundaries. I want to find out the the union (may not be the correct word here) between two files (i.e. post code 1, 2 and 3 overlaps with government area a; post code 1 also overlaps with government area b). Bear in mind that government area is bigger than post code area. So I have a query like below:
select g.id, g.name, p.id, p.code from gov g inner join poa p on ST_Overlaps(g.geom, p.geom) = true
However if I apply this where clause, to my surprise the count(*) actually returns results. I am confused by why total area of g + p - union_area of(g,p) can be negative...
where ST_Area(g.geom) + ST_Area(p.geom) - ST_Area(ST_Union(g.geom, p.geom)) < 0
Am I using the wrong function (ST_Overlaps)? Should I be using ST_Intersecs/ST_Equals/other functions?
I am new to this area so I am not too sure what the differences are in terms of solving my particular problem.
I imported shape file as geometry and MULTIPOLYGON object and I tried different results in both PostGIS and SQL Server 2008 R2 and both produced slightly different results. The above sql query syntax is in PostGIS though.
--SQL Server 2008 -- STOverlaps -- DIFF < 0 = 27 -- DIFF > 0 = 1646
--SQL Server 2008 -- STEquals() -- 42
--PostGIS -- DIFF < 0 = 730 -- DIFF > 0 = 5312
--PostGIS -- STEquals() -- 0
SQL Server 2008 syntax
select g.id, g.name, p.id, p.code from gov g inner join poa p on g.geom.MakeValid().STOverlaps(p.geom.MakeValid()) = 1 --for some reason I had to use *.geom.MakeValid() where g.geom.MakeValid().STArea() + p.geom.MakeValid().STArea() - g.geom.MakeValid().STUnion(p.geom.MakeValid()).STArea() < 0
I used Shape2SQL to import into SQL Server 2008, Planer Geometry, didn't check SRID, create spatial index.
And I used PostGIS Shapefile Import/Export Manager which came with PostGIS 2.0 to import into PostGIS, and all default settings.