[postgis-users] Legislative District Lookup

[Graham Stewart]

JC

Try this...

select astext(the_geom) from mn_cong_2002

You'll see that the geometry isn't in a standard lat/long format, the
points that make up the polygons are in a different coordinate system. I
think, but don't take my word for it, that they are UTM15 / SRID 2027.

Generally you should fix this on load, using the -s parameter to set the
correct srid on your input data.

Assuming you don't do that then give this a shot for the comparison

  SELECT district FROM mn_cong_2002 
  WHERE 
	    transform(setsrid(the_geom,2027),4269) && setsrid(makepoint(-93.294325, 44.949265),4269)
	and contains(transform(setsrid(the_geom,2027),4269), setsrid(makepoint(-93.294325, 44.949265),4269)) ; 


Essentially we cast the_geom field into SRID 2027, then translate it
into 4269 where it can be compared to the constant point (which we need
to also cast into 4269 for the comparison to work). Once the bounding
box test has been done, we can append the more expensive contains()
function to get a more accurate result.

Once you've got that working you should really focus on loading the
correct SRID. Mainly because it keeps the database cleaner, but also
because the you can do one transformation on the input point (to bring
it into 2027) instead of transforming each of the districts.

Hope that helps

Graham


J.C. Quirin wrote:
> I am trying to code up a legislative district finder for the upcoming
> elections.   My plan is to have the user enter their address, pass
> that through the free geocoder.us system, take the lat/long
> coordinates that it returns, and do an intersect query to the PostGIS
> system.    I have loaded a PostGIS db with a couple of the shape files
> from this site:
> http://www.commissions.leg.state.mn.us/gis/html/download.htm
> (I am currently doing my testing with the congressional district one
> -- found in con02.zip -- because I figure that should be most
> forgiving.)  I loaded that data into a table named mn_cong_2002 by
> using the command:
> shp2pgsql c2002.shp mn_cong_2002 > mn_cong_2002.sql
> (and then imported that sql into the db, of course)
>
> The query I am using (hard-coded for now) is as follows:
> SELECT district FROM mn_cong_2002 WHERE (the_geom &&
> makepoint(-93.294325, 44.949265));
>
> Unfortunately, it's not returning anything (despite those coordinates
> being right smack-dab in the middle of Minneapolis).
>
> I'm a complete newbie when it comes to this stuff, so I don't assume
> at all that my queries are correct.  I have checked the coordinates w/
> google maps, and those are correct, but I'm not sure if I'm doing the
> makepoint() correctly.
>
> Any ideas?
>
> Thanks in advance!
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users