[postgis-users] point-in-poly query

[Michael Fuhr]

On Tue, Jan 17, 2006 at 10:03:35PM -0700, John.C.Cartwright at noaa.gov wrote:
> given a table of country polygons and a table of city points, I'm trying
> to find all the cities that fall w/in a given country.  The query listed
> below runs slowly and also produces incorrect results.  Can someone
> correct what I'm doing wrong?
> 
> select cities.city_name,cities.cntry_name
> from cities p, country a 
> where p.shape && a.shape and 
>   intersects(p.shape,a.shape) and 
>   a.gmi_cntry = 'USA';   

This query should produce a warning like

NOTICE:  adding missing FROM-clause entry for table "cities"

That's because you use table aliases in the from list but then refer
to one of the original table names in the select list.  Try using
p.city_name and p.cntry_name in the select list.

To avoid these kinds of mistakes, disable add_missing_from in your
server configuration -- that'll give an error instead of a warning
an unexpected results, forcing you to fix the query (that setting
is off by default since PostgreSQL 8.1).

-- 
Michael Fuhr