[postgis-users] Question: How to Select all Points inside a specific polygon

[chodgson at refractions.net]

I would think that something like this would work:

SELECT user.name, user.the_geom 
FROM user
WHERE within( user.the_geom, (
        SELECT the_geom 
        FROM Country 
        WHERE Country.name = "Sweden" )
    ) AND the_geom && (
        SELECT user.the_geom 
        FROM Country 
        WHERE Country.name = "Sweden" )

The part after the AND is an extra bit for postgis that will allow you to make 
use a spatial index on the "the_geom" column of the user table - it's not 
OpenGIS compliant, but it's much faster if you have many users in your system 
an only a few are within the queried country.

HTH.
Chris Hodgson

Quoting Reinhold Stahlmann <stahlma at gmx.de>:

> Hello,
> 
> how can I built an (OGC-conform) SQL-Query with PostGis
> to select all Points of a given Point-Dataset who have their
> Geometry within a specific Polygon-Geometry chosen by a label-Field
> of the Polygon?
> 
> For example: All PostGIS-Users.name (Point-Geometry) within "Sweden"
> (Polygon-Geometry)
> 
> SELECT PostGIS-Users.name
> >From PostGIS-Users
> WHERE Within(PostGIS-Users.geometry,(Countries.name = "Sweden").geometry) =
> true
> (something like this above)
> 
> 
> Thanks for any hint
> 
> Reinhold Stahlmann