Importing column from one shapefile to another using PostGIS?

Question

So I have two shapefiles: one is a hexbinned shapefile with data of how many dogs, cats, and birds in it and another with county lines.

How would I merge the two shapefiles to show which county each hex bin is in? I've tried the following query

create table newhex AS (select * from hexes, counties where ST_Within(hexes.geom, counties.geom));

but I get the error Error executing user query: ERROR: column "gid" specified more than once

I'm assuming that there are two difference geom columns and that conflicted the geom columns. So I tried another query with explicit selecting the the geom column of the hex bin and the county name with:

select shp_ohhexsm.geom, ohcounties.name from shp_ohhexsm, ohcounties where ST_Within(shp_ohhexsm.geom, ohcounties.geom)

But I get this weird result

Is there any way I can just merge the name of the counties into my hex files without the weird spaces?

Answer 1

You have two issues that you need to consider:

1. Your small hexagonal polygons are still polygons and can overlay two county polygons, with ST_Within returning false. You will probably want to reduce these hexagonal polygons to a point, e.g. the centroid. This can be achieved with the ST_Centroid function.

2. The join itself based on geographic location which can be solved with a join statement as follows:

   SELECT * 
   FROM hexes H LEFT OUTER JOIN counties C 
   ON ST_Within(ST_Centroid(H.geom), C.geom);
   

   The left outer join makes sure you don't drop any hexagonal polygon where the centroid might be outside of your county geometries, by returning a NULL value.

The last problem you need to solve is related to the CREATE TABLE AS ... statement where you cannot have two identical column names. You need to list your columns manually and you might need to rename a column with the col_name AS new_name syntax like here:

SELECT H.gid AS gid_hex, C.gid AS gid_county
FROM ...

(assuming the same query as before and you have two gid columns, and you want to keep both of them).