[GIS] Merging multiple shapefiles into one shapefile

mergepostgispostgresqlsqlunion

I have 5 shapefiles in Postgres. i am trying to merge them into one shapefile but each shapefile has a different amount of columns and data types. I am aware the union and union all will not work if there are different amounts of columns and data types. they all have the same SRID

select * into newtable
from(select * from schools
    union all
    select * from childcare
    union all
    select * from infrastructure
    union all
    select * from nursinghomes
    union all
    select * from hospitals 
    )t;

I am wondering if there is work around that can merge or smash the shapefiles together similar to the merge tool in arcgis which will do this

Best Answer

When you do your unions, each table being unionized must contain the same amount of columns with the same column names.

To simplify your statement so that you understand, I give you this example where the column names are the same:

select 
  id,
  geom
from nursinghomes
    union all
select 
  id,
  geom
from hospitals

this has the effect of removing data from both table that you may want to retain, so you could try something like this:

select 
  id,
  geom,
  nursinghomesname as name,
  nursinghomedoctor as misc1 
from nursinghomes
    union all
select 
  id,
  geom,
  hospitalname as name,
  '' as misc1 
from hospitals
Related Question