1

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

enter image description here

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

  • Nothing weird about that result -- It's the logical outcome of a WITHIN test. You've got multiple questions in this question. Since the "star" selection resulting in duplicate column names isn't GIS-related, I recommend you focus on the correct spatial relation test (ST_Intersects), which will produce a different kind of error (boundary cells being selected twice). – Vince Sep 7 '15 at 17:32
4

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).

  • I've tried running your query as CREATE TABLE hexnew AS SELECT H.gid as hexid, C.gid as cid, cats, dogs, birds FROM hextable H LEFT OUTER JOIN countytable C ON ST_Within(ST_Centroid(H.geom), C.geom)); and I get the right table, however, when I run it on pgsql2shp, I only get a .dbf. It appears the error that's causing this is NOTICE: gserialized_gist_joinsel: jointype 1 not supported. Any thoughts? – Minh Mai Sep 8 '15 at 14:15
  • You will need to include the H.geom in the columns to select, otherwise there is no geometry in your result. – chkaiser Sep 8 '15 at 14:32

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.