2

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

ziggy
  • 4,515
  • 3
  • 37
  • 83
  • Your question is a bit confusing, do you want to merge different schema shps into one shp (before in postgres) or merge different schema postgres tables into one postgres table? – artwork21 Aug 31 '16 at 17:34
  • in postgres i want to merge the shapefiles into one shapefile – ziggy Aug 31 '16 at 17:36

1 Answers1

3

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
kttii
  • 3,630
  • 1
  • 16
  • 36