[postgis-users] Attribute query at lat./long. coordinate

[Richard Greenwood]

On Sun, Nov 27, 2011 at 12:09 PM, Peter Weilandt <PeterWeilandt at shaw.ca> wrote:
> I came across PostGIS in my search for a tool to use in a website.  I want
> to develop a website that will use a coordinate (lat & long), which is
> easily obtained from a user on the client side by clicking on a Google map,
> and then transferring this coordinate to the server side where it will be
> used to  query 4 shapefiles, with each shapefile containing many
> (800+) overlapping polygons, extracting the values from 4 fields of the
> attribute table for each polygon that contains this coordinate (drill-down
> results), saving these values in tabular format for immediate processing,
> and then returning derived values to the user on the client side. There is
> no map generation required, only extracting data from the attribute
> table. This sounds simple in concept, but is PostGIS a tool that can
> accomplish the query portion of this process and, if so, are there any
> examples showing the code to use for such a query?


Peter,

I may have been the one who here from your post on the mapserver list.
PostGIS most certainly can do what you describe. A simple example:

  SELECT * FROM polygon_table WHERE St_Intersects(
      point, polygon_table.geometry);

I cheated a little there - you will need to create a point geometry,
so expanding that example slightly:

   SELECT * FROM polygon_table WHERE St_Intersects(
      ST_Point($x, $y), polygon_table.geometry);

The above assumes $x and $y came from your user's point click and are
being passed into your SQL query via a server-side programming
language such as PHP.

You may need to define the spatial reference system (SRID) with:
   ST_SetSRID( ST_Point($x, $y), 4326 )

HTH,
Rich

-- 
Richard Greenwood
richard.greenwood at gmail.com
www.greenwoodmap.com