0

I have two land use maps for London which have an overlapped area.

Can I use the st_union function in postgis to join them together?

It looks like this from QGIS.

The attribute table contains information includes legends and building heights.

The attribute table contains the same information for these two maps

Will this method affect my features?

For example, in the overlapped area, will the build heights become 2 times of the original?

Data from postgres have the same columns and same type of data.

both data are displayed in the postgres with attributes like this

After using @CL.'s method, I got this map. Only some features near the boundaries were deleted which needed to be fixed.

Joint boundaries of two maps.

Improve this question
12
  • ST_Union affects only the geometry value; the other values do whatever you have specified in the SQL query. Anyway, do the objects in the two tables actually overlap? Are the geometries identical? Do the columns always have identical values? Is there some column that identifies them? Show some example data from both, and what the result should be. – CL. Oct 8 '18 at 13:21
  • Yes, the objects are overlapped. I was doing buffer and intersects and the results were larger than I expected. Then I came back and had a look then found out the overlapped. I will take some pictures from postgres. The result should be for each geom have one idential legend and building height values. thanks – ay__ya Oct 8 '18 at 13:27
  • You answered only two of my four questions. – CL. Oct 8 '18 at 14:53
  • Geometries are identical, and geometries columns have identical values. Other columns have repeat values. – ay__ya Oct 8 '18 at 16:17
  • So you have identical rows? Don't you just want to throw the duplicate rows away? – CL. Oct 8 '18 at 16:27
1

To check whether there are any overlapping objects, use ST_Intersects:

CREATE TABLE Table3 AS

SELECT *
FROM Table1

UNION ALL

SELECT *
FROM Table2
WHERE NOT EXISTS (SELECT *
                  FROM Table1
                  WHERE ST_Intersects(Table1.geom, Table2.geom));
Improve this answer
6
  • thank you very much for your answer. It works well on my case. Only some features near the boundary of the two maps were deleted which I need to fix. – ay__ya Oct 9 '18 at 10:38
  • What happens when you exchange Table1 and Table2? If there is no difference, try adding AND NOT ST_Touches(...). – CL. Oct 9 '18 at 12:27
  • I think some features partially intersect each other. – ay__ya Oct 9 '18 at 12:33
  • Hello @CL., still wondering why would the exchange make such big differences? thanks – ay__ya Oct 9 '18 at 16:12
  • It appears only the objects in the bottom table were clipped at the line. – CL. Oct 9 '18 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.