[postgis-users] Polygon from point query

Peter N. Schweitzer pschweitzer at usgs.gov
Tue Dec 28 12:08:25 PST 2010


On 12/28/2010 02:49 PM, Poynter, David wrote:
> This is probably an easy one, but being a PostGIS newb I've been spinning my wheels and haven't the google fu to locate an answer, so here goes...
>
> I have a table of counties from the state (county) and I'm creating a point to insert in another table and I would like to find the county the point falls in.
>
> I think I should be able to do it in one query rather than looping thru county by county, but I can't seem to put it together.
>
> SELECT county_nam FROM county WHERE ?
>
> I've been looking at ST_Within() and think the answer lies with it somehow, thanks for any clues...

I do this in PHP, and am using a query like this:

select fips from county
where the_geom && PointFromText('POINT($longitude $latitude)',4269)
and ST_within(PointFromText('POINT($longitude $latitude)',4269),the_geom)

Since this is a string in PHP, the X coordinate will go where I've
written $longitude and the Y coordinate will go where I've written
$latitude.

The county table in this case is the countyp020 shapefile drawn from
the US National Atlas at nationalatlas.gov.  In my case what I want is
the FIPS code of the county.

You can actually try this out online using syntax like this:
http://tin.er.usgs.gov/atlas/point-xml.php?latitude=39&longitude=-77
That service will run your point against a variety of other base polygon
layers, as you can see.

Peter
-- 
Peter N. Schweitzer (MS 954, U.S. Geological Survey, Reston, VA 20192)
(703) 648-6533  FAX: (703) 648-6252  email: pschweitzer at usgs.gov
<http://geology.usgs.gov/peter/>



More information about the postgis-users mailing list