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

[Obe, Regina]

 Couple of things that seem odd -- 

1) If the_geom is a polygon I think you may have your contains check
backward the polygon contains the point not the point containing the
polygon 
2) Why are you using SetSRID and transform - the fact that you use
SetSRID is already telling Postgis it is in 4269 so to then transform
seems a little redundant to me

Try

SELECT zcta, contains (the_geom, SetSRID(MakePoint(-117.119403,
32.771608),4269) as areacontainspoint from ziptest where zcta = 92116;

Hope that helps,
Regina


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Matt
Bartolome
Sent: Thursday, March 01, 2007 1:40 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Re: zcta census data, help with query

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
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.