In class exercises - Class #7 Coordinate Systems

Part I - Slides

SELECT *
FROM geometry_columns;

SELECT *
FROM spatial_ref_sys
WHERE SRID=2263;

SELECT *
FROM spatial_ref_sys
WHERE srtext LIKE 'GEOGCS["NAD83%';
then variation with PROJ

SELECT *
FROM spatial_ref_sys
WHERE srtext LIKE '%New York%';

Part II - Tract Population Centroids Example

--Create temporary staging table for import
CREATE TABLE nyc.tract_temp (
statefp varchar(2),
countyfp varchar(3),
tractce varchar(6),
population integer,
latitude numeric(9,6),
longitude numeric(9,6)
);

--Create ideal, final table
CREATE TABLE nyc.tract_popctr (
geoid varchar(11) PRIMARY KEY,
tract varchar(6),
pop2010 integer,
lat numeric(9,6),
lon numeric(9,6),
geom geometry(point,2263)
);

\copy nyc.tract_temp
FROM 'C:\workspace\CenPop2010_TR36.txt' WITH DELIMITER AS ’,’ CSV HEADER
OR just use PgAdmin GUI

INSERT INTO nyc.tract_popctr (geoid, tract, pop2010, lat, lon, geom) 
SELECT (statefp || countyfp || tractce), tractce, population, latitude, longitude, 
ST_Transform(ST_SetSRID(ST_Point(longitude,latitude),4269),2263)
FROM nyc.tract_temp
WHERE countyfp IN ('005','047','061','081','085');

DROP TABLE nyc.tract_temp;

Shapefile - load zctas file

--Transform geometry from NAD83 to NYSP
ALTER TABLE nyc.zctas
ALTER COLUMN geom TYPE geometry(multipolygon,2263)
USING ST_Transform(geom, 2263);

--Just to demonstrate how spatial view would work
CREATE VIEW nyc.zctas_bronx AS 
SELECT zcta, bcode, geom
FROM nyc.zctas
WHERE bcode='36005';

Display in QGIS