How to import data with SRID 4269 to GEOGRAPHY type?

Question

In PostGIS, we are importing various US census bureau shapefiles (with SRID = 4269) using shp2pgsql loader. I have medium-sized database with several tables containing fields of type geography (lat/lon points, polygons, multipolygons, etc) that span locations across the US on which we do various distance calculations, “within” polygon, etc type of geographic functions as well as display data using Google Maps Api. We currently have no geometry type fields (only SRID used in database is 4326 via geography type).

Because they cover a fairly large area and to keep things simple and consistent (avoid projection details etc), I would like to store the census shapes (ie boundaries for states, metros, counties,block groups..) in geography data type instead of geometry as well (unless there is a reason NOT to do this or someone can explain why we should store as geometry instead).

My question is, if I use shp2pgsql loader with –G option to create a MULTIPOLYGON geography field for these boundaries (and thus use a 4326 SRID) does the loader “convert” the data from a 4269 projection to 4326? Is a conversion really necessary? Do I need to first load into temp table with geometry type then perform update to transform/cast into geography type and then store that result?

Thank you for any advice or insight related to this.

Answer 1

Do you need to use geography data type... well... you can still store geographic coordinates (lat lng) in a geometry column. Geographic coordinates are not exclusive to geography type in postgis. The difference is how certain functions behave, like distance measurements, etc... Read more here:

http://postgis.refractions.net/documentation/manual-1.5/ch04.html#PostGIS_Geography

Bottomline, if you want precise calculations on geographic coordinates, use geography type, otherwise use geometry type. Note that by using geography type you will loose some of the functions which are not implemented for that type. Another note: because in a small area, a sphere (which is the base of calculations of geography type) is similar to a plane, so the difference between the results of measurements using either data type wont be big, but they of course increase with size of your spatial objects as a plane doesn't represent the surface of a sphere as the area of interest grows

About the data import, there are 2 options:

1. Import data as is... then reproject in the database (add a column with geography type, and use builtin postgis functions to reproject through a query)
2. Use shp2pgsql with -s from_srid:to_srid check this link http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide_20.bqg

Answer 2

shp2pgsql can reproject (provided you have proj installed)

first you create the sql dump:

shp2pgsql -s 4269:4326 -g geom -I  <schema>.<dbname> > dump.sql

then you load it at your postgis db:

psql -f dump.sql

you're done

If you already have the data in postgis a better way is to create a new geom collumn with another SRID:

-- create the column:
ALTER TABLE <schema>.<tablename> ADD COLUMN geom_4326 geometry;
-- Populate it:
UPDATE <schema>.<tablename> set geom_4326 = st_translate(geom,4326);

Answer 3

I believe PostGIS geography data type handles only 4326 SRID at the moment (it requires lon/lat data in WGS84), so your options:

1.Convert the 4269 SRID data to 4326 projection yourself before loading it to PostGIS using shp2pgsql with the -G option to get the final 4326 Geography data,

2.or load the 4269 SRID data without the -G option,

shp2pgsql -r 4269 -s 4326 schema.table | psql -h host -d database -U user

and change the geometry data into geography within PostGIS using Geography(ST_Transform(geometry,srid)) function. There's an example on how to do this in http://workshops.opengeo.org/postgis-intro/geography.html

Hope it helps.