[postgis-users] Re: zcta census data, help with query

[Matt Bartolome]

Ok, so I went back and looked over what I had done to import the data
and one stupid error I think I made was here:

shp2pgsql -s 2230 -d zt06_d00.shp public.ziptest > zip.sql

Census docs say that the SRID for the zcta data is actually 4269
(lon,lat). So I re-imported the generated sql with the correct srid
param and tested the data with the query:

select zcta, x(centroid(transform(the_geom, 4269))) as longitude,
y(centroid(transform(the_geom, 4269))) as latitude from ziptest where
zcta = 92116;

 zcta  |     longitude     |     latitude
-------+-------------------+------------------
 92116 | -117.121819788206 | 32.7655437135842

The point appears where I think it should however I still get false
when doing my within or contains queries:

SELECT zcta, contains (Transform(SetSRID(MakePoint(-117.119403,
32.771608),4269),4269), the_geom) from ziptest where zcta = 92116;
 zcta  | contains
-------+----------
 92116 | f
(1 row)

Thanks to anyone who can help me out,
Matt

On 2/28/07, Matt Bartolome <mattxbart at gmail.com> wrote:
> Hello List,
>
> I have loaded zt06_d00.shp (california zcta) data from
> http://www.census.gov/geo/www/cob/z52000.html
>
> Using the shp2pgsql utility:
> shp2pgsql -s 2230 -d zt06_d00.shp public.ziptest > zip.sql
>
> I am able to load the data into my postgres database with no problem.
> Dumping shapefiles and some other things I've tried work great.
>
> My problem right now is doing the query:
> SELECT zcta, WITHIN (Transform(SetSRID(MakePoint(-117.119403,
> 32.771608),4269),2230), the_geom) as within from ziptest;
>
> This should fall within zipcode 92116 but it is showing false. Can
> anybody "point" me in the right direction?
>
> Thanks,
> Matt
>