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

[Matt Bartolome]

Regina,
That did it. Wonderful. The transform was actually masking the error I
made when I flip flopped the args.

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

 zcta  | areacontainspoint
-------+-------------------
 92116 | t

Thanks!

On 3/1/07, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
>  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.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>