1

I'm trying to create a shapefile of a state. I have a map of US zipcodes that I downloaded from the US Census and I want do create a shapefile of zipcodes in Florida.

I ran this query which produces a shapefile that I expected, however when I examine the attribute table in QGIS, I see that there are duplicates of the zip code. The same thing occurred when I modify the query to intersect a specific state using a shapefile of US DMAs (Flordia has 11 DMAs, but the attribute table has 1,107 rows, mostly duplicates). Answers regarding how to fix duplicate rows in an attribute table has come up unsatisfactory. An example of my output is here:

enter image description here

My query is below:

"SELECT geoid10, geom FROM (
    SELECT uszips.geoid10, (ST_Dump(ST_Intersection(uszips.geom, fl.geom))).geom
    FROM uszips, fl
    WHERE ST_Intersects(uszips.geom, fl.geom)) t
WHERE ST_GeometryType(geom) = 'ST_Polygon';
  • Why are you using ST_Dump? It seems that all you want are the zip codes that fall within the Florida polygon boundary? Is that the case? – DPSSpatial Aug 20 '15 at 17:16
1

You're getting duplicates because ST_Dump is breaking up multi geometries and/or geometry collections into constituent parts, each of which has the same ID of the original geometry. So if a MultiPolygon has 5 Polygons, you'll get 5 Polygons with the same ID. You can assign a new ID to each shape if you want, but keeping the original ID will help with joins and grouping, etc.

SELECT row_number() over() as newid, geoid10, geom FROM (
    SELECT uszips.geoid10, (ST_Dump(ST_Intersection(uszips.geom, fl.geom))).geom
    FROM uszips, fl
    WHERE ST_Intersects(uszips.geom, fl.geom)
) t
WHERE ST_GeometryType(geom) = 'ST_Polygon'

row_number is a window function that just generates a unique ID for each row.

Or, union and group by ID.

SELECT geoid10, ST_Union(geom) AS geom FROM (
    SELECT uszips.geoid10, (ST_Dump(ST_Intersection(uszips.geom, fl.geom))).geom
    FROM uszips, fl
    WHERE ST_Intersects(uszips.geom, fl.geom)
) t
WHERE ST_GeometryType(geom) = 'ST_Polygon'
GROUP BY geoidid
|improve this answer|||||
  • I'm fairly new to postGIS, how would I then do that with the query? – Minh Mai Aug 20 '15 at 16:54
  • See my edit re: row_number. – Rob Skelly Aug 20 '15 at 16:58
  • I got the same results. How would I remove duplicate zip codes name but still maintain the shape? – Minh Mai Aug 20 '15 at 18:51
  • Union the shapes back together, as in my edit (which I have not tested...) – Rob Skelly Aug 20 '15 at 20:43

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.