Re: PostGIS return multiple points

On 28 October 2010 10:00, trevor1940 <antonys(at)nsom(dot)org(dot)uk> wrote:

>
> Hi
>
> I have a PostGIS table and I wish to get the location/name of multiple
> points at once the command for selecting one point is
>
> select PolyName from MyPolygones where st_Contains(the_geom,
> GeomFromText('point($LAT $LONG)4326');
>
> where $LAT $LONG are perl varables
> So how can i do this if iI have 100 points without hitting the database 100
> times?
>
>
hi,
Hi,
you could create one query using MULTIPOINT, something like:

ST_MPointFromText('MULTIPOINT($LAT1 $LONG1, $LAT2 $LONG2 ... $LAT100
$LONG100 )', 4326)

now you could find all polygons:

SELECT DISTINCT PolyName
FROM MyPolygones
WHERE ST_INTERSECTS(the_geom,
ST_MPointFromText('MULTIPOINT($LAT1 $LONG1, $LAT2 $LONG2 ... $LAT100
$LONG100 )', 4326)
);

where:
ST_INTERSECTS returns true if geometries have any common point
ST_MPointFromText creates brand new and shiny new MULTIPOINT geometry
containing all the points

regards
Szymon