Select data from table to generate layer

[Umberto Nicoletti]

The same has been cleaned up and added to the documentation here:

http://mapserver.gis.umn.edu/docs/reference/vector_data/mygis

Umberto

On 9/28/07, Umberto Nicoletti <umberto.nicoletti at gmail.com> wrote:
> Henric,
> first of all you need to make the mysql accessibile to mapserver by
> converting coordintes into a valid mygis geometry.
> Unfortunately there is not much documentation on mysql around, but I
> could gather the following by looking at the source of mapmygis.c:
>
>
> Standard ways of specifiying are :
> (1) 'geometry_column from geometry_table'
> (2) 'geometry_column from (&lt;sub query&gt;) as foo using unique
> &lt;column name&gt; using SRID=&lt;srid#&gt;'",
>
> NOTE: for (2) 'using unique' and 'SRID=' are optional, but its highly
> recommended that you use them!!!
>
> The most common problem with (1) is incorrectly uploading your data.
> There must be an entry in the geometry_columns table.  This will be
> automatically done if you used the shp2mysql program or created your
> geometry column with the AddGeometryColumn() MYGIS function.
>
> Another important thing to check is that the MYGIS user specified in
> the CONNECTION string does have SELECT permissions on the table(s)
> specified in your DATA string.
>
> If you are using the (2) method (and have errors), you've probably made a typo.
> Example:  'the_geom from (select the_geom,oid from mytable) as foo
> using unique oid using SRID=76'
> This is very much like the (1) example.  The subquery ('select
> the_geom,oid from mytable') will be executed, and mapserver will use
> 'oid' (a postgresql system column) for uniquely specifying a geometry
> (for mapserver queries).  The geometry (the_geom) must have a SRID of
> 76.
>
> Example:
> 'roads from (select
> table1.roads,table1.rd_segment_id,table2.rd_name,table2.rd_type from
> table1,table2 where table1.rd_segment_id=table2.rd_segment_id) as foo
> using unique rd_segment_id using SRID=89'
>
> This is a more complex sub-query involving joining two tables.  The
> resulting geometry (column 'roads') has SRID=89, and mapserver will
> use rd_segment_id to uniquely identify a geometry.  The attributes
> rd_type and rd_name are useable by other parts of mapserver.
>
> To use a view, do something like:
> '<geometry_column> from (SELECT * FROM <view>) as foo using unique
> <column name> using SRID=<srid#>'
>
> For example: 'the_geom from (SELECT * FROM myview) as foo using unique
> gid using SRID=-1'
>
> NOTE: for the (2) case, the ' as foo ' is requred.  The 'using unique
> &lt;column&gt;' and 'using SRID=' are case sensitive.
>
> Then you can build a simple form which either builds a filter or does
> a query by attributes on the mysql layer.
>
> HTH,
> Umberto
>
> On 9/28/07, Henric Ernstson <henric at ernstson.nu> wrote:
> > HI I have made a simple application using PHP script, al data is in a database (mySQL).
> >
> > What I woud like to do now is to add a layer from the database, where the user can select what data to view.
> >
> > I got a tabble in my database with different clients/customer  reffering to geograpical coordinates. I would like to make a application / layer where i could select with client/customer to view on the map. Have any one some idee whare to start or where I could find an example?
> >
> > Best reg. Henric
> >
>