1

I'm trying to create a shapefile of the state of zip codes (in this example, I pick Virginia). However, the zip codes do not line up exactly to the state borders. So I run the following query to get a shapefile of the zip codes for the given state.

SELECT uszips.geoid10, uszips.geom FROM uszips,va_shp
WHERE ST_Intersection(uszips.geom, va_shp.geom) 
GROUP BY uszips.geoid10, uszips.geom

However, when I checked the shapefile, it looks like this: enter image description here

When the state of Virginia looks like this:

enter image description here

Is there any way I can return the shapefile with the zip code geom truncated to match the exact borders of the state?

  • 1
    Some ZIP codes span multiple states. – Maksim Aug 18 '15 at 19:12
5

ST_Intersects and ST_Intersection are two different things. The former is a true/false check, and the latter returns the intersection. In your query, you're returning the whole geometry, rather than the intersected geometry, when the intersected geometry is not null. Not an error, but not what you want.

Try this:

CREATE TABLE zips AS 
SELECT geoid10, geom FROM (
    SELECT uszips.geoid10, (ST_Dump(ST_Intersection(uszips.geom, va_shp.geom))).geom
    FROM uszips,va_shp
    WHERE ST_Intersects(uszips.geom, va_shp.geom)
) t 
WHERE ST_GeometryType(geom) = 'ST_Polygon';
  • Hrmm, I tried running the query and everything looks normal except for this output: Dumping: XXUnknown WKB type (7) for record 110. I tried opening the shape file and it's empty. Not sure what happened. – Minh Mai Aug 18 '15 at 19:29
  • I think 7 is GeometryCollection. It's possible the output is mixed (e.g. Polygons and collections of Polygons.) I've added an update that might help, by dumping any multi- geometries or collections. If you provide a download link I could test it. – Rob Skelly Aug 18 '15 at 19:43
  • No luck. The file still comes up empty. I've linked the file for your convenience; the zip file can be found here while the Virginia file can be found here – Minh Mai Aug 18 '15 at 19:50
  • I got it to work, but I had to filter out all the non-polygon results. Because some neighbouring zip areas touch the state boundary only on an edge or a point, you'll get degenerate geometries (lines and points) in the output. What are you actually using to create the shapefile? – Rob Skelly Aug 18 '15 at 21:06
  • What do you mean? I download the files from the US census and then upload them using shp2pgsql-gui and then I attempt to run the following query. – Minh Mai Aug 18 '15 at 22:10
2

You can use the Virginia shapefile to Clip the Zip Code shapefile. Note: This will cut the polygons and you will not have the exact geometry of each zip code area.

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.