[GIS] Geocode US ZIP codes in QGIS

geocodingqgissql server

I have a list of zip codes (nearly thousands) which I want to show in qgis. After researching I found that I need to geocode them first into Lat/Long then I can use them to create a layer for qgis.

I have the zip codes in an excel file and I have imported it in sql server 2008.

Here is the table :

Is there a plugin or an easier way to show these zip's in QGIS?

Best Answer

You could download the US Census Zip Code Tabulation Areas. Then join your Zip Code (numbers) to the ZCTA's and ultimately give you the spatial polygons that generally represent the Zip Code boundaries. While not a 1-to-1 match (because the US Postal Service does not publish their zip code boundaries), it should get you close.

Related Solutions

[GIS] Find distance from a point to linestring

First of all you need to make sure that both your coastline features and points are in the GEOGRAPHY datatype and that the coordinates are lat/longs.

Alternatively make sure all your features are in the GEOMETRY datatype and all coordinates are projected (to Mercator, a National Grid etc.).

Then using the STDistance method you should be able to use a query similar to:

SELECT pointtable.ID,
MIN(pointtable.GEOG.STDistance(shapetable.GEOG)) as MinDistance
FROM pointtable, shapetable
GROUP BY pointtable.ID

GEOG field names should be replaced with the field names containing the GEOGRAPHY data.
ID field names should be replaced with the appropriate ID field for the table.

If you want to see the actual string represenations of a GEOGRAPHY field use SQL such as:

SELECT shapetable.ID,
shapetable.GEOG.STAsText()
FROM shapetable

[GIS] Create a geography/geometry column from x and y fields – SQL Server 2008

You can add a computed column like this

alter table yourTable add geographyColumn as geography::STGeomFromText('POINT('+convert(varchar(20),Long)+' '+convert(varchar(20),Lat)+')',4326)

I have add the conversion from lng or lat because I store the Long and Lats as numbers.

Best Answer

Related Solutions

[GIS] Find distance from a point to linestring

[GIS] Create a geography/geometry column from x and y fields – SQL Server 2008

Related Question